How to find data hierarchy level information

[WendyWang303]

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: 

[Ahmedx · Accepted solution]

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

[v-hashadapu]

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.

[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.

[WendyWang303]

@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.

[Shahid12523]

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]

@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 

.