Forum Discussion

mork's avatar
mork
Helper V
10 years ago
Solved

Help with a table visualization

Hey everyone, I have two data tables that I have transformed. One is about projects and has columns like ProjectName, ClientName, Completion%, ProposedEffort, ActualEffort etc. The other is about m...
Need Help
  • ImkeF's avatar
    ImkeF
    10 years ago

    List.Distinct will do that:

    let
    Source = Ressource,
    GroupRows = Table.Group(Source, {"ProjectName"}, {{"ResourceName", each _, type table}, {"SumTimesheetActualWork", each List.Sum([#"TimesheetActualWork"]), type number}}),
    TransformColumnToText = Table.AddColumn(GroupRows, "Custom", each Text.Combine(List.Distinct([ResourceName][ResourceName]), ", ")),
    Cleanup = Table.RemoveColumns(TransformColumnToText,{"ResourceName"})
in
    Cleanup

     

     

Sean's avatar
Sean
Community Champion
10 years ago

 

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

 

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

ImkeF's avatar
ImkeF
Community Champion
10 years ago

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.

  • ImkeF's avatar
    ImkeF
    Community Champion
    10 years ago

    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 !

  • Sean's avatar
    Sean
    Community Champion
    10 years ago

    Thanks! That fixed it all!

     

     

  • Sean's avatar
    Sean
    Community Champion
    10 years ago

    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!

  • Sean's avatar
    Sean
    Community Champion
    10 years ago

    ImkeF Wow! Amazing!

     

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

     

    Thank You again!