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
Joe_Barry's avatar
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's avatar
    COIL-ibesmond
    Helper I
    1 year ago

    Yeah - I did some previous ETL.  The original data comes out in one row per person.

    Person1st Date1st Amt2nd Date2nd Amt3rd Date3rd Amt4th Date4th Amt5th Date5th Amt
    11/1/24500.001/5/24600.001/8/24200.001/12/241000.001/30/24600.00
    21/12/241200.001/30/241500.002/14/241600.002/16/241800.002/27/241500.00

     

    With the attempt of transforming it into:

     

    PersonActivity1st Date1st Amt
    11st 1/1/2024500
    21st 1/12/20241200
    12nd1/5/2024600
    22nd1/30/20241500
    13rd1/8/2024200
    23rd2/14/20241600
    14th1/12/20241000
    24th2/16/20241800
    15th1/30/2024600
    25th2/27/20241500
    • dufoq3's avatar
      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
    • AlienSx's avatar
      AlienSx
      Super User
      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