Unpivot multiple category columns into attributes and values

This can be done via List.Range and Table.SelectColumns.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcssvylUwVNJRckktzkzPAzKc83NzU/NKwIKG+ob6RgZGptjkjYFsI30TiHysDpJRzvl5xSVFpcklmfnIGoyAbFN9Q2OQDhPcykzA5hoZQZTBDTbC5gZTsJFmMBMx5M2BbAt9CyxG4bDcDORpQ4SJOJRZANnmcK/ExgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, A.Type = _t, A.Comments = _t, A.Date = _t, B.Type = _t, B.Comments = _t, B.Date = _t]),
    Process = (tbl,col)=>
        let
            s = Table.SelectColumns(tbl,{"ID"} & col),
            newcol = List.Transform(col, each if Text.Length(_)>2 then Text.Range(_,2) else _),
            t = Table.RenameColumns(s,List.Zip({col,newcol}))
        in
            Table.AddColumn(t,"Category",each Text.Start(col{1},1)),
    Combined = Process(Source,List.Range(Table.ColumnNames(Source),1,3)) & Process(Source, List.Range(Table.ColumnNames(Source),4,3)),
    #"Reordered Columns" = Table.ReorderColumns(Combined,{"ID", "Category", "Type", "Comments", "Date"})
in
    #"Reordered Columns"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the entire Source step with your own source.

hello arpitprakash89 

please check if this accomodate your need.

1. Merged type, comment, and date of A with any delimiter (i used colon). then put "A" as column name.

2. do exact same for "B"

3. unpivot A and B column

4. split the value column by colon delimiter since i used colon as delimiter when merging

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcssvylUwVNJRckktzkzPAzKc83NzU/NKwIKG+ob6RgZGptjkjYFsI30TiHysDpJRzvl5xSVFpcklmfnIGoyAbFN9Q2OQDhPcykzA5hoZQZTBDTbC5gZTsJFmMBMx5M2BbAt9CyxG4bDcDORpQ4SJOJRZANnmcK/ExgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"A.Type " = _t, A.Comments = _t, A.Date = _t, B.Type = _t, B.Comments = _t, B.Date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"A.Type ", type text}, {"A.Comments", type text}, {"A.Date", type date}, {"B.Type", type text}, {"B.Comments", type text}, {"B.Date", type date}}),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type", {{"A.Date", type text}}, "en-US"),{"A.Type ", "A.Comments", "A.Date"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"A"),
#"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns", {{"B.Date", type text}}, "en-US"),{"B.Type", "B.Comments", "B.Date"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"B"),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Merged Columns1", {"ID"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Value", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Value.1", "Value.2", "Value.3"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Value.1", type text}, {"Value.2", type text}, {"Value.3", type date}})
in
#"Changed Type1"

Hope this will help.

Thank you.