Forum Discussion
Solved
Table with Primary Key and Values Across Columns to values Down Columns
I have a data export that has a primary key and sales values for 12 months along columns, like so
| Key | Month1Sales | Month1Date | Month2Sales | Month2Date | Month3Sales | Month3Date |
| 001 | 34 | Jan 2022 | 394 | Feb 2022 | 346 | March 2022 |
| 002 | 45 | Feb 2022 | 57 | March 2022 | 5668 | April 2022 |
and I want to make another table based on this that has multiple entries for the Key, and sales down 1 column, like this
| Key | Date | Sales |
| 001 | Jan 2022 | 34 |
| 001 | Feb 2022 | 394 |
| 001 | March 2022 | 346 |
| 002 | Feb 2022 | 45 |
| 002 | March 2022 | 57 |
| 002 | April 2022 | 5668 |
There are other columns in the original table that I want to keep there but not import to the new table.
How do I do this?
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjAwVNJRMjYBEl6JeQpGBkZGIL4lSMAtNQkuYGIGJH0TiyACsTognSAJE1NUhabmyOqAfDMzCyDlWADTGQsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Key = _t, Month1Sales = _t, Month1Date = _t, Month2Sales = _t, Month2Date = _t, Month3Sales = _t, Month3Date = _t]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Key"}, "Attribute", "Date"), DateTbl = Table.SelectRows(#"Unpivoted Other Columns", each Text.EndsWith([Attribute],"Date")), DateTblIndex = Table.AddIndexColumn(DateTbl, "Index", 0, 1, Int64.Type), SalesTbl = Table.SelectRows(#"Unpivoted Other Columns", each Text.EndsWith([Attribute],"Sales")), SalesTblIndex = Table.AddIndexColumn(SalesTbl, "Index", 0, 1, Int64.Type), #"Merged Queries" = Table.NestedJoin(DateTblIndex, {"Index"}, SalesTblIndex, {"Index"}, "SalesTblIndex", JoinKind.LeftOuter), #"Expanded SalesTblIndex" = Table.ExpandTableColumn(#"Merged Queries", "SalesTblIndex", {"Date"}, {"Sales"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded SalesTblIndex",{"Attribute", "Index"}) in #"Removed Columns"
1 Reply
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjAwVNJRMjYBEl6JeQpGBkZGIL4lSMAtNQkuYGIGJH0TiyACsTognSAJE1NUhabmyOqAfDMzCyDlWADTGQsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Key = _t, Month1Sales = _t, Month1Date = _t, Month2Sales = _t, Month2Date = _t, Month3Sales = _t, Month3Date = _t]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Key"}, "Attribute", "Date"), DateTbl = Table.SelectRows(#"Unpivoted Other Columns", each Text.EndsWith([Attribute],"Date")), DateTblIndex = Table.AddIndexColumn(DateTbl, "Index", 0, 1, Int64.Type), SalesTbl = Table.SelectRows(#"Unpivoted Other Columns", each Text.EndsWith([Attribute],"Sales")), SalesTblIndex = Table.AddIndexColumn(SalesTbl, "Index", 0, 1, Int64.Type), #"Merged Queries" = Table.NestedJoin(DateTblIndex, {"Index"}, SalesTblIndex, {"Index"}, "SalesTblIndex", JoinKind.LeftOuter), #"Expanded SalesTblIndex" = Table.ExpandTableColumn(#"Merged Queries", "SalesTblIndex", {"Date"}, {"Sales"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded SalesTblIndex",{"Attribute", "Index"}) in #"Removed Columns"