Unpivoting and Pivoting Rows

[Anonymous]

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!

[Anonymous]

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]

You're welcome. Enjoy 😉

[lbendlin]

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

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

[Anonymous]

Hello Ibend,

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

Market	Product	Metrics	09/03/2023	09/10/2023	09/17/2023	09/24/2023	10/01/2023
MULOC + eComm	Candy	Total Unit Volume	1,366,248	1,319,207	1,314,249	1,307,763	1,315,539
MULOC + eComm	Soda	Total Unit Volume	397,863	429,617	455,535	503,015	532,100
MULOC + eComm	Cookies	Total Unit Volume	531,076	532,085	546,823	579,667	571,243
MULOC + eComm	Candy	Base Unit Volume	1,276,400	1,255,117	1,250,392	1,249,588	1,258,706
MULOC + eComm	Soda	Base Unit Volume	348,623	361,058	389,665	417,842	419,842
MULOC + eComm	Cookies	Base Unit Volume	425,973	420,605	439,832	456,040	441,642
MULOC + eComm	Candy	Incremental Unit Volume	89,848	64,090	63,857	58,175	56,833
MULOC + eComm	Soda	Incremental Unit Volume	49,240	68,559	65,870	85,173	112,258
MULOC + eComm	Cookies	Incremental Unit Volume	105,103	111,480	106,991	123,627	129,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
Market	Product	Date	Total Unit Volume	Base Unit Volume	Incremental Unit Volume	Total Unit Revenue	Base Unit Revenue	Incremental Unit Revenue	Regular Price
MULOC + eComm	Candy	09/03/2023	1,366,248	1,276,400	89,848	$7,194,238.82	$6,822,721.93	$371,516.89	$5.35
MULOC + eComm	Candy	09/10/2023	1,319,207	1,255,117	64,090	$6,997,334.89	$6,731,002.05	$266,332.84	$5.36
MULOC + eComm	Candy	09/17/2023	1,314,249	1,250,392	63,857	$6,973,808.94	$6,719,095.52	$254,713.42	$5.37
MULOC + eComm	Candy	09/24/2023	1,307,763	1,249,588	58,175	$6,975,411.22	$6,720,501.69	$254,909.52	$5.38
MULOC + eComm	Candy	10/01/2023	1,315,539	1,258,706	56,833	$7,037,991.78	$6,828,002.18	$209,989.59	$5.42
MULOC + eComm	Soda	09/03/2023	397,863	348,623	49,240	$4,309,640.34	$3,804,347.57	$505,292.77	$10.91
MULOC + eComm	Soda	09/10/2023	429,617	361,058	68,559	$4,550,588.59	$3,889,787.79	$660,800.80	$10.77
MULOC + eComm	Soda	09/17/2023	455,535	389,665	65,870	$4,917,761.35	$4,210,091.58	$707,669.78	$10.80
MULOC + eComm	Soda	09/24/2023	503,015	417,842	85,173	$5,416,862.45	$4,485,050.84	$931,811.61	$10.73
MULOC + eComm	Soda	10/01/2023	532,100	419,842	112,258	$5,650,108.89	$4,462,048.95	$1,188,059.94	$10.63
MULOC + eComm	Cookies	09/03/2023	531,076	425,973	105,103	$6,297,971.17	$5,124,706.26	$1,173,264.91	$12.03
MULOC + eComm	Cookies	09/10/2023	532,085	420,605	111,480	$6,323,293.16	$5,064,062.32	$1,259,230.84	$12.04
MULOC + eComm	Cookies	09/17/2023	546,823	439,832	106,991	$6,572,204.50	$5,350,787.40	$1,221,417.10	$12.17
MULOC + eComm	Cookies	09/24/2023	579,667	456,040	123,627	$7,050,537.92	$5,574,012.12	$1,476,525.80	$12.22
MULOC + eComm	Cookies	10/01/2023	571,243	441,642	129,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]

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]

solution without unpivoting/pivoting

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

🙂

[dufoq3]

It is transpose in general, not unpivot 😉

[lbendlin · Accepted solution]

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.