Forum Discussion

JVos's avatar
JVos
Icon for Helper IV rankHelper IV
7 years ago
Solved

Concatenate 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 ...
Need Help
  • ImkeF's avatar
    ImkeF
    7 years ago

    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-msft's avatar
v-juanli-msft
Icon for Community Support rankCommunity Support
7 years ago

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.