Forum Discussion
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!
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
4 Replies
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.
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
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_TYPE AD_TYPE AD_WEEK AD_WEEK AD_YEAR AD_YEAR PROGRAM_TYPE PROGRAM_TYPE PROMO_START_DT PROMO_START_DT PROMO_END_DT PROMO_END_DT Vendor Vendor SP_RETAIL_PRICE SP_RETAIL_PRICE U_PUR_ORG_NUM U_PUR_ORG_NUM U_PUR_ORG_NM U_PUR_ORG_NM U_DIST_CHANNEL_NUM U_DIST_CHANNEL_NUM U_DIST_CHANNEL_NM U_DIST_CHANNEL_NM B00_QTY QTY B01_QTY TRANSMIT_DT B01_TRANSMIT_DT DELIVERY_DT B01_DELIVERY_DT PICK_DT B01_PICK_DT PREQ_CREATE_DT B01_PREQ_CREATE_DT SOURCE B01_SOURCE LDGGROUP_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
