Text.Combine within Table.Group fails on numeric keys, even with Number.ToText

[Goodkat]

Dear Power Query enthusiasts,

I am regularly thrilled by the efficiencies and smart dataflows PowerQuery enables. To further aggregate which data is worth to display, and which could be hidden, I had a look at the group by functionality.

https://c.gmx.net/@324888734501700174/7lpIroMCTFSyvoig62x16A

I am grouping two numeric dimensions and a dimension containing numeric Step IDs:

DataType_PIDText = Table.TransformColumnTypes(Add_BaseID,{{"Step ID", type text}}),

    Group = Table.Group(DataType_PIDText, {"Base ID"}, {{"2021 Actual", each List.Sum([2021 Actual]), type number}, {"2021 Plan", each List.Sum([2021 Plan]), type number}, {"Step ID", each Text.Combine([Step ID], "#(lf)")}})

That works, but as a prerequisite of the Text.Combine function the [Step ID] must be converted into text before. But as this is one of my major mapping keys and is by nature an integer, artificially convert into text for this one step (and subsequently convert it back) does not appear appropriate.

So I tried in the second query of the linked file (Aggregation_condensed) to bring in Number.ToText:

Group = Table.Group(Add_BaseID, {"Base ID"}, {{"2021 Actual", each List.Sum([2021 Actual]), type number}, {"2021 Plan", each List.Sum([2021 Plan]), type number}, {"Step ID", each Text.Combine(Number.ToText([Step ID]), "#(lf)")}})

But I get errors thrown. I was searching the internet with various key word combinations, but I did not find anything. Combining Text there are multiple posts, but numeric keys into a text field I failed to find.

I would be helpful if someone from the forum could direct me, whether this is possible at all and what I am doing wrong.

Thank you very much in advance!

Best regards, Andreas

[sevenhills · Accepted solution]

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

[Goodkat]

Dear 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