Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years 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 to Pivot Column (don't aggregate), Pivoting multiple columns, reordering and then pivoting, custom column that I found in another thread. And I am stuck.

The column highlighted, I want to go across as column headers so that I can sum, divide, etc. for measures. But when I pivot, it looks like this.


 metrics go across the top and the values are in the proper association to the Market, Product, and date.

 

Any attempted yields the table like this. In the end, I want something like this which puts all the values in a row under the appropriate column hea

 

I'm sorry if this is something simple. I did try to use the other threads in community and Co-pilot. Your expertises are always appreciated.


Regards!

Need Help
Show and Tell
Tips and Tricks
  • lbendlin's avatar
    lbendlin
    2 years 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.

8 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years 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
      2 years 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
        Icon for Community Champion rankCommunity Champion
        2 years 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
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Ibend / dufoq,

     

    Thank you! It took a while to figure out the "source" part to replace. But It looks good to go with the real file so far! 

     

    I don't know how you guys do these things. But thank god for you guys.

    • dufoq3's avatar
      dufoq3
      Icon for Community Champion rankCommunity Champion
      2 years ago

      You're welcome. Enjoy 😉