Forum Discussion

COIL-ibesmond's avatar
COIL-ibesmond
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 onl...
Need Help
Tutorial Requests
  • AlienSx's avatar
    AlienSx
    1 year ago
    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's avatar
    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
Anonymous's avatar
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