Forum Discussion
[Power Query] - Handle hierarchy table
Hi, here is my little issue : a hierarchy table that look like this I need to shift my data so that the last completed level goes into the Level6 column : (child data shift as well ...
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"
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"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 !
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