Forum Discussion
mschroetel
8 years agoNew 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 a...
- 8 years ago
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
bigdee008
7 years agoFrequent 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_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 |