[Power Query] - Handle hierarchy table

[Anonymous]

Hi,

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

Raw 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 level

Fill blanks with lowest level value (in 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 : 

Level0	Level1	Level2	Level3	Level4	Level5	Level6
1724310	P0310121	P0310120	P0310100	P0310000	P0300000	TP000000
1724320	P0310221	P0310220	P0310200	P0310000	P0300000	TP000000
1724710	P0310122	P0310120	P0310100	P0310000	P0300000	TP000000
1724720	P0310222	P0310220	P0310200	P0310000	P0300000	TP000000
1726210	P0310113	P0310110	P0310100	P0310000	P0300000	TP000000
1800000	A0410200	A0410000	A0400000	TA000000
1800001	A0410200	A0410000	A0400000	TA000000
1801000	A0100000	TA000000
1801001	A0100000	TA000000
1910101	A0410310	A0410300	A0410000	A0400000	TA000000
1910102	A0410310	A0410300	A0410000	A0400000	TA000000
1910104	A0410310	A0410300	A0410000	A0400000	TA000000
1910106	A0410310	A0410300	A0410000	A0400000	TA000000
1910107	A0410310	A0410300	A0410000	A0400000	TA000000
1910108	A0410310	A0410300	A0410000	A0400000	TA000000
1910201	A0410320	A0410300	A0410000	A0400000	TA000000
1910202	A0410320	A0410300	A0410000	A0400000	TA000000
1910204	A0410320	A0410300	A0410000	A0400000	TA000000
1910206	A0410320	A0410300	A0410000	A0400000	TA000000
1910207	A0410320	A0410300	A0410000	A0400000	TA000000
1910208	A0410320	A0410300	A0410000	A0400000	TA000000
1912051AS	A0704130	A0704100	A0704000	A0700000	TA000000
1912057AS	A0704130	A0704100	A0704000	A0700000	TA000000
1991256AS	A0704200	A0704000	A0700000	TA000000
1999130	A0410410	A0410400	A0410000	A0400000	TA000000
1999230	A0410420	A0410400	A0410000	A0400000	TA000000
2011001	A0420117	A0420110	A0420100	A0420000	A0400000	TA000000
2011002	A0420117	A0420110	A0420100	A0420000	A0400000	TA000000
2011004	A0420117	A0420110	A0420100	A0420000	A0400000	TA000000
2011007	A0420117	A0420110	A0420100	A0420000	A0400000	TA000000
2011008	A0420117	A0420110	A0420100	A0420000	A0400000	TA000000

[Zubair_Muhammad]

@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"

[Anonymous]

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]

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

[Zubair_Muhammad]

@Anonymous 

I am sorry..I missed your second requirement

I will look into in an hour or before

[Anonymous]

No problemo amigo

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

[Zubair_Muhammad · 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"

[Zubair_Muhammad]

@Anonymous 

Bascially

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

[Anonymous]

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