cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sean Super Contributor
Super Contributor

Re: Help with a table visualization

Thanks!

 

By alphabetized I mean the names sorted A-Z on each row

 

Right now they are just output in the order they are found in the source

 

John, Victor, Bill => Bill, John, Victor

George, Elizabeth => Elizabeth, George

 

Super User
Super User

Re: Help with a table visualization

Yes, you just squeeze in a List.Sort between Text.Combine and List.Distinct: Text.Combine(List.Sort(List.Distinct.....)))

 

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Sean Super Contributor
Super Contributor

Re: Help with a table visualization

@ImkeF Thanks!

 

When I add List.Sort - the names get sorted in reverese Z-A - AND all spaces and commas disapper between the names

 

Bill, Victor, Elizabeth => VictorElizabethBill - so they sort but in reverse Z-A and spaces and commas disapper ???

 

When I remove List.Sort it goes back to => Bill, Victor, Elizabeth

Super User
Super User

Re: Help with a table visualization

That's probably due to brackets at the wrong place. Could you please share the codeline?

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Sean Super Contributor
Super Contributor

Re: Help with a table visualization

 

let
    Source = ProjectQueries,
    GroupRows = Table.Group(Source, {"JP"}, {{"AGENT", each _, type table}}),
    TransformColumnToText = Table.AddColumn(GroupRows, "ALL Agents Working", each Text.Combine(List.Distinct([AGENT][AGENT]), ", ")),
    #"Duplicated Column" = Table.DuplicateColumn(TransformColumnToText, "ALL Agents Working", "Agents Working"),
    #"Replaced Value" = Table.ReplaceValue(#"Duplicated Column",(", Deleted"),"",Replacer.ReplaceText,{"Agents Working"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Deleted, ","",Replacer.ReplaceText,{"Agents Working"}),
    Cleanup = Table.RemoveColumns(#"Replaced Value1",{"AGENT"})
in
    Cleanup

With the above code I get

ListSort1.png

 

If you are wondering about the other code - here's a brief explanation of my reasoning

I Duplicated the Column so I can see the Original next to the result when I gett rid off Deleted

Then I have 3 scenario with the Deleted

1 - when Deleted is first => Deleted, Name, Name, Name

2 - when Deleted is somewhere in between => Name, Deleted, Name, Name

3 - when Deleted is last => Name, Name, Name, Deleted

after playing around with the code for a while I settled on what you see above

it seems to work for all cases for getting rid of Deleted and the comma

I tried to do it in one line with the OR operator || but it work

 

Here's the code with List.Insert

let
    Source = ProjectQueries,
    GroupRows = Table.Group(Source, {"JP"}, {{"AGENT", each _, type table}}),
    TransformColumnToText = Table.AddColumn(GroupRows, "ALL Agents Working", each Text.Combine(List.Sort(List.Distinct([AGENT][AGENT]), ", "))),
    #"Duplicated Column" = Table.DuplicateColumn(TransformColumnToText, "ALL Agents Working", "Agents Working"),
    #"Replaced Value" = Table.ReplaceValue(#"Duplicated Column",(", Deleted"),"",Replacer.ReplaceText,{"Agents Working"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Deleted, ","",Replacer.ReplaceText,{"Agents Working"}),
    Cleanup = Table.RemoveColumns(#"Replaced Value1",{"AGENT"})
in
    Cleanup

 

And the result

ListSort2.png

Super User
Super User

Re: Help with a table visualization

This is painfully simple 🙂 : You need to shift one of your closing parenthesis' after the AGENTS:

 

TransformColumnToText = Table.AddColumn(GroupRows, "ALL Agents Working", each Text.Combine(List.Sort(List.Distinct([AGENT][AGENT])), ", ")),

 

So the List.Sort-Command didn't stop soon enough. Strange that it didn't error.

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Sean Super Contributor
Super Contributor

Re: Help with a table visualization

Thanks! That fixed it all!

 

 

Sean Super Contributor
Super Contributor

Re: Help with a table visualization

Hello @ImkeF

 

I am wondering if something else can be added to the code below...

let
    Source = ProjectQueries,
    GroupRows = Table.Group(Source, {"JP"}, {{"AGENT", each _, type table}}),
    TransformColumnToText = Table.AddColumn(GroupRows, "ALL Agents Working", each Text.Combine(List.Sort(List.Distinct([AGENT][AGENT])), ", ")),
    #"Duplicated Column" = Table.DuplicateColumn(TransformColumnToText, "ALL Agents Working", "Agents Working"),
    #"Replaced Value" = Table.ReplaceValue(#"Duplicated Column",(", Deleted"),"",Replacer.ReplaceText,{"Agents Working"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Deleted, ","",Replacer.ReplaceText,{"Agents Working"}),
    Cleanup = Table.RemoveColumns(#"Replaced Value1",{"AGENT"})
in
    Cleanup

Is there any way to add a count of AGENT for each JP

 

So result looks like this

JP - Agents Working

1   - Bill (10), Emma (15),

2   - George (5), Victor (10)

3   - etc...

 

Thanks for your help!

Super User
Super User

Re: Help with a table visualization

You have to add another "grouping-round" before:

 

let
    Source = ProjectQueries,
    GroupAgents = Table.Group(Source, {"JP", "AGENT"}, {{"Count", each Table.RowCount(_), type number}}),
    CountAgent = Table.AddColumn(GroupAgents, "Custom", each [AGENT]&" ("&Text.From([Count])&")"),
    RemoveCols = Table.RemoveColumns(CountAgent,{"AGENT", "Count"}),
    GroupRows = Table.Group(RemoveCols, {"JP"}, {{"AGENT", each _, type table}}),
    TransformColumnToText = Table.AddColumn(GroupRows, "ALL Agents Working", each Text.Combine(List.Sort(List.Distinct([AGENT][Custom])), ", "))

 

! Watch the changed code in the last line !

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Sean Super Contributor
Super Contributor

Re: Help with a table visualization

@ImkeF Wow! Amazing!

 

I've been avoiding M far too long - trying to get by with just the UI.

 

Thank You again!

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)