Converting a table of 2 columns (no values) to a 2 dimensional matrix layout (nothing to aggregate

[sliceNdiceUup]

Hi Guys,

Seeking for some genious solution here. I am familiar with transpose, unpivot etc but I think we will need to dig deeper here.

I have a table of 2 columns. column 1: Categoires  column 2: subcategories and would like to convert it to a matrix as indicated below:

[Vijay_A_Verma · Accepted solution]

Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXI0VIrVgTKNEExjMNMJyHQyRDAhCpyBTGdDBBNJFKgtFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, Subcategory = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Category"}, {{"Temp", each _, type table [Category=nullable text, Subcategory=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Temp],"Index")),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Subcategory", "Index"}, {"Subcategory", "Index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Temp"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Category]), "Category", "Subcategory"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns1"

Another alternative is Transpose

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXI0VIrVgTKNEExjMNMJyHQyRDAhCpyBTGdDBBNJFKgtFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, Subcategory = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Category"}, {{"Temp", each _, type table [Category=nullable text, Subcategory=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each [Temp][Subcategory]),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"Temp"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Custom", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), List.Transform({1..List.Max(Table.AddColumn(#"Removed Columns", "Temp", each List.Count(Text.Split([Custom],",")))[Temp])},each "Custom." & Number.ToText(_))),
    #"Transposed Table" = Table.Transpose(#"Split Column by Delimiter"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true])
in
    #"Promoted Headers"

[sliceNdiceUup]

Appreciate the quick help mate. I need to get my head around the logic of these scripts. It was interesting that when using the built in pivot function the list count feature had to be manually removed to get the outcome.