cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mschroetel
New Member

Unpivot Multiple Coumns

I have a direct connection to a databased containing sales transaction data from a point of sale.   Each transaction can split the revenue between 6 profit centers.  

In the transaction data there are columns for the profit center ID (pr_ctr_1, pr_ct_2, pr_ct_3, pr_ct_4, pr_ct_5, pr_ct_6) and the amount allocated to each profit center (pcsplit_1, pcsplit2,  pcsplit3, pcsplit4, pcsplit5, pcsplit6)

Each transaction is recorded as a row in the data table.  

I need to create a new column called "profit_center" and one called "pc_amount" and unpivot the information from the pr_ctr_1 through pr_ctr_6 columns into the new "profit_center" column and the information from the corresponding pcsplit_1 through pcsplit_6 columns into the new "pc_amount" column.  The information from the pr_ctr_1 and pcsplit1 columns need to continue to correspond.

The rest of the row data should be retained for each newly created row.  

I have included a a screenshot of a spreadsheet showing the current data structure and the desired data structure after the unpivot.

I have tried using the unpivot command but with cannot get the desired result.  I have been able to do it with a SQL query but am hoping to be able to do this all in power BI without having to run a SQL query and data export to a csv.

Any help you can provide would be much appreciated.  Thanks!

pivotsampledata.png

 

 

 

1 ACCEPTED SOLUTION

I've mocked up a sample with the solution here. Please paste into the advanced editor and follow the steps:  

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUUoEYkMDIJEExMYGSrE60UpOUHEjmLgJUDwWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Item = _t, pcsplit_1 = _t, prctr_1 = _t, pcsplit_2 = _t, prctr_2 = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Item"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Attribute.1]), "Attribute.1", "Value"),
    OptionalRemove = Table.RemoveColumns(#"Pivoted Column",{"Attribute.2"})
in
    OptionalRemove

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

4 REPLIES 4
bigdee008
Frequent Visitor

Hi Iker, I modified your suggested querry  above but in did not seem to work in my example below:

Current Header Desired Unpivotted Header
AD_TYPEAD_TYPE
AD_WEEKAD_WEEK
AD_YEARAD_YEAR
PROGRAM_TYPEPROGRAM_TYPE
PROMO_START_DTPROMO_START_DT
PROMO_END_DTPROMO_END_DT
VendorVendor
SP_RETAIL_PRICESP_RETAIL_PRICE
U_PUR_ORG_NUMU_PUR_ORG_NUM
U_PUR_ORG_NMU_PUR_ORG_NM
U_DIST_CHANNEL_NUMU_DIST_CHANNEL_NUM
U_DIST_CHANNEL_NMU_DIST_CHANNEL_NM
B00_QTYQTY
B01_QTYTRANSMIT_DT
B01_TRANSMIT_DTDELIVERY_DT
B01_DELIVERY_DTPICK_DT
B01_PICK_DTPREQ_CREATE_DT
B01_PREQ_CREATE_DTSOURCE
B01_SOURCELDGGROUP_VSR
B01_LDGGROUP_VSR 
B02_QTY 
B02_TRANSMIT_DT 
B02_DELIVERY_DT 
B02_PICK_DT 
B02_PREQ_CREATE_DT 
B03_QTY 
B03_TRANSMIT_DT 
B03_DELIVERY_DT 
B03_PICK_DT 
B03_PREQ_CREATE_DT 
B04_QTY 
B04_TRANSMIT_DT 
B04_DELIVERY_DT 
B04_PICK_DT 
B04_PREQ_CREATE_DT 
B05_QTY 
B05_TRANSMIT_DT 
B05_DELIVERY_DT 
B05_PICK_DT 
B05_PREQ_CREATE_DT 
B06_QTY 
B06_TRANSMIT_DT 
B06_DELIVERY_DT 
B06_PICK_DT 
B06_PREQ_CREATE_DT 
B07_QTY 
B07_TRANSMIT_DT 
B07_DELIVERY_DT 
B07_PICK_DT 
B07_PREQ_CREATE_DT 
B08_QTY 
B08_TRANSMIT_DT 
B08_DELIVERY_DT 
ImkeF
Super User
Super User

1) Check the columns that shall stay and choose "Unpivot other Columns"

2) Check (the new) colum "Attribute" and Split by delimiter "_"

3) Check the first of the resulting columns (that contain "pctr" and "pctsplit") and pivot again. 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

@ImkeF Thanks for the reply.  Can you clarify what you mean on the step 3?  Am I pivoting the first column "attribute.1" on the newly created "attribute.2" column?

Thanks!

I've mocked up a sample with the solution here. Please paste into the advanced editor and follow the steps:  

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUUoEYkMDIJEExMYGSrE60UpOUHEjmLgJUDwWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Item = _t, pcsplit_1 = _t, prctr_1 = _t, pcsplit_2 = _t, prctr_2 = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Item"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Attribute.1]), "Attribute.1", "Value"),
    OptionalRemove = Table.RemoveColumns(#"Pivoted Column",{"Attribute.2"})
in
    OptionalRemove

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors