Forum Discussion
Unpivot multiple category columns into attributes and values
Hi,
I am trying to transform the following table -
| ID | A.Type | A.Comments | A.Date | B.Type | B.Comments | B.Date |
| Form 1 | Design | Comment 1 | 1/1/2025 | Design | Comment 3 | 2/4/2025 |
| Form 1 | Construction | Comment 2 | 5/13/2024 | Construction | Comment 4 | 2/22/2024 |
| Form 2 | Design | Comment 5 | 5/6/2024 | Design | Comment 7 | 8/8/2024 |
| Form 2 | Construction | Comment 6 | 11/6/2024 | Construction | Comment 8 | 7/13/2024 |
into this -
| ID | Category | Type | Comments | Date |
| Form 1 | A | Design | Comment 1 | 1/1/2025 |
| Form 1 | A | Construction | Comment 2 | 5/13/2024 |
| Form 1 | B | Design | Comment 5 | 2/4/2025 |
| Form 1 | B | Construction | Comment 6 | 2/22/2024 |
| Form 2 | A | Design | Comment 5 | 5/6/2024 |
| Form 2 | A | Construction | Comment 6 | 11/6/2024 |
| Form 2 | B | Design | Comment 7 | 8/8/2024 |
| Form 2 | B | Construction | Comment 8 | 7/13/2024 |
I can do this by manually unpivoting each category one by one, which leads to many duplicates. Then I check for unique values across all the columns and delete the duplicates. This is for about 10 categories and is slowing the query down.
Looking for a much more efficient process, using Power Query M.
Appreciate the assistance!
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.
13 Replies
- lbendlin
Super User
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.
This is a little more advanced for me, but looks efficient! Will have to tweak this a bit for my production data, but will learn tons in the process. Appreciate the assistance!
Would it be possible to make your script a little more flexible? It works with the sample data I provided, however, my production data is a bit more complex.
Instead of the first character for Categories, I need to use the position of '.' as an identifier. This is because the Categories can be a longer string than just one character. Eg. "ProjectA.Comment"
Also, the columns are not always sequential. For Category A, it could be {Type, Comment, Date} but Category B may be {Comment, Type, Date}.
Sorry, should have clarified in the original question 😞- lbendlin
Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.
Irwanhello 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.
Wow, this is very simple and creative, thank you!