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

     

     

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

ImkeF Wow! Amazing!

 

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

 

Thank You again!