Forum Discussion

WendyWang303's avatar
WendyWang303
Frequent Visitor
10 months ago
Solved

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 li...
Need Help
  • Ahmedx's avatar
    Ahmedx
    10 months ago

    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's avatar
Shahid12523
Icon for Community Champion rankCommunity Champion
10 months ago

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
BuildHierarchy

WendyWang303's avatar
WendyWang303
Frequent Visitor
10 months ago

Shahid12523 

Thank 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

Test data 

.