Forum Discussion
How to find data hierarchy level information
I need use M code (power query, not DAX) to convert Project task table information to a hierarchy table. under is example, Thank you for help.
Project task table like under:
result need like under:
pls try
let trans = (x)=> Table.ReplaceValue(x,null,0,Replacer.ReplaceValue, Table.ColumnNames(x)), t = (x)=> Table.TransformColumnTypes(x,{{"Parent Task", type text}, {"Task", type text}}), from=t(trans(Table1)), dic = Record.FromList( from[Project], from[Task]), dict = Record.FromTable(Table.RenameColumns(Table.Group(from, "Parent Task", {"Value", (x)=>x[Task]}),{"Parent Task","Name"})), f=(lst)=>List.Combine(List.Transform(lst,(x)=>List.Transform(Record.FieldOrDefault(dict,List.Last(x)??"",{null}),(y)=>x&{y}))), df = List.Zip({List.Difference(List.Distinct(from[Parent Task]),from[Task])}), to = Table.FromList(f(f(f(f(df)))),(x)=>x), AddColumn = Table.AddColumn(to, "Project", each Record.FieldOrDefault(dic,[Column2])), SelectColumns = Table.SelectColumns(AddColumn,{"Project", "Column2", "Column3", "Column4"}) in SelectColumns
7 Replies
- Ahmedx
Super User
pls try
let trans = (x)=> Table.ReplaceValue(x,null,0,Replacer.ReplaceValue, Table.ColumnNames(x)), t = (x)=> Table.TransformColumnTypes(x,{{"Parent Task", type text}, {"Task", type text}}), from=t(trans(Table1)), dic = Record.FromList( from[Project], from[Task]), dict = Record.FromTable(Table.RenameColumns(Table.Group(from, "Parent Task", {"Value", (x)=>x[Task]}),{"Parent Task","Name"})), f=(lst)=>List.Combine(List.Transform(lst,(x)=>List.Transform(Record.FieldOrDefault(dict,List.Last(x)??"",{null}),(y)=>x&{y}))), df = List.Zip({List.Difference(List.Distinct(from[Parent Task]),from[Task])}), to = Table.FromList(f(f(f(f(df)))),(x)=>x), AddColumn = Table.AddColumn(to, "Project", each Record.FieldOrDefault(dic,[Column2])), SelectColumns = Table.SelectColumns(AddColumn,{"Project", "Column2", "Column3", "Column4"}) in SelectColumns - Shahid12523
Community Champion
let
BuildHierarchy = (tbl as table) =>
let
AddPath = List.Generate(
() => [Current = null, Path = {}],
each true,
each [
Current = if [Current] = null then
List.Select(tbl[Task], each Record.Field(_, "ParentTask") = null){0}
else
try List.Select(tbl[Task], each Record.Field(_, "ParentTask") = [Current]){0} otherwise null,
Path = [Path] & { [Current] }
],
each [Path]
),
Expanded = Table.FromList(AddPath, Splitter.SplitByNothing(), {"Path"}),
WithLevels = Table.TransformColumns(Expanded, {
{"Path", each List.Transform(_, Text.From)}
}),
Final = Table.ExpandListColumn(WithLevels, "Path")
in
Final
in
BuildHierarchyThank you for your help—your code logic looks like it matches my needs. However, when I tried to use the code in my report, it seems there’s an issue with the "AddPath" step.
I've attached my test file for your reference. Could you please help me figure out how to fix this?
Thank you again for your support!
Here is test data file.
- Ashish_Mathur
Hi,
Cannot understand the logic of how table 2 was generated. Please explain. Also, share data in a format that can be pasted in an MS Excel file.
Ashish_Mathur
Thank you for help, here share my test data, I want from parent column get task hierarchy level details.
Test data- Ashish_Mathur
That takes me to a request access page. Please also explain the logic very clearly.
v-hashadapuCommunity Support
Hi WendyWang303 , Thank you for reaching out to the Microsoft Community Forum.
Your test data link got redirected to access page, where i requested access. Please grant the access or provide the details here, so we can better understand the issue and help you solve it.
