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...
  • Ahmedx's avatar
    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