Make hirarcy out of level column

[Anonymous]

Hi,

I'm realy struggeling with the logical setup of this information.

I have the data in a diffrent sheet, this is the level structure of the data that I will later connect to the actural data.

I want to make a hirarcy like this:

the CSV file is structured like this:

Code: Actural codes from one digite to 5 digits.

Parant code: corresponding code on level one.

Level: Level in hirarcy.  

So I want something like this:

Level 1	Level 2	Level 3	Level 4	Level 5
1 - University	10 - ITC	100 - Programming	1000 - Machine learning	10000 - Generel
1 - University	10 - ITC	100 - Programming	1000 - Machine learning	10001 - Special

Any ideas on how to solve this? 

I'm used to solving things like this with pivot columns and transposing. I have never coded to get a different structure. 

Regards,

[Anonymous]

Hi, Thank's for looking at the problem. I have got the outcome in Jimmy's solution. But where do I go from there? I don't see how this can help me make i hirarcy in the other table. If you can help me by giving me the solutions i PBI, I have the link to the CSV file here: Classification of education (NUS) - Statistics Norway (ssb.no)

[Jimmy801]

hello @Anonymous 

can you show me then please what outcome you would need of a list like you where referecing at?

You were just posting rows, where your hierarchy is on columns... so i suppose that the courses are on rows or can you explain in detail your scenario?

BR

Jimmy

[Vera_33]

Hi @Anonymous 

One way to do in M, paste in Advanced Editor

@Jimmy801 steal your sample data😁

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlDSUQIi38TMPKVYnWglQyDHACoQXJpkCBY0QhU0AgsaI3RCVJkAeYZIWuHaTTElgEbEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [code = _t, parentCode = _t, name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"code", Int64.Type}, {"parentCode", Int64.Type}}),
    ParentList = List.Buffer(#"Changed Type"[name]),
    ParentCodeList = List.Buffer(#"Changed Type"[parentCode]),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Combine( List.Reverse( {[name]} & List.Generate(()=>
[child = [name],
position = [parentCode],
parent = if position = null then child else ParentList{position},
parents =  parent],
each [position] <> null,
each [child = [parent],
      position = ParentCodeList{List.PositionOf(ParentList,child)},
      parent =  ParentList{position},
      parents = parent ],
each [parents])),";")),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom", "Custom", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Level 1", "Level 2", "Level 3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"name", type text}, {"Custom.1", type text}, {"Custom.2", type text}, {"Custom.3", type text}})
in
    #"Changed Type1"

[Jimmy801]

Hello @Anonymous 

I can give you a solution in DAX.

First add a new query calles "Courses"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlDSUQIi38TMPKVYnWglQyDHACoQXJpkCBY0QhU0AgsaI3RCVJkAeYZIWuHaTTElgEbEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [code = _t, parentCode = _t, name = _t])
in
    Source

then go to the desktop and add 3 new column

NewParentCode = 
IF (
COUNTROWS (
FILTER ( Courses,Courses[code] = EARLIER ( Courses[parentCode] ) )
)
= 0,
Courses[code],
Courses[parentCode]
)

Path = PATH (
Courses[code],
Courses[NewParentCode]
)

Level1 = 
LOOKUPVALUE (
Courses[name],
Courses[code], PATHITEM ( Courses[Path], 1 )
)

the outcome looks like this

it adds to every "course" its hierarchy of level 1, 2 etc.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy