Forum Discussion
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...
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"
Hey,
I added an index column to the table using PowerQuery.
Using this DAX statement to create a CALCULATED column
Parent =
var parentlevel = 'Table1'[Level] - 1
var currentIndex = 'Table1'[Index]
var indexofParent =
CALCULATE(
MAX('Table1'[Index])
,FILTER(ALL('Table1')
,'Table1'[Level] = parentlevel && 'Table1'[Index] < currentIndex
)
)
return
CALCULATE(
FIRSTNONBLANK('Table1'[Part],0)
,FILTER(ALL('Table1')
,'Table1'[Level] = parentlevel && 'Table1'[Index] = indexofParent
)
)
creates this output:
I guess this is what you are looking for.
Regards,
Tom
Is there a way this can be done in powerquery?
Ah, just read that you want a Power Query solution.
Will provide this tomorrow.
Regards,
Tom
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"Thank you
