Concatenate values from child records while expanding from merged query

[JVos]

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?

[ImkeF · Accepted solution]

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.

[JVos]

@ImkeFThanks!

[v-juanli-msft]

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
Maggie

Community 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.

[JVos]

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

Community 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.