Automatic creation of Parent/Child colums level

[Anonymous]

Hello PBI Community!

I'm having a really complicated time with a problem on a pbi hierarchy... 

The data that I received came like this:

I need to create each "level name" based on the path category, creating 4 colums, "level 1", "level 2", till "level 4"...

I was able to do so by typing:
---For first level---
IF(
  LEFT( TABLE[PATH CATEGORY], 2) = "01", "NAME 1",
 IF(LEFT(TABLE[PATH CATEGORY], 2) = "02", "NAME 5"
)

---For second level---
IF(LEFT( TABLE[PATH CATEGORY], 4) = "0101", "NAME 2",
IF(LEFT( TABLE[PATH CATEGORY], 4) = "0102", "NAME 3",
IF(LEFT( TABLE[PATH CATEGORY], 4) = "0201", NAME 6",
IF(LEFT( TABLE[PATH CATEGORY], 2) = "01", "NAME 1",
IF(LEFT(TABLE[PATH CATEGORY], 2) = "02", "NAME 2")

And doing so till fourth level,

Showing like this:

It worked well, but if later I need to add some names, categories or even paths, this will not work anymore because of my string input...

I don't know if its possible (i believe that is, but not sure), i need to find a way that will identify the names of all levels according to the paths, and update all the "colums levels"...

I had searched some cases with the same cenario, but didn't found (or maybe i made a bad search, if i did so, I'm sorry!!)

Someone could give me a hand? I'll stay trying till then, Thank You So Much!!!

[mangaus1111 · Accepted solution]

Hi @Anonymous ,

try this M script, it works

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"

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hello Mangaus!

Unfortunatelly this don't resolve my problem...

With your scrip i still need a string input, "[Name]" it will not identify and create by it self... I did this using DAX, like in my example...

I'm starting to think that it's not possible on M or DAX haha
 

[mangaus1111]

Hi @Anonymous ,

see my pbi file

https://1drv.ms/u/s!Aj45jbu0mDVJizuV9GDv1zIRcWNk?e=WhSnxg

The query in M is based on the lenght of the characters of the Column [Path], not on each single name of the Column [Name].

It is automatic in my opinion.

[Anonymous]

Hello @mangaus1111 ,
I'm really sorry, you was right, i read it wrong...
Your code really is what I need! I'll apply on my project now! Thank you so much!! I was cracking my head thinking about it haha

Thanks! 😄

[mangaus1111]

Hi @Anonymous ,

I guess this article can help you

https://www.daxpatterns.com/parent-child-hierarchies/

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hello Mangaus, thanks for reply my post!

Unfortunately this article didn't helped me, its completely different from my problem, if i had 2 colums that specifies the parent-child, it would be awesome hahah, but thanks!