Forum Discussion

hk2018086's avatar
hk2018086
Helper I
7 years ago
Solved

Find the Parent

  Hi  I need help to find the parent of each part as per the illustration below. I am trying to do this in Powerquery and an Index column can be added if required.   INPUT   Level Part 1...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 years ago

    hk2018086

     

    Here is a Power Query Solution

    Please see attached file as well

     

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJUitWJVjICspzALGMgyxnMMgGyXOCyrnBZNzgLKBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Level = _t, Part = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Level", Int64.Type}, {"Part", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Parent Level", each [Level]+1),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom",{"Level"},#"Added Custom",{"Parent Level"},"Added Custom",JoinKind.LeftOuter),
    #"Added Custom1" = Table.AddColumn(#"Merged Queries", "Custom", each let myindex = [Index] in
Table.Max(Table.SelectRows([Added Custom],each [Index] < myindex),"Index")),
    #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom1", "Custom", {"Part"}, {"Custom.Part"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Index", "Parent Level", "Added Custom"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom.Part", "Parent"}})
in
    #"Renamed Columns"
TomMartens's avatar
TomMartens
Super User
7 years ago

Ah, just read that you want a Power Query solution.

Will provide this tomorrow.

 

Regards,

Tom

Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
7 years ago

hk2018086

 

Here is a Power Query Solution

Please see attached file as well

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJUitWJVjICspzALGMgyxnMMgGyXOCyrnBZNzgLKBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Level = _t, Part = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Level", Int64.Type}, {"Part", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Parent Level", each [Level]+1),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom",{"Level"},#"Added Custom",{"Parent Level"},"Added Custom",JoinKind.LeftOuter),
    #"Added Custom1" = Table.AddColumn(#"Merged Queries", "Custom", each let myindex = [Index] in
Table.Max(Table.SelectRows([Added Custom],each [Index] < myindex),"Index")),
    #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom1", "Custom", {"Part"}, {"Custom.Part"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Index", "Parent Level", "Added Custom"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom.Part", "Parent"}})
in
    #"Renamed Columns"
  • hk2018086's avatar
    hk2018086
    Helper I
    7 years ago

    Thank you