Parent-child hierarchy

[Anonymous]

Hello PBI Forum!

A month ago i needed a help with parent-child hierarchy, the solutions really worked but now I need to do a little diff...

My goal at that time was to create 4 new hierarchy columns with just 3 colums, i got some help here and it really worked, staying like that:

The M code was that:

let
    Source = Excel.Workbook(File.Contents("G:\Meine Ablage\Mappe2.xlsx"), null, true),
    Facts21_Table = Source{[Item="Facts21",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Facts21_Table,{{"Path", type text}}),
    #"Inserted Text Length" = Table.AddColumn(#"Changed Type", "Length", each Text.Length([Path]), Int64.Type),
    #"Changed Type1" = Table.TransformColumnTypes(#"Inserted Text Length",{{"Length", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Level 1", each if[Length] <= 2 then [Name] else null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Level 2", each if [Length] <= 4 then [Name] else null),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Level 3", each if [Length] <= 6 then [Name] else null),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Level 4", each if [Length] <= 8 then [Name] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom3",{"Level 1", "Level 2", "Level 3", "Level 4"}),
    #"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Length"})
in
    #"Removed Columns"

I did in my project and it worked well, now the problem is another..

When i put a filter on pbi, the Name 1 appears in all levels (of course) but it is just a group of other names, there is no need to the "Name 1" appears on Forth level, its possible to transform the same table into that??

I tried a little bit, but unsuccessful =\

Thankss!

#Edit

The first topic: Solved: Re: Automatic creation of Parent/Child colums leve... - Microsoft Power BI Community

[mangaus1111 · Accepted solution]

hi @Anonymous ,

you can add this to the original query

#"Added Custom4" = Table.AddColumn(#"Removed Columns", "Level 2.1", each if [Level 2] = [Level 1] then null else [Level 2]),
    #"Added Custom5" = Table.AddColumn(#"Added Custom4", "Level 3.1", each if [Level 3] = [Level 2] or [Level 3] = [Level 1] then null else [Level 3]),
    #"Added Custom6" = Table.AddColumn(#"Added Custom5", "Level 4.1", each if [Level 4] = [Level 3] or [Level 4] = [Level 2] or [Level 4] = [Level 1] then null else [Level 4]),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom6",{"Level 2", "Level 3", "Level 4"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Level 2.1", "Level 2"}, {"Level 3.1", "Level 3"}, {"Level 4.1", "Level 4"}})
in
    #"Renamed Columns"