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.
Hello everyone,
my export table looks so like:
How can I transform it quick into a true hiararchical table? Also so, that I have a pair Category-Subcategory (or no subcategory, only overall value for the Category) in every row.
Solved! Go to Solution.
Hi @Anonymous ,
I understand you want to go from this:
to this:
If so, try with this:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wck4sSU3PL6pUMFTSUQIiY6VYnWgQI7g0KRlZzhCbhBEuCWO4hDOyWkxRY2RRnNbClZvQ2pWmWF1pBhE1oba1aBImEIlYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, Subcategory = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}, {"Subcategory", type text}, {"Value", Int64.Type}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"Category", "Subcategory"}),
#"Filled Down" = Table.FillDown(#"Replaced Value",{"Category"}),
#"With Subcategory" = Table.SelectRows(#"Filled Down", each ([Subcategory] <> null)),
#"Without Subcategory to keep" = Table.RemoveColumns(Table.NestedJoin(Table.SelectRows(#"Filled Down", each ([Subcategory] = null)), {"Category"},#"With Subcategory" , {"Category"}, "Custom", JoinKind.LeftAnti),{"Custom"}),
#"Appended Query" = Table.Combine({#"With Subcategory", #"Without Subcategory to keep"}),
#"Sorted Rows" = Table.Sort(#"Appended Query",{{"Category", Order.Ascending}, {"Subcategory", Order.Ascending}})
in
#"Sorted Rows"
It works. Thank you
Hi @Anonymous ,
I understand you want to go from this:
to this:
If so, try with this:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wck4sSU3PL6pUMFTSUQIiY6VYnWgQI7g0KRlZzhCbhBEuCWO4hDOyWkxRY2RRnNbClZvQ2pWmWF1pBhE1oba1aBImEIlYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, Subcategory = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}, {"Subcategory", type text}, {"Value", Int64.Type}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"Category", "Subcategory"}),
#"Filled Down" = Table.FillDown(#"Replaced Value",{"Category"}),
#"With Subcategory" = Table.SelectRows(#"Filled Down", each ([Subcategory] <> null)),
#"Without Subcategory to keep" = Table.RemoveColumns(Table.NestedJoin(Table.SelectRows(#"Filled Down", each ([Subcategory] = null)), {"Category"},#"With Subcategory" , {"Category"}, "Custom", JoinKind.LeftAnti),{"Custom"}),
#"Appended Query" = Table.Combine({#"With Subcategory", #"Without Subcategory to keep"}),
#"Sorted Rows" = Table.Sort(#"Appended Query",{{"Category", Order.Ascending}, {"Subcategory", Order.Ascending}})
in
#"Sorted Rows"
Very well explained.Kudos
Thank you @kumar27
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.