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 III
Super User III

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.....)))

 

 

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

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 III
Super User III

Re: Help with a table visualization

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

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

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 III
Super User III

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.

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

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 III
Super User III

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 !

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

Imke Feldmann

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




Highlighted
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
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors