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: PersonValueTypeActivity128/2/2023 12:00:00 AMDateFirst Order12500AmtFirst Order126/21/2023 12:00:00 AMDateSecond Order12900AmtSecond Order128/31/2024 12:00:00 AMDateThird Order12750AmtThird Order126/21/2023 12:00:00 AMDateFourth Order12800AmtFourth Order128/1/2023 12:00:00 AMDateFifth Order12200AmtFifth Order121/1/9999 12:00:00 AMDateSixth Order12450AmtSixth Order1210/26/2022 5:30:00 PMDateSeventh Order12780AmtSeventh Order Into this table: PersonActivityDateAmt12First Order8/2/2023 12:00:00 AM50012Second Order6/21/2023 12:00:00 AM90012Third Order8/31/2024 12:00:00 AM75012Fourth Order6/21/2023 12:00:00 AM80012Fifth Order8/1/2023 12:00:00 AM20012Sixth Order1/1/9999 12:00:00 AM45012Seventh Order10/26/2022 5:30:00 PM780Thank you.

Helper I

1 year ago

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 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.

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

dufoq3

1 year ago

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

6 Replies

Omid_Motamedise

Super User

1 year ago

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

Solution Sage

1 year ago

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

Helper I

1 year ago

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

Community Champion

1 year ago

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

Anonymous

Not applicable

1 year ago

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

Forum Discussion

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

6 Replies

Related content

Transpose/ Transform Rows to Columns

Help Transposing

Transform and Transpose data

Table Transformation

Multiple Rows transpose to Column