Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
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 |
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |