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.
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 to n-1 level creating blanks)
Fill blanks with lowest level value (in 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 |
Solved! Go to 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"
@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
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"
@Anonymous
Bascially
=List.InsertRange([Custom],0,List.Repeat({List.First([Custom])},[MaxCount]-[ListCount]))
PERFECT !!!!!!!!!! u made my day thank you !!!
User | Count |
---|---|
113 | |
106 | |
83 | |
67 | |
42 |
User | Count |
---|---|
157 | |
110 | |
84 | |
80 | |
60 |