Help with recursive column

[kk1791]

Need help: I want to create a column "Result" which has the parent item along with its component item. If this is not a recursive problem, please let me know what type of syntax should I search in google to solve this. 

Thanks for any help in advance

[v-yalanwu-msft · Accepted solution]

Hi, @kk1791 ;

Here are two methods in power query.

1.Group by column.

2.add conditional column.

3.unpivot it.

The final show:

M lanuage:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMFTSUTIyMjEwANKGSrE6GIJG2ASNsQmaoApaYjPTEpuZltjMtMRtpqlSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"End Item" = _t, #"PARENT ITEM" = _t, #"COMPONENT ITEM" = _t]),
    #"Grouped Rows" = Table.Group(Source, {"PARENT ITEM", "End Item"}, {{"min", each List.Min([COMPONENT ITEM]), type nullable text}, {"a", each _, type table [End Item=nullable text, PARENT ITEM=nullable text, COMPONENT ITEM=nullable text]}}),
    #"Expanded a" = Table.ExpandTableColumn(#"Grouped Rows", "a", {"COMPONENT ITEM"}, {"a.COMPONENT ITEM"}),
    #"Added Conditional Column" = Table.AddColumn(#"Expanded a", "Custom", each if [min] = [a.COMPONENT ITEM] then [PARENT ITEM] else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"min", "PARENT ITEM"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"End Item", "Custom", "a.COMPONENT ITEM"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", {"End Item"}, "Attribute", "Value"),
    #"Removed Columns1" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"})
in
    #"Removed Columns1"

Method2:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMFTSUTIyMjEwANKGSrE6GIJG2ASNsQmaoApaYjPTEpuZltjMtMRtpqlSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"End Item" = _t, #"PARENT ITEM" = _t, #"COMPONENT ITEM" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"End Item", Int64.Type}, {"PARENT ITEM", Int64.Type}, {"COMPONENT ITEM", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let a = [PARENT ITEM],
Component = Table.SelectRows(#"Changed Type",each [PARENT ITEM] = a)[COMPONENT ITEM]
 in List.Combine({ List.Distinct({[PARENT ITEM]}) ,Component})),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"COMPONENT ITEM"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"End Item", "PARENT ITEM"}, {{"Count", each List.First([Custom]), type list}}),
    #"Expanded Count" = Table.ExpandListColumn(#"Grouped Rows", "Count"),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Count",{"PARENT ITEM"})
in
    #"Removed Columns1"

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.