Unpivot Multiple Coumns

[mschroetel]

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!

[bigdee008]

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

[ImkeF]

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. 

[mschroetel]

@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!

[ImkeF · 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