As always, love this magic show

@AccessAnalytic

8 ай бұрын

😁cheers!

Very helpful! Thank you!

@AccessAnalytic

7 ай бұрын

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

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

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

@AccessAnalytic

8 ай бұрын

Great solution Brian 😀

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

@AccessAnalytic

8 ай бұрын

All available via the link in the description 👍🏼

Very cool, thanks Wynn

@AccessAnalytic

8 ай бұрын

No worries

Those challenges are a nice initiative

@AccessAnalytic

8 ай бұрын

Thanks 😀

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

8 ай бұрын

Good one

Bloody Awesome

@AccessAnalytic

8 ай бұрын

😀Cheers!

hmm.. my solution wasn't featured. pity

@AccessAnalytic

8 ай бұрын

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

@mienzillaz

8 ай бұрын

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

@AccessAnalytic

8 ай бұрын

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"

@AccessAnalytic

8 ай бұрын

😀