Transforming data using Pivot, Unpivot and Transpose, Need to Pivot multiple columns

[COIL-ibesmond]

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 only pivot on column.  I need to pivot two columns.  I would prefer to avoid splitting the data and merging them back together. I tried to concatenate Type and Value, and then pivot the data, but I got an error on the rows with dates and wasn't sure how to proceed, so I didn't try to pivot the concatenated column.

Can someone assist me with how to transform this table:

Person	Value	Type	Activity
12	8/2/2023 12:00:00 AM	Date	First Order
12	500	Amt	First Order
12	6/21/2023 12:00:00 AM	Date	Second Order
12	900	Amt	Second Order
12	8/31/2024 12:00:00 AM	Date	Third Order
12	750	Amt	Third Order
12	6/21/2023 12:00:00 AM	Date	Fourth Order
12	800	Amt	Fourth Order
12	8/1/2023 12:00:00 AM	Date	Fifth Order
12	200	Amt	Fifth Order
12	1/1/9999 12:00:00 AM	Date	Sixth Order
12	450	Amt	Sixth Order
12	10/26/2022 5:30:00 PM	Date	Seventh Order
12	780	Amt	Seventh Order

Into this table:

Person	Activity	Date	Amt
12	First Order	8/2/2023 12:00:00 AM	500
12	Second Order	6/21/2023 12:00:00 AM	900
12	Third Order	8/31/2024 12:00:00 AM	750
12	Fourth Order	6/21/2023 12:00:00 AM	800
12	Fifth Order	8/1/2023 12:00:00 AM	200
12	Sixth Order	1/1/9999 12:00:00 AM	450
12	Seventh Order	10/26/2022 5:30:00 PM	780

Thank you.

[AlienSx · Accepted solution]

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
    z

[v-cgao-msft]

Hi @COIL-ibesmond ,

You can also filter and then merge queries:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZFdC8IgFIb/ing9UI9zX3eD2F0U1N3YRTRjXrSBWfTzsxGk6ZaIgjzvw/GctsUMcIILAgQocMSgotRuVG/t8+ZkpL0apW8G7XQvNe6ST0RQas/6apaAjABbkR7keRr731DpWONEQfisTePa46B0kMnF1xoF/tTaTHdthqAStwNxgqxa1SWMgNfWCMCss7RroavqGUZS5/tRgFEC2btSQKLis3TvjeohxzCVF+6sPKR7AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Person = _t, Value = _t, Type = _t, Activity = _t]),
    tab1 = Table.SelectRows(Source, each ([Type] = "Date")),
    tab2 = Table.SelectRows(Source, each ([Type] = "Amt")),
    #"Merged Queries" = Table.NestedJoin(tab1, {"Person", "Activity"}, tab2, {"Person", "Activity"}, "tab2", JoinKind.LeftOuter),
    #"Expanded tab2" = Table.ExpandTableColumn(#"Merged Queries", "tab2", {"Value"}, {"tab2.Value"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded tab2",{"Type"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Value", "Date"}, {"tab2.Value", "Amt"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type datetime}, {"Amt", Int64.Type}})
in
    #"Changed Type"

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Omid_Motamedise]

use this formula

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZFdC8IgFIb/ing9UM++3O4GsbsoqLvhRTRjXmwDs+jnZyOYS7dEFOR5H47nNA1mgCPMCRCgECMGJaV2o2pvn3cXI+1VK3036KBbqbGIvpGUUntWvVkDMgJsQ3qS13Fof0OFYw0TnMSTNglrz53SXiZPZ2sQ+FNrPT606bxK3A6ECbJpVTc/Aou2BgBmnYVdK11VLz+SON8PAowSyD6VAkrLeJIeF6N6ysFP5dyd1YxgId4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Person = _t, Value = _t, Type = _t, Activity = _t]),
    Custom1 = Table.Split(Source,2),
    Custom2 = List.Transform(Custom1,each [Person=_{0}[Person],Date=_{0}[Value],Activity=_{0}[Activity],AMT=_{1}[Value]]),
    Custom3 = Table.FromRecords(Custom2)
in
    Custom3

[Joe_Barry]

Hi @COIL-ibesmond 

Does the dat come out of the source like that? It looks like it's been unpivoted already, check the steps in your query and test by removing the step.

If it hasn't been unpivoted, just highlight the Type column, then go to the Transform tab in the ribbon and click on Pivot column. In the Values Column field make sure the Value columni schosen and Advanced Options choose Don't Aggregate

Then you get the result you're looking for

Joe

[COIL-ibesmond]

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

[dufoq3 · Accepted solution]

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

[AlienSx · Accepted solution]

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
    z