Forum Discussion
WendyWang303
10 months agoFrequent Visitor
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...
- 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
Ahmedx
Super User
10 months agopls 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