Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Unpivoting and Pivoting Rows

Hello Power BI Community,   I am having an issue with Pivoting a table in Power Query Editor after I unpivot some columns and could use some help. I'm sure it's something simple, but I have tried t...
Need Help
Show and Tell
Tips and Tricks
  • lbendlin's avatar
    lbendlin
    1 year ago
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lVXJaiRHEP2VRug2QRJLrlfrNDDGZhZfBh0aTWHESN2gGRn8936RmYXbdJcbnSoiqyqW9+JFfv168+uXD7/d7d7tlrvj8/Puhm7u9odvf7vx+fhz/7T7cnj8ufvj+PT6vOBMyHImjXXY0ki5TDvivA2bC5Vs8zxRsnZzTxdyfTp+22+lslao9iBRG2XxNDF5sAQrsRFLt0xJmC8nuDsevz8uP7ZyJBPikmcUrj1ezFTV86aCvLl0S9CcbeRYAftl/2M5w0tLpojyuo3yRcq0mazpsGOjVOs8r1Q4/z9eFzJZrJR72ZbRVPJoVr0BbyoKwIzardata3BdSBE1USuDEabMPbAhnPXAKRNHbzRGobyZYkXr/eHhZXleDuesVK/Q68+RuHnEbFRTJ6KSlM4SSLINQlaUtjMAb+2l5kop+dDmRLX4SQVFvUcRdTKuArWdRRixeMQSirUPAWdqTdxSA2F9GHy+Wa7gdTK/H5e/lsOrp7gtJA3Csxqqk3Dr0QuZxVDb9AvA4xpaXP1EUSSojv/ZilcUykarFzR6kj9C7Kg+crA4/ITBxjCHDiv8Jr4MJHTZ3npuUJc1xOlnfC+or27siMsSPikB+wgttyKhSwu+AVptFiQPPxVQyTEkni17iVZCm5DVBva1hLIhuwsK/0/+qkpFJTQbfvG1whq6QtyH5rilkGa+AvkklpDb+n/t38sVCi6md3bBQiyhK8R9CKhUtDMZUGGkl9B3AvyIGQcEoU7GYsbui5iQ9IatcFIBplyjL62gefjsygXHfTHANwDuFXXNwU8F7wUNz/cxN7LKga+t2DOxnQKBJZ0kz8FX3FNmOpvUhALFQl+B3WvcJh+KAW61+cC+bZ2cQgCpa9NQxgRmBikAeMwbbsOcmwsMXsNsVMgv+w64FZIK7lNzeb5505wUIL61SHMMbQbWhCVnK8vwFTtIShAefsTFlDTNIuErLipMUbnGwcflz9en/cvu95fHh9H8qu1geT7LfNbx3LoMVmjPQgqvffDEVFY4/cSmkbdq/Re089Aa2FYjTmNsDhhjK8JA6ff3/wA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Market = _t, Product = _t, Metrics = _t, #"09/03/2023" = _t, #"09/10/2023" = _t, #"09/17/2023" = _t, #"09/24/2023" = _t, #"10/01/2023" = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Market", "Product", "Metrics"}, "Date", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Value", Currency.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Metrics]), "Metrics", "Value")
in
    #"Pivoted Column"

    How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.

lbendlin's avatar
lbendlin
Super User
1 year ago

The key for a successful pivot is that each row must be unique.  That is often not a given, especially after a prior unpivot.

 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hello Ibend,

     

    You have helped me before! Thank you for responding. Please find the Excel file attached. Hope this helps.

    MarketProductMetrics09/03/202309/10/202309/17/202309/24/202310/01/2023  
    MULOC + eComm Candy Total Unit Volume1,366,2481,319,2071,314,2491,307,7631,315,539  
    MULOC + eComm Soda Total Unit Volume397,863429,617455,535503,015532,100  
    MULOC + eComm Cookies Total Unit Volume531,076532,085546,823579,667571,243  
    MULOC + eComm Candy Base Unit Volume1,276,4001,255,1171,250,3921,249,5881,258,706  
    MULOC + eComm Soda Base Unit Volume348,623361,058389,665417,842419,842  
    MULOC + eComm Cookies Base Unit Volume425,973420,605439,832456,040441,642  
    MULOC + eComm Candy Incremental Unit Volume89,84864,09063,85758,17556,833  
    MULOC + eComm Soda Incremental Unit Volume49,24068,55965,87085,173112,258  
    MULOC + eComm Cookies Incremental Unit Volume105,103111,480106,991123,627129,601  
    MULOC + eComm Candy Total Unit Revenue$7,194,238.82$6,997,334.89$6,973,808.94$6,975,411.22$7,037,991.78  
    MULOC + eComm Soda Total Unit Revenue$4,309,640.34$4,550,588.59$4,917,761.35$5,416,862.45$5,650,108.89  
    MULOC + eComm Cookies Total Unit Revenue$6,297,971.17$6,323,293.16$6,572,204.50$7,050,537.92$6,895,827.76  
    MULOC + eComm Candy Base Unit Revenue$6,822,721.93$6,731,002.05$6,719,095.52$6,720,501.69$6,828,002.18  
    MULOC + eComm Soda Base Unit Revenue$3,804,347.57$3,889,787.79$4,210,091.58$4,485,050.84$4,462,048.95  
    MULOC + eComm Cookies Base Unit Revenue$5,124,706.26$5,064,062.32$5,350,787.40$5,574,012.12$5,469,380.03  
    MULOC + eComm Candy Incremental Unit Revenue$371,516.89$266,332.84$254,713.42$254,909.52$209,989.59  
    MULOC + eComm Soda Incremental Unit Revenue$505,292.77$660,800.80$707,669.78$931,811.61$1,188,059.94  
    MULOC + eComm Cookies Incremental Unit Revenue$1,173,264.91$1,259,230.84$1,221,417.10$1,476,525.80$1,426,447.73  
    MULOC + eComm Candy Regular Price$5.35$5.36$5.37$5.38$5.42  
    MULOC + eComm Soda Regular Price$10.91$10.77$10.80$10.73$10.63  
    MULOC + eComm Cookies Regular Price$12.03$12.04$12.17$12.22$12.38  
              
    MarketProductDateTotal Unit VolumeBase Unit VolumeIncremental Unit VolumeTotal Unit RevenueBase Unit RevenueIncremental Unit RevenueRegular Price
    MULOC + eComm Candy 09/03/20231,366,2481,276,40089,848$7,194,238.82$6,822,721.93$371,516.89$5.35
    MULOC + eComm Candy 09/10/20231,319,2071,255,11764,090$6,997,334.89$6,731,002.05$266,332.84$5.36
    MULOC + eComm Candy 09/17/20231,314,2491,250,39263,857$6,973,808.94$6,719,095.52$254,713.42$5.37
    MULOC + eComm Candy 09/24/20231,307,7631,249,58858,175$6,975,411.22$6,720,501.69$254,909.52$5.38
    MULOC + eComm Candy 10/01/20231,315,5391,258,70656,833$7,037,991.78$6,828,002.18$209,989.59$5.42
    MULOC + eComm Soda 09/03/2023397,863348,62349,240$4,309,640.34$3,804,347.57$505,292.77$10.91
    MULOC + eComm Soda 09/10/2023429,617361,05868,559$4,550,588.59$3,889,787.79$660,800.80$10.77
    MULOC + eComm Soda 09/17/2023455,535389,66565,870$4,917,761.35$4,210,091.58$707,669.78$10.80
    MULOC + eComm Soda 09/24/2023503,015417,84285,173$5,416,862.45$4,485,050.84$931,811.61$10.73
    MULOC + eComm Soda 10/01/2023532,100419,842112,258$5,650,108.89$4,462,048.95$1,188,059.94$10.63
    MULOC + eComm Cookies 09/03/2023531,076425,973105,103$6,297,971.17$5,124,706.26$1,173,264.91$12.03
    MULOC + eComm Cookies 09/10/2023532,085420,605111,480$6,323,293.16$5,064,062.32$1,259,230.84$12.04
    MULOC + eComm Cookies 09/17/2023546,823439,832106,991$6,572,204.50$5,350,787.40$1,221,417.10$12.17
    MULOC + eComm Cookies 09/24/2023579,667456,040123,627$7,050,537.92$5,574,012.12$1,476,525.80$12.22
    MULOC + eComm Cookies 10/01/2023571,243441,642129,601$6,895,827.76$5,469,380.03$1,426,447.73$12.38
    Would like to Edit in Power Query to result in this         
    Cropped all the dates for easier view on desired results         
    • dufoq3's avatar
      dufoq3
      Community Champion
      1 year ago

      Hi Anonymous, another solution without unpivoting/pivoting:

       

      Output

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lVXJaiRHEP2VRug2QRJLrlfrNDDGZhZfBh0aTWHESN2gGRn8936RmYXbdJcbnSoiqyqW9+JFfv168+uXD7/d7d7tlrvj8/Puhm7u9odvf7vx+fhz/7T7cnj8ufvj+PT6vOBMyHImjXXY0ki5TDvivA2bC5Vs8zxRsnZzTxdyfTp+22+lslao9iBRG2XxNDF5sAQrsRFLt0xJmC8nuDsevz8uP7ZyJBPikmcUrj1ezFTV86aCvLl0S9CcbeRYAftl/2M5w0tLpojyuo3yRcq0mazpsGOjVOs8r1Q4/z9eFzJZrJR72ZbRVPJoVr0BbyoKwIzardata3BdSBE1USuDEabMPbAhnPXAKRNHbzRGobyZYkXr/eHhZXleDuesVK/Q68+RuHnEbFRTJ6KSlM4SSLINQlaUtjMAb+2l5kop+dDmRLX4SQVFvUcRdTKuArWdRRixeMQSirUPAWdqTdxSA2F9GHy+Wa7gdTK/H5e/lsOrp7gtJA3Csxqqk3Dr0QuZxVDb9AvA4xpaXP1EUSSojv/ZilcUykarFzR6kj9C7Kg+crA4/ITBxjCHDiv8Jr4MJHTZ3npuUJc1xOlnfC+or27siMsSPikB+wgttyKhSwu+AVptFiQPPxVQyTEkni17iVZCm5DVBva1hLIhuwsK/0/+qkpFJTQbfvG1whq6QtyH5rilkGa+AvkklpDb+n/t38sVCi6md3bBQiyhK8R9CKhUtDMZUGGkl9B3AvyIGQcEoU7GYsbui5iQ9IatcFIBplyjL62gefjsygXHfTHANwDuFXXNwU8F7wUNz/cxN7LKga+t2DOxnQKBJZ0kz8FX3FNmOpvUhALFQl+B3WvcJh+KAW61+cC+bZ2cQgCpa9NQxgRmBikAeMwbbsOcmwsMXsNsVMgv+w64FZIK7lNzeb5505wUIL61SHMMbQbWhCVnK8vwFTtIShAefsTFlDTNIuErLipMUbnGwcflz9en/cvu95fHh9H8qu1geT7LfNbx3LoMVmjPQgqvffDEVFY4/cSmkbdq/Re089Aa2FYjTmNsDhhjK8JA6ff3/wA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Market = _t, Product = _t, Metrics = _t, #"09/03/2023" = _t, #"09/10/2023" = _t, #"09/17/2023" = _t, #"09/24/2023" = _t, #"10/01/2023" = _t]),
    GroupedRows = Table.Group(Source, {"Market", "Product"}, {{"All", each Table.RenameColumns(Table.PromoteHeaders(Table.FillDown(Table.FromColumns({{"Market", _{0}[Market]}} & {{"Product", _{0}[Product]}} & Table.ToRows(Table.DemoteHeaders(Table.RemoveColumns(_,{"Market", "Product"})))), {"Column1", "Column2"})), {{"Metrics", "Date"}}), type table}}),
    Combined = Table.Combine(GroupedRows[All]),
    ChangedType = Table.TransformColumnTypes(Combined,{{"Market", type text}, {"Product", type text}, {"Date", type date}, {"Total Unit Volume", type number}, {"Base Unit Volume", type number}, {"Incremental Unit Volume", type number}, {"Total Unit Revenue", Currency.Type}, {"Base Unit Revenue", Currency.Type}, {"Incremental Unit Revenue", Currency.Type}, {"Regular Price", Currency.Type}}, "en-US")
in
    ChangedType
      • lbendlin's avatar
        lbendlin
        Super User
        1 year ago
        solution without unpivoting/pivoting

         

        ... Table.FromColumns ... Table.ToRows ...

        🙂

    • lbendlin's avatar
      lbendlin
      Super User
      1 year ago
      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lVXJaiRHEP2VRug2QRJLrlfrNDDGZhZfBh0aTWHESN2gGRn8936RmYXbdJcbnSoiqyqW9+JFfv168+uXD7/d7d7tlrvj8/Puhm7u9odvf7vx+fhz/7T7cnj8ufvj+PT6vOBMyHImjXXY0ki5TDvivA2bC5Vs8zxRsnZzTxdyfTp+22+lslao9iBRG2XxNDF5sAQrsRFLt0xJmC8nuDsevz8uP7ZyJBPikmcUrj1ezFTV86aCvLl0S9CcbeRYAftl/2M5w0tLpojyuo3yRcq0mazpsGOjVOs8r1Q4/z9eFzJZrJR72ZbRVPJoVr0BbyoKwIzardata3BdSBE1USuDEabMPbAhnPXAKRNHbzRGobyZYkXr/eHhZXleDuesVK/Q68+RuHnEbFRTJ6KSlM4SSLINQlaUtjMAb+2l5kop+dDmRLX4SQVFvUcRdTKuArWdRRixeMQSirUPAWdqTdxSA2F9GHy+Wa7gdTK/H5e/lsOrp7gtJA3Csxqqk3Dr0QuZxVDb9AvA4xpaXP1EUSSojv/ZilcUykarFzR6kj9C7Kg+crA4/ITBxjCHDiv8Jr4MJHTZ3npuUJc1xOlnfC+or27siMsSPikB+wgttyKhSwu+AVptFiQPPxVQyTEkni17iVZCm5DVBva1hLIhuwsK/0/+qkpFJTQbfvG1whq6QtyH5rilkGa+AvkklpDb+n/t38sVCi6md3bBQiyhK8R9CKhUtDMZUGGkl9B3AvyIGQcEoU7GYsbui5iQ9IatcFIBplyjL62gefjsygXHfTHANwDuFXXNwU8F7wUNz/cxN7LKga+t2DOxnQKBJZ0kz8FX3FNmOpvUhALFQl+B3WvcJh+KAW61+cC+bZ2cQgCpa9NQxgRmBikAeMwbbsOcmwsMXsNsVMgv+w64FZIK7lNzeb5505wUIL61SHMMbQbWhCVnK8vwFTtIShAefsTFlDTNIuErLipMUbnGwcflz9en/cvu95fHh9H8qu1geT7LfNbx3LoMVmjPQgqvffDEVFY4/cSmkbdq/Re089Aa2FYjTmNsDhhjK8JA6ff3/wA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Market = _t, Product = _t, Metrics = _t, #"09/03/2023" = _t, #"09/10/2023" = _t, #"09/17/2023" = _t, #"09/24/2023" = _t, #"10/01/2023" = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Market", "Product", "Metrics"}, "Date", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Value", Currency.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Metrics]), "Metrics", "Value")
in
    #"Pivoted Column"

      How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.