Rebuild hierarchical table

[Anonymous]

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.

[Anonymous]

It works. Thank you

[Payeras_BI · Accepted 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"

[kumar27]

Very well explained.Kudos

[Payeras_BI]

Thank you @kumar27