Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all!
I have a flat file that shows me the relation for each code combination:
Parent | Child |
X | X1 |
X1 | X12 |
X1 | X13 |
A | A1 |
A1 | A2 |
As you can see we have a multi level relation here between both column.
Now I would like to get a result this construct:
Parent | Child | Level 1 | Level 2 | Level 3 | Level 4 |
X | X1 | TRUE | FALSE | FALSE | FALSE |
X1 | X12 | FALSE | TRUE | FALSE | FALSE |
X1 | X13 | FALSE | TRUE | FALSE | FALSE |
How is that possible using Power Query?
I don't know how many levels we have in the end.
Solved! Go to Solution.
Alright then. Let's try this...
For the dataset
add the [Child] column as a new query and remove duplicates
now test the [Parent] column to see if the value is a "master" parent
Table.AddColumn(#"Changed Type1", "masterParent", each if List.Contains(Child, [Parent]) then null else [Parent])
Fill down the [masterParent] column
Table.FillDown(#"Added Custom",{"masterParent"})
Group By [masterParent] and [Parent] with now aggregation of rows
Table.Group(#"Filled Down", {"Parent", "masterParent"}, {{"Group1", each _, type table [Parent=nullable text, Child=nullable text, master=text]}})
Now Group By [masterParent] with no aggregation
Table.Group(#"Grouped Rows", {"masterParent"}, {{"Group2", each _, type table [Parent=nullable text, master=text, Count=table]}})
Add an index column
Table.AddColumn(#"Grouped Rows1", "Index", each Table.AddIndexColumn([Group2], "Index", 1))
Remove the [masterParent] and [Group2] columns
Table.RemoveColumns(#"Added Custom1",{"masterParent", "Group2"})
expand the [Index] column
Table.ExpandTableColumn(#"Removed Columns", "Index", {"Parent", "masterParent", "Group1", "Index"}, {"Parent", "masterParent", "Group1", "Index"})
remove the [Parent] and [masterParent] columns
Table.RemoveColumns(#"Expanded Custom",{"Parent", "masterParent"})
expand [Group1] column
Table.ExpandTableColumn(#"Removed Columns1", "Group1", {"Parent", "Child", "masterParent"}, {"Parent", "Child", "masterParent"})
add a Prefix to the [Index] column
Table.TransformColumns(#"Expanded Count", {{"Index", each "Level " & Text.From(_, "en-US"), type text}})
add a boolean true column
Table.AddColumn(#"Added Prefix", "_boolean", each true)
add the [Index] column as a new query and remove duplicates
pivot the [Index] column using the [_boolean] column as values with no row aggregation
Table.Pivot(#"Added Custom2", List.Distinct(#"Added Custom2"[Index]), "Index", "_boolean")
replace the null values with "FALSE", using the Index list to ensure it will work dynamically
Table.ReplaceValue(#"Pivoted Column",null,false,Replacer.ReplaceValue,Index)
and finally remove the [masterParent] column
Table.RemoveColumns(#"Replaced Value",{"masterParent"})
Hope this helps!
Proud to be a Super User! | |
What would dictate if a value is Level 1 or Level 2 etc?
Proud to be a Super User! | |
This is not dictatedin the data right now. This is the exercise / request here. Calculate the number of Levels for each hierarchy / relation between the codes and display the individual level for each combination.
It is always Parent To Child. Of there is no relation more downstream, then it is the end. The same applies for Level 1. If nothing is above, then it is Level 1
Alright then. Let's try this...
For the dataset
add the [Child] column as a new query and remove duplicates
now test the [Parent] column to see if the value is a "master" parent
Table.AddColumn(#"Changed Type1", "masterParent", each if List.Contains(Child, [Parent]) then null else [Parent])
Fill down the [masterParent] column
Table.FillDown(#"Added Custom",{"masterParent"})
Group By [masterParent] and [Parent] with now aggregation of rows
Table.Group(#"Filled Down", {"Parent", "masterParent"}, {{"Group1", each _, type table [Parent=nullable text, Child=nullable text, master=text]}})
Now Group By [masterParent] with no aggregation
Table.Group(#"Grouped Rows", {"masterParent"}, {{"Group2", each _, type table [Parent=nullable text, master=text, Count=table]}})
Add an index column
Table.AddColumn(#"Grouped Rows1", "Index", each Table.AddIndexColumn([Group2], "Index", 1))
Remove the [masterParent] and [Group2] columns
Table.RemoveColumns(#"Added Custom1",{"masterParent", "Group2"})
expand the [Index] column
Table.ExpandTableColumn(#"Removed Columns", "Index", {"Parent", "masterParent", "Group1", "Index"}, {"Parent", "masterParent", "Group1", "Index"})
remove the [Parent] and [masterParent] columns
Table.RemoveColumns(#"Expanded Custom",{"Parent", "masterParent"})
expand [Group1] column
Table.ExpandTableColumn(#"Removed Columns1", "Group1", {"Parent", "Child", "masterParent"}, {"Parent", "Child", "masterParent"})
add a Prefix to the [Index] column
Table.TransformColumns(#"Expanded Count", {{"Index", each "Level " & Text.From(_, "en-US"), type text}})
add a boolean true column
Table.AddColumn(#"Added Prefix", "_boolean", each true)
add the [Index] column as a new query and remove duplicates
pivot the [Index] column using the [_boolean] column as values with no row aggregation
Table.Pivot(#"Added Custom2", List.Distinct(#"Added Custom2"[Index]), "Index", "_boolean")
replace the null values with "FALSE", using the Index list to ensure it will work dynamically
Table.ReplaceValue(#"Pivoted Column",null,false,Replacer.ReplaceValue,Index)
and finally remove the [masterParent] column
Table.RemoveColumns(#"Replaced Value",{"masterParent"})
Hope this helps!
Proud to be a Super User! | |
As with all things Power Query there are likely other (better) ways of doing this, but this should get you going in the right direction.
I created an excel file with your data as the source
added a column to populate "True" values
Table.AddColumn(#"Changed Type1", "childBoolean", each if [Child] <> null then true else false)
added a column to get the length of the [Child] values (subtracted 1 to make sure column names are correct)
Table.AddColumn(#"Added Custom", "childLength", each Text.Length([Child]) - 1)
add a prefix to the [childLength] column to name the resulting columns correctly
added the [childLength] column as a new query, converted it to a list and removed duplicates to get
back in the main query, I pivoted the childLength column using the childBoolean column as values, not aggregating
Table.Pivot(#"Added Prefix", List.Distinct(#"Added Prefix"[childLength]), "childLength", "childBoolean")
the null values are replaced using the childLength list created earlier to ensure the columns are dynamically updated
Table.ReplaceValue(#"Pivoted Column",null,false,Replacer.ReplaceValue,childLength)
Hope this helps.
Proud to be a Super User! | |
@jgeddes : Thanks a lot! Sorry for not making this clear. But the length of the string has nothing to do with the level. It was just used to demonstrate the relation more clearly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.