Forum Discussion
Helper IVConcatenate values from child records while expanding from merged query
Given a 'parent' table:
| ROUTING_ID |
| 1 |
| 2 |
and a 'child' table:
| ROUTING_ID_From | ROUTING_ID_To |
| 1 | 25 |
| 1 | 26 |
| 1 | 27 |
| 2 | 25 |
| 2 | 26 |
How can I get the following output column 'NextRoutings', if possible in the 'ExpandTableColumn' step:
| ROUTING_ID | NextRoutings |
| 1 | 25,26,27 |
| 2 | 25,26 |
This is NOT working:
= Table.ExpandTableColumn(#"Merged Queries", "qryRoutingsFromTo", List.Accumulate(qryRoutingsFromTo[ROUTING_ID_To], "", (state, current) => if state = "" then Number.ToText(current) else state & "," & Number.ToText(current)), {"ROUTING_ID_To"})
I also don't want to expand the table in the 'normal' way and then group it on all columns other than qryRoutingsFromTo[ROUTING_ID_To] and then 'calculcate' the NextRouting column. Reason: I think it can be done directly in the ExpandTableColumn step. I also need to group on a lot of columns.
Another solution could be to first group the child table (which is only on one column) and after that to merge with it. But still... can it be done in the expand-step?
For your purpose, "Table.ExpandTableColumn" is the wrong command, as you don't want an expansion, but an aggregation instead.
Using the UI, you have the option to select an aggregation instead:
You can either use one of the default-options and then tweak the code:
Table.AggregateTableColumn(#"Merged Queries", "Table2", {{"ROUTING_ID_To", each Text.Combine(List.Transform(_, (x) => Text.From(x)), ","), "Desired Result"}})or simply add a column with the code v-juanli-msft has provided already (Text.Combine[MergedChildTableColumn], ","). That delivers the desired result and you simple remove the merged column afterwards.
5 Replies
- ImkeF
Community Champion
For your purpose, "Table.ExpandTableColumn" is the wrong command, as you don't want an expansion, but an aggregation instead.
Using the UI, you have the option to select an aggregation instead:
You can either use one of the default-options and then tweak the code:
Table.AggregateTableColumn(#"Merged Queries", "Table2", {{"ROUTING_ID_To", each Text.Combine(List.Transform(_, (x) => Text.From(x)), ","), "Desired Result"}})or simply add a column with the code v-juanli-msft has provided already (Text.Combine[MergedChildTableColumn], ","). That delivers the desired result and you simple remove the merged column afterwards.
v-juanli-msftCommunity Support
Hi JVos
In Child table, group by ROUTING_ID_From
let Source = Excel.Workbook(File.Contents("C:\Users\maggiel\Desktop\case\6\6.27\6.27.xlsx"), null, true), Sheet3_Sheet = Source{[Item="Sheet3",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(Sheet3_Sheet, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ROUTING_ID_From", Int64.Type}, {"ROUTING_ID_To", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"ROUTING_ID_From"}, {{"NextRoutings", each Text.Combine([ROUTING_ID_To]," , "), type text}}) in #"Grouped Rows"Then merge child table to parent table and expand the column.
Best Regards
MaggieCommunity Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
JVosHi v-juanli-msft, I think you didn't read the last sentence of my question...
- v-juanli-msft
Hi JVos
But still... can it be done in the expand-step?
I can't make it work only with the expand-step.
There is some mistake in my previous post, please read again since i update.
Hope it will help you indeed.
Best Regards
MaggieCommunity Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.