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.

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

    Hi v-juanli-msft, I think you didn't read the last sentence of my question...

    • v-juanli-msft's avatar
      v-juanli-msft
      Icon for Community Support rankCommunity Support
      7 years ago

      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.