Forum Discussion
Help Unpivoting data
I have data which I need to unpivot into 2 columns, one for the week attribute and one for the value. All the data in on the same row. The issue is I have multiple columns for data with the associated value next to it. The 1st 2 rows are what the data currently looks like when I get it. The bottom 5 rows are what I need it to look like. I've tried the Unpivot command unsuccessfully as the unpivot doesn't maintain the relationship between the Date field and the assoicated Value field (i.e. ProcessDate1 and Value1 are related). I'm wondering if either (1) people have any thoughts on how I can do this or (2) tell me where I'm going wrong with the Unpivot command.
| WO | Date_Received | ProcessDate1 | Value1 | ProcessDate2 | Value2 | ProcessDate3 | Value3 | ProcessDate4 | Value4 |
| ABC | 16-May-17 | 18-May-17 | 25 | 23-May-17 | 8 | 26-May-17 | 1 | 05-Jun-17 | 2 |
| WO | WO_Received | Date | Value | ||||||
| 2016-0000337 | 16-May-17 | 18-May-17 | 25 | ||||||
| 2016-0000337 | 16-May-17 | 23-May-17 | 8 | ||||||
| 2016-0000337 | 16-May-17 | 26-May-17 | 1 | ||||||
| 2016-0000337 | 16-May-17 | 05-Jun-17 | 2 |
Hi kimmal,
With help from MarcelBeug, this code will perfectly solve your probelm - there will no longer be a limitation of upto 10 columns
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"WO", type text}, {"Date_Received", type datetime}, {"ProcessDate1", type datetime}, {"Value1", Int64.Type}, {"ProcessDate2", type datetime}, {"Value2", Int64.Type}, {"ProcessDate3", type datetime}, {"Value3", Int64.Type}, {"ProcessDate4", type datetime}, {"Value4", Int64.Type}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"WO", "Date_Received"}, "Attribute", "Value"), #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each Text.Combine(Text.SplitAny([Attribute],"0123456789"))), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Text.End([Attribute],Text.Length([Attribute])-Text.Length([Custom]))), #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Attribute"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Custom]), "Custom", "Value"), #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Custom.1"}) in #"Removed Columns1"Hope this helps.
13 Replies
After unpivot, the trick is to add an Index column (starting with 0) and integer-divide that column by the number of different items (i.c. 2). Then you can adjust the attribute column so - in this case - you have "Date" and "Value" entries.
Now you can pivot back (with advanced option "Don't Summarize"), remove the Index column and adjust data types.
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"WO", type text}, {"Date_Received", type date}, {"ProcessDate1", type date}, {"Value1", Int64.Type}, {"ProcessDate2", type date}, {"Value2", Int64.Type}, {"ProcessDate3", type date}, {"Value3", Int64.Type}, {"ProcessDate4", type date}, {"Value4", Int64.Type}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"WO", "Date_Received"}, "Attribute", "Value"), #"Transformed Attribute" = Table.TransformColumns(#"Unpivoted Other Columns",{{"Attribute", each if Text.Contains(_,"Date") then "Date" else "Value"}}), #"Added Index" = Table.AddIndexColumn(#"Transformed Attribute", "Index", 0, 1), #"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 2), Int64.Type}}), #"Pivoted Column" = Table.Pivot(#"Integer-Divided Column", List.Distinct(#"Integer-Divided Column"[Attribute]), "Attribute", "Value"), #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"}), #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Date", type date}, {"Value", Int64.Type}}) in #"Changed Type1"Hi,
Try this M language solution
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"WO", type text}, {"Date_Received", type datetime}, {"ProcessDate1", type datetime}, {"Value1", Int64.Type}, {"ProcessDate2", type datetime}, {"Value2", Int64.Type}, {"ProcessDate3", type datetime}, {"Value3", Int64.Type}, {"ProcessDate4", type datetime}, {"Value4", Int64.Type}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"WO", "Date_Received"}, "Attribute", "Value"), #"Split Column by Position" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByPositions({0, 1}, true), {"Attribute.1", "Attribute.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Attribute.1", type text}, {"Attribute.2", Int64.Type}}), #"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Attribute.1]), "Attribute.1", "Value") in #"Pivoted Column"The limitation of this method is that it will only work with a dataset which has upto 10 columns of ProcessDate per WO.
Hope this helps.
Thanks, Ashish, this worked perfectly, except for the fact that it's limited to 10 columns, but I think I can fix the source file to exclude 3 of the columns
Hi,
You are welcone. This isn't the best solution die to the 10 column limitation. If someone can tell us how to extract text from an alphanumeric string in Power Query, then you will get the perfect solution. So from an alphanumeric string such as ABCD435, how does one extract abcd? Also, that solution should work for different lengths of the text and numeric portion.
I suggest you start another thread with the question of how to extract the text portion of an alphanumeric string where the length of each portion is unknown. Once we get that solution, we will plug it into my code shared above.
Ashish_Mathur how do this code work for 300 columns?
Hi,
Describe the question, share some data and show the expected result.
By the way, you may also take a look at the GENERATE Function similar to the CROSS APPLY condition in SQL.
