Forum Discussion
Solved
Transforming data using Pivot, Unpivot and Transpose, Need to Pivot multiple columns
I've already done a bunch of ETL on this data, but I'm stuck trying to pivot (I think) Value and Type, so it shows Date and Amount as the column headers. I feel pivot is what I need, but you can onl...
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], activities = List.Buffer({1..(Table.ColumnCount(Source) - 1) / 2}), tr = List.TransformMany( Table.ToRows(Source), (x) => ((w) => List.Zip( { activities, List.Alternate(w, 1, 1, 1), List.Alternate(w, 1, 1) } ) )(List.Skip(x)), (x, y) => {x{0}} & y ), z = Table.FromRows(tr, {"Person", "Activity", "Date", "Amount"}) in zHi COIL-ibesmond, different approach:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc1RCsAgDAPQu/RbZpNp513E+1/DMWVd2V94JKR3gSRBRma5Q1U9VB+pS8ylLaELuAjqdmoYjtSFscx/Ge8xM8o2+5hta2684naMCQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Person = _t, #"1st Date" = _t, #"1st Amt" = _t, #"2nd Date" = _t, #"2nd Amt" = _t, #"3rd Date" = _t, #"3rd Amt" = _t, #"4th Date" = _t, #"4th Amt" = _t, #"5th Date" = _t, #"5th Amt" = _t]), Unpivoted = Table.UnpivotOtherColumns(Source, {"Person"}, "Attribute", "Value"), Splitted = Table.SplitColumn(Unpivoted, "Attribute", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Activity", "Attribute.2"}), Pivoted = Table.Pivot(Splitted, List.Distinct(Splitted[Attribute.2]), "Attribute.2", "Value"), ChangedType = Table.TransformColumnTypes(Pivoted,{{"Person", Int16.Type}, {"Date", type date}, {"Amt", type number}}, "en-US"), SortedRows = Table.Sort(ChangedType,{{"Activity", Order.Ascending}, {"Person", Order.Ascending}}) in SortedRows
Yeah - I did some previous ETL. The original data comes out in one row per person.
| Person | 1st Date | 1st Amt | 2nd Date | 2nd Amt | 3rd Date | 3rd Amt | 4th Date | 4th Amt | 5th Date | 5th Amt |
| 1 | 1/1/24 | 500.00 | 1/5/24 | 600.00 | 1/8/24 | 200.00 | 1/12/24 | 1000.00 | 1/30/24 | 600.00 |
| 2 | 1/12/24 | 1200.00 | 1/30/24 | 1500.00 | 2/14/24 | 1600.00 | 2/16/24 | 1800.00 | 2/27/24 | 1500.00 |
With the attempt of transforming it into:
| Person | Activity | 1st Date | 1st Amt |
| 1 | 1st | 1/1/2024 | 500 |
| 2 | 1st | 1/12/2024 | 1200 |
| 1 | 2nd | 1/5/2024 | 600 |
| 2 | 2nd | 1/30/2024 | 1500 |
| 1 | 3rd | 1/8/2024 | 200 |
| 2 | 3rd | 2/14/2024 | 1600 |
| 1 | 4th | 1/12/2024 | 1000 |
| 2 | 4th | 2/16/2024 | 1800 |
| 1 | 5th | 1/30/2024 | 600 |
| 2 | 5th | 2/27/2024 | 1500 |
Hi COIL-ibesmond, different approach:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc1RCsAgDAPQu/RbZpNp513E+1/DMWVd2V94JKR3gSRBRma5Q1U9VB+pS8ylLaELuAjqdmoYjtSFscx/Ge8xM8o2+5hta2684naMCQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Person = _t, #"1st Date" = _t, #"1st Amt" = _t, #"2nd Date" = _t, #"2nd Amt" = _t, #"3rd Date" = _t, #"3rd Amt" = _t, #"4th Date" = _t, #"4th Amt" = _t, #"5th Date" = _t, #"5th Amt" = _t]),
Unpivoted = Table.UnpivotOtherColumns(Source, {"Person"}, "Attribute", "Value"),
Splitted = Table.SplitColumn(Unpivoted, "Attribute", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Activity", "Attribute.2"}),
Pivoted = Table.Pivot(Splitted, List.Distinct(Splitted[Attribute.2]), "Attribute.2", "Value"),
ChangedType = Table.TransformColumnTypes(Pivoted,{{"Person", Int16.Type}, {"Date", type date}, {"Amt", type number}}, "en-US"),
SortedRows = Table.Sort(ChangedType,{{"Activity", Order.Ascending}, {"Person", Order.Ascending}})
in
SortedRows