cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LAPORTES Regular Visitor
Regular Visitor

[Power Query] - Handle hierarchy table

Hi,

 

here is my little issue : a hierarchy table that look like this

 

Hierarchy01.pngRaw Data

 

I need to shift my data so that the last completed level goes into the Level6 column :

(child data shift as well to n-1 level creating blanks)

 

Hierarchy02.pngShift data in proper level

 

Fill blanks with lowest level value (in yellow) :

Hierarchy03.pngFill the gap with parent value (yellow)

HELP !  Smiley Happy

 

I struggled a couple hours with no success...

(one last thing : i prefer doing this in query, not DAX)

 

Thanks

 

Steph

 

 

Data : 

 

Level0Level1Level2Level3Level4Level5Level6
1724310P0310121P0310120P0310100P0310000P0300000TP000000
1724320P0310221P0310220P0310200P0310000P0300000TP000000
1724710P0310122P0310120P0310100P0310000P0300000TP000000
1724720P0310222P0310220P0310200P0310000P0300000TP000000
1726210P0310113P0310110P0310100P0310000P0300000TP000000
1800000A0410200A0410000A0400000TA000000  
1800001A0410200A0410000A0400000TA000000  
1801000A0100000TA000000    
1801001A0100000TA000000    
1910101A0410310A0410300A0410000A0400000TA000000 
1910102A0410310A0410300A0410000A0400000TA000000 
1910104A0410310A0410300A0410000A0400000TA000000 
1910106A0410310A0410300A0410000A0400000TA000000 
1910107A0410310A0410300A0410000A0400000TA000000 
1910108A0410310A0410300A0410000A0400000TA000000 
1910201A0410320A0410300A0410000A0400000TA000000 
1910202A0410320A0410300A0410000A0400000TA000000 
1910204A0410320A0410300A0410000A0400000TA000000 
1910206A0410320A0410300A0410000A0400000TA000000 
1910207A0410320A0410300A0410000A0400000TA000000 
1910208A0410320A0410300A0410000A0400000TA000000 
1912051ASA0704130A0704100A0704000A0700000TA000000 
1912057ASA0704130A0704100A0704000A0700000TA000000 
1991256ASA0704200A0704000A0700000TA000000  
1999130A0410410A0410400A0410000A0400000TA000000 
1999230A0410420A0410400A0410000A0400000TA000000 
2011001A0420117A0420110A0420100A0420000A0400000TA000000
2011002A0420117A0420110A0420100A0420000A0400000TA000000
2011004A0420117A0420110A0420100A0420000A0400000TA000000
2011007A0420117A0420110A0420100A0420000A0400000TA000000
2011008A0420117A0420110A0420100A0420000A0400000TA000000
1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: [Power Query] - Handle hierarchy table

@LAPORTES 

 

Check this

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZRLDoMwDESvUrFmYQ8BhyU3QGp3iPtfo+ETO1JFxccLxCPKvIxCYJoqFoSGqaqrkdKdwYY2SoqUkXb8jLThXO82y8FsKEav2KTshqfdpOyGh906FN24MbzRLebhgUJusaKOam6gjK/1sjw/yXOeyYczfxN8JdEvG6Idt0O34dm6poGPJvhoOh+N+GiihwbFm8ITDXw0wUfT+WjERxMdNKCWh/c6T1KoIUVSJMX/IvEQJVPbmQhn04Whz6untYMd4nBta/oehQb3NFj+5/lbWB7EkBRJ8dhoNrjagqtNXG3RwzZ/AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Level0 = _t, Level1 = _t, Level2 = _t, Level3 = _t, Level4 = _t, Level5 = _t, Level6 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Level0", type text}, {"Level1", type text}, {"Level2", type text}, {"Level3", type text}, {"Level4", type text}, {"Level5", type text}, {"Level6", type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Changed Type",{ "Level2", "Level3", "Level4", "Level5", "Level6"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged"),
    #"Added Custom" = Table.AddColumn(#"Merged Columns", "Custom", each List.RemoveItems(Text.Split([Merged],"|"),{" ",null})),
    AddedCustom1 = Table.AddColumn(#"Added Custom", "ListCount", each List.Count([Custom])),
    MaxCount=Table.AddColumn(AddedCustom1, "MaxCount", each List.Max(AddedCustom1[ListCount])),
    #"Added Custom1" = Table.AddColumn(MaxCount, "Custom.1", each List.InsertRange([Custom],0,List.Repeat({List.First([Custom])},[MaxCount]-[ListCount]))),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Merged", "Custom", "ListCount", "MaxCount"}),
    #"Extracted Values" = Table.TransformColumns(#"Removed Columns", {"Custom.1", each Text.Combine(List.Transform(_, Text.From), "|"), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom.1", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Level 3", "Level 4", "Level 5", "Level 6", "Level 7"})
in
    #"Split Column by Delimiter"
8 REPLIES 8
Super User
Super User

Re: [Power Query] - Handle hierarchy table

@LAPORTES 

 

Please see if this M solution helps

 

File attached as well

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZRLDoMwDESvUrFmYQ8BhyU3QGp3iPtfo+ETO1JFxccLxCPKvIxCYJoqFoSGqaqrkdKdwYY2SoqUkXb8jLThXO82y8FsKEav2KTshqfdpOyGh906FN24MbzRLebhgUJusaKOam6gjK/1sjw/yXOeyYczfxN8JdEvG6Idt0O34dm6poGPJvhoOh+N+GiihwbFm8ITDXw0wUfT+WjERxMdNKCWh/c6T1KoIUVSJMX/IvEQJVPbmQhn04Whz6untYMd4nBta/oehQb3NFj+5/lbWB7EkBRJ8dhoNrjagqtNXG3RwzZ/AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Level0 = _t, Level1 = _t, Level2 = _t, Level3 = _t, Level4 = _t, Level5 = _t, Level6 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Level0", type text}, {"Level1", type text}, {"Level2", type text}, {"Level3", type text}, {"Level4", type text}, {"Level5", type text}, {"Level6", type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Changed Type",{ "Level2", "Level3", "Level4", "Level5", "Level6"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged"),
    #"Added Custom" = Table.AddColumn(#"Merged Columns", "Custom", each List.RemoveItems(Text.Split([Merged],"|"),{" ",null})),
    AddedCustom1 = Table.AddColumn(#"Added Custom", "ListCount", each List.Count([Custom])),
    MaxCount=Table.AddColumn(AddedCustom1, "MaxCount", each List.Max(AddedCustom1[ListCount])),
    #"Added Custom1" = Table.AddColumn(MaxCount, "Custom.1", each List.InsertRange([Custom],0,List.Repeat({""},[MaxCount]-[ListCount]))),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Merged", "Custom", "ListCount", "MaxCount"}),
    #"Extracted Values" = Table.TransformColumns(#"Removed Columns", {"Custom.1", each Text.Combine(List.Transform(_, Text.From), "|"), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom.1", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Level 3", "Level 4", "Level 5", "Level 6", "Level 7"})
in
    #"Split Column by Delimiter"
Highlighted
LAPORTES Regular Visitor
Regular Visitor

Re: [Power Query] - Handle hierarchy table

Almost perfect ! Thank you @Zubair_Muhammad 

 

What i see (in my Power BI sept 2018) : i copy/paste the M code, and my hierarchy is as described in my 2nd printscreen. My level values have been fairly shift on the right, this is perfect. Just a few steps, this is great.

 

Now i need to fill the blanks. I try to do it. If you a faster than me (and i bet you will), i take the answer.

 

Anyway huge thanks !

LAPORTES Regular Visitor
Regular Visitor

Re: [Power Query] - Handle hierarchy table

mmh i think i got it

 

I could add a step to get the left([Merged];8)  and put this instead of the " "  in the function below

 

List.InsertRange([Custom],0,List.Repeat({""},[MaxCount]-[ListCount]))

Super User
Super User

Re: [Power Query] - Handle hierarchy table

@LAPORTES 

 

I am sorry..I missed your second requirement

 

I will look into in an hour or before

LAPORTES Regular Visitor
Regular Visitor

Re: [Power Query] - Handle hierarchy table

No problemo amigo

 

i'll try to find on my own in the meanwhile

Super User
Super User

Re: [Power Query] - Handle hierarchy table

@LAPORTES 

 

Check this

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZRLDoMwDESvUrFmYQ8BhyU3QGp3iPtfo+ETO1JFxccLxCPKvIxCYJoqFoSGqaqrkdKdwYY2SoqUkXb8jLThXO82y8FsKEav2KTshqfdpOyGh906FN24MbzRLebhgUJusaKOam6gjK/1sjw/yXOeyYczfxN8JdEvG6Idt0O34dm6poGPJvhoOh+N+GiihwbFm8ITDXw0wUfT+WjERxMdNKCWh/c6T1KoIUVSJMX/IvEQJVPbmQhn04Whz6untYMd4nBta/oehQb3NFj+5/lbWB7EkBRJ8dhoNrjagqtNXG3RwzZ/AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Level0 = _t, Level1 = _t, Level2 = _t, Level3 = _t, Level4 = _t, Level5 = _t, Level6 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Level0", type text}, {"Level1", type text}, {"Level2", type text}, {"Level3", type text}, {"Level4", type text}, {"Level5", type text}, {"Level6", type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Changed Type",{ "Level2", "Level3", "Level4", "Level5", "Level6"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged"),
    #"Added Custom" = Table.AddColumn(#"Merged Columns", "Custom", each List.RemoveItems(Text.Split([Merged],"|"),{" ",null})),
    AddedCustom1 = Table.AddColumn(#"Added Custom", "ListCount", each List.Count([Custom])),
    MaxCount=Table.AddColumn(AddedCustom1, "MaxCount", each List.Max(AddedCustom1[ListCount])),
    #"Added Custom1" = Table.AddColumn(MaxCount, "Custom.1", each List.InsertRange([Custom],0,List.Repeat({List.First([Custom])},[MaxCount]-[ListCount]))),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Merged", "Custom", "ListCount", "MaxCount"}),
    #"Extracted Values" = Table.TransformColumns(#"Removed Columns", {"Custom.1", each Text.Combine(List.Transform(_, Text.From), "|"), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom.1", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Level 3", "Level 4", "Level 5", "Level 6", "Level 7"})
in
    #"Split Column by Delimiter"
Super User
Super User

Re: [Power Query] - Handle hierarchy table

@LAPORTES 

 

Bascially

 

=List.InsertRange([Custom],0,List.Repeat({List.First([Custom])},[MaxCount]-[ListCount]))
LAPORTES Regular Visitor
Regular Visitor

Re: [Power Query] - Handle hierarchy table

PERFECT !!!!!!!!!! u made my day   thank you !!!