Forum Discussion
Helper IIText.Combine within Table.Group fails on numeric keys, even with Number.ToText
There are few ways you can solve ...
Assuming Sub Step ID - copy is the duplicated column and marked as text.
Method 1: Copy the number column and change the type as text and then use Text.Combine; Check "a" column
Method 2: Using Text.Combine, List.Transform. Check "b" column
Method 3: we may need distinct some times, using method 2, I can make distinct. Check "c" column.
= Table.Group(#"Changed Type", {"Step ID"}, {{"sum1", each List.Sum([2021 Actual]), type nullable number}, {"sum2", each List.Sum([2021 Plan]), type nullable number} , {"a", each Text.Combine([#"Sub Step ID - Copy"], ","), type text} , {"b", each Text.Combine(List.Transform([Sub Step ID], Text.From), ", "), type text} , {"c", each Text.Combine(List.Transform(List.Distinct([Sub Step ID]), Text.From), ", "), type text}})Hope it helps, Thanks
Super UserThere are few ways you can solve ...
Assuming Sub Step ID - copy is the duplicated column and marked as text.
Method 1: Copy the number column and change the type as text and then use Text.Combine; Check "a" column
Method 2: Using Text.Combine, List.Transform. Check "b" column
Method 3: we may need distinct some times, using method 2, I can make distinct. Check "c" column.
= Table.Group(#"Changed Type", {"Step ID"}, {{"sum1", each List.Sum([2021 Actual]), type nullable number}, {"sum2", each List.Sum([2021 Plan]), type nullable number}
, {"a", each Text.Combine([#"Sub Step ID - Copy"], ","), type text}
, {"b", each Text.Combine(List.Transform([Sub Step ID], Text.From), ", "), type text}
, {"c", each Text.Combine(List.Transform(List.Distinct([Sub Step ID]), Text.From), ", "), type text}})
Hope it helps, Thanks
GoodkatDear sevenhills,
Please accept my apologies for the late delay. Here in Germany was a longer holiday and I was visiting my family, not in reach of a computer and appropriate time. But now I am back and see gratefully your reply with three alternative options. Very cool! And all of them do work! Your idea of combining List.Distinct into option c is very clever. It will save me another interim step!
I will mark your reply as 'accept a solution' and transfer the logic of your option c into my datamodel.
Thank you once again for your time and help that gives me a path forward, but more important, provides a learning!
Best regards, Goodkat
- sevenhills
Dear Goodkat,
Thanks for your reply and we all understand that sometimes due to personal reasons, we may delay in replying to our own thrreads.
Glad to hear that you get to visit your family and enjoyed the holidays.
Hope you got the problem resolved related to this post.
Best Regards, SevenHills