I think i would have gone with the "Text.Combine(List.Distinct([Data][System 1 contact ID]),",")" custom columns after grouping by ID and do this for each field to return. No need to expand lists etc but the method shown involves less steps. Nice.
And I missed your challenge again ☹ So maybe I'll give you something short, just two steps let Source = Excel.CurrentWorkbook(){[Name="tblData"]}[Content], Result = Table.Combine(Table.Group(Source, {"Client ID#"}, {{"tbl", (x) => Table.FromRows({List.Transform(Table.ToColumns(x), each Text.Combine(List.Distinct(_), ", "))}, Table.ColumnNames(x)), type table}})[tbl]) in Result
I did it in two steps but it was manual. It does not deal with the Deal ID#s problem and it is not dynamic if the column names change. Thanks for saying use PQ to rename column Deal ID - obvious after I saw your solution..... let Source = Excel.CurrentWorkbook(){[Name="tblData"]}[Content], #"Grouped Rows" = Table.Group(Source, {"Client ID#"}, {{"Count S1 id", each Text.Combine(List.Distinct( [System 1 contact ID]),", "), type text}, {"Count S2 id", each Text.Combine(List.Distinct( [System 2 contact ID]),", "), type text}, {"Count S3 id", each Text.Combine( List.Distinct([System 3 contact ID]),", "), type text}, {"Count Name", each Text.Combine(List.Distinct([Name]),", "), type text}, {"Count Email", each Text.Combine(List.Distinct([Email]),", "), type text}, {"Count Phone", each Text.Combine(List.Distinct([Phone number]),", "), type text}, {"Count Deal", each Text.Combine(List.Distinct([Deal IDs]),", "), type text}}) in #"Grouped Rows"
As always, love this magic show
😁cheers!
Great solutions. I am very interested in seeing more 👍❤️
Wyn - Thanks! Fun challenge and a great wrap up.
Great solution Brian 😀
Very helpful! Thank you!
You’re welcome. I appreciate you taking the time to let me know you found it useful
Those challenges are a nice initiative
Thanks 😀
Would love ❤ to see other solutions as well...
All available via the link in the description 👍🏼
Very cool, thanks Wynn
No worries
I think i would have gone with the "Text.Combine(List.Distinct([Data][System 1 contact ID]),",")" custom columns after grouping by ID and do this for each field to return. No need to expand lists etc but the method shown involves less steps. Nice.
Good one
Bloody Awesome
😀Cheers!
hmm.. my solution wasn't featured. pity
Yeah just too many to cover all sorry.
I see you went down the same lines as the video as did several others 👍🏼
@@AccessAnalytic yes, unpivot first with all rows, transform them to get a list, clean up from duplicates, and put it back together with a pivot
And I missed your challenge again ☹ So maybe I'll give you something short, just two steps
let
Source = Excel.CurrentWorkbook(){[Name="tblData"]}[Content],
Result = Table.Combine(Table.Group(Source, {"Client ID#"}, {{"tbl", (x) => Table.FromRows({List.Transform(Table.ToColumns(x), each Text.Combine(List.Distinct(_), ", "))}, Table.ColumnNames(x)), type table}})[tbl])
in
Result
Cheers Bill, I’m always impressed by the nested formula approach but find it harder to understand and explain to others than separate steps.
I did it in two steps but it was manual. It does not deal with the Deal ID#s problem and it is not dynamic if the column names change. Thanks for saying use PQ to rename column Deal ID - obvious after I saw your solution.....
let
Source = Excel.CurrentWorkbook(){[Name="tblData"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"Client ID#"},
{{"Count S1 id", each Text.Combine(List.Distinct( [System 1 contact ID]),", "), type text},
{"Count S2 id", each Text.Combine(List.Distinct( [System 2 contact ID]),", "), type text},
{"Count S3 id", each Text.Combine( List.Distinct([System 3 contact ID]),", "), type text},
{"Count Name", each Text.Combine(List.Distinct([Name]),", "), type text},
{"Count Email", each Text.Combine(List.Distinct([Email]),", "), type text},
{"Count Phone", each Text.Combine(List.Distinct([Phone number]),", "), type text},
{"Count Deal", each Text.Combine(List.Distinct([Deal IDs]),", "), type text}})
in
#"Grouped Rows"
😀