Group Uniques - Power Query Challenge

⏬ This Power Query Challenge is about combining multiple records into single rows ⏬
00:00 Intro
00:19 The Challenge
01:44 The Long Solution
05:44 The Better Solution
The Solution File and everyone's submissions can be found here:
aasolutions.sharepoint.com/:f...
The team and I at Access Analytic develop Power BI and Excel solutions for clients in Australia and deliver training around the World. accessanalytic.com.au/
Did you know I've written a book "Power BI for the Excel Analyst"?
pbi.guide/book/
Connect with me
wyn.bio.link/

[@hk_200k]

As always, love this magic show

[@AccessAnalytic]

😁cheers!

[@angelaisland4036]

Very helpful! Thank you!

[@AccessAnalytic]

You’re welcome. I appreciate you taking the time to let me know you found it useful

[@brianjulius6401]

Wyn - Thanks! Fun challenge and a great wrap up.

[@AccessAnalytic]

Great solution Brian 😀

[@Acheiropoietos]

Great solutions. I am very interested in seeing more 👍❤️

[@chrism9037]

Very cool, thanks Wynn

[@AccessAnalytic]

No worries

[@thebhaskarjoshi]

Would love ❤ to see other solutions as well...

[@AccessAnalytic]

All available via the link in the description 👍🏼

[@Bhavik_Khatri]

Bloody Awesome

[@AccessAnalytic]

😀Cheers!

[@davidmaldonadocastillejos3179]

Those challenges are a nice initiative

[@AccessAnalytic]

Thanks 😀

[@UKDevice]

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.

[@AccessAnalytic]

Good one

[@mienzillaz]

hmm.. my solution wasn't featured. pity

[@AccessAnalytic]

Yeah just too many to cover all sorry.
I see you went down the same lines as the video as did several others 👍🏼

[@mienzillaz]

@@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

[@BillSzysz1]

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

[@AccessAnalytic]

Cheers Bill, I’m always impressed by the nested formula approach but find it harder to understand and explain to others than separate steps.

[@GrainneDuggan_Excel]

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"

[@AccessAnalytic]

😀