Forum Discussion
Help Unpivoting data
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.
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.
How about
= Text.Combine(Text.SplitAny("ABCD435","0123456789"))
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.
Thanks Ashish, this worked perfectly without having to split up the data file into 3 speperate queries and then merging the results back together (which also worked perfectly)
Ashish_Mathur how do this code work for 300 columns?
Hi,
Describe the question, share some data and show the expected result.
