Forum Discussion
Helper IFind 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 | A |
| 2 | B |
| 3 | C |
| 4 | D |
| 2 | E |
| 3 | F |
| 3 | D |
Desired output
| Level | Part | Parent |
| 1 | A | null |
| 2 | B | A |
| 3 | C | B |
| 4 | D | C |
| 2 | E | A |
| 3 | F | E |
| 3 | D | E |
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"
11 Replies
- TomMartens
Super User
Hey,
this could be done, but for now an essental piece of information is missing in your sample data - a row index, that reflects the order of the rows in your sample data.
Without this row index, it will not be possible, due to the ambiguous answers to the question "who is the parent of the current chils with the level id 3"
With this row index, this parent can be determined by also looking for the parent with max rowindex.
Please chcek if this will be possibel, and update your sample data accordingly, than we can provide you with the appropriate DAX statement.
Regards,
Tom
hk2018086Hi Tom
Adding an index is no issue. Please assume an index column.
- Ashish_Mathur
Hi,
For the last 2 rows, why should the parent not be B?
- hk2018086
that's because there is a level 2 that comes first when going from down to up.
- Zubair_Muhammad
Community Champion
With help of supporting index column
Column = MINX ( TOPN ( 1, FILTER ( Table1, [Level] = EARLIER ( [Level] ) - 1 && [Index] < EARLIER ( [Index] ) ), [Index], DESC ), [Part] )
- TomMartens
Hey,
I added an index column to the table using PowerQuery.
Using this DAX statement to create a CALCULATED columnParent =
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- hk2018086
Is there a way this can be done in powerquery?
- TomMartens
Ah, just read that you want a Power Query solution.
Will provide this tomorrow.
Regards,
Tom
- Zubair_Muhammad
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"
