Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

[Power Query] - Handle hierarchy table

Hi,

 

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

 

Raw DataRaw 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)

 

Shift data in proper levelShift data in proper level

 

Fill blanks with lowest level value (in yellow) :

Fill the gap with parent value (yellow)Fill the gap with parent value (yellow)

HELP !  🙂

 

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

@Anonymous 

 

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"

Regards
Zubair

Please try my custom visuals

View solution in original post

8 REPLIES 8
Zubair_Muhammad
Community Champion
Community Champion

@Anonymous 

 

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"

Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

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 !

Anonymous
Not applicable

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]))

@Anonymous 

 

I am sorry..I missed your second requirement

 

I will look into in an hour or before


Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

No problemo amigo

 

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

@Anonymous 

 

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"

Regards
Zubair

Please try my custom visuals

@Anonymous 

 

Bascially

 

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

Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.