Forum Discussion
Need help with pivoting multiple columns
I have a data source with 44 sets of column data (Name and Value) that I want to Pivot to have all the names as columns and the correct values in each column.
I know this sounds confusing so here is a small example showing 3 pairs (I have 44):
So lets say my data looks like this (na dimaging my x_Name and x_Value coumns go up to 44!):
| Ticket | 0_Name | 0_Value | 1_Name | 1_Value | 2_Name | 2_Value |
| 123 | Status | Completed | Resolution | Fixed | Asset | abc |
| 456 | Zip | 33487 | Asset | def | Resoltuion | Fixed |
| 789 | Status | Completed | null | null | Resoltution | Fixed |
The same name values could appear in different "x_Value" columns other rows.
I unpivoted all the selected columns to provide the follwowing veiw:
Table.Unpivot(Source, {"2_Value", "2_Name", "1_Value", "1_Name", "0_Value", "0_Name"}, "Attribute", "Value")
| Ticket | Attribute | Values |
| 123 | 0_Name | Status |
| 123 | 0_Value | Completed |
| 456 | 0_Name | Zip |
| 456 | 0_Value | 33487 |
| 789 | 0_Name | Status |
| 789 | 0_Value | Completed |
| 123 | 1_Name | Resolution |
| 123 | 1_Value | Fixed |
| 456 | 1_Name | Asset |
| 456 | 1_Value | def |
| 789 | 1_Name | null |
| 789 | 1_Value | null |
| 123 | 2_Name | Asset |
| 123 | 2_Value | abc |
| 456 | 2_Name | Resoltuion |
| 456 | 2_Value | Fixed |
| 789 | 2_Name | Resoltution |
| 789 | 2_Value | Fixed |
I then removed the 0_, 1_ and 2_ prefix so that only "Value" and "Name" shows.
| Ticket | Attribute | Values |
| 123 | Name | Status |
| 123 | Value | Completed |
| 456 | Name | Zip |
| 456 | Value | 33487 |
| 789 | Name | Status |
| 789 | Value | Completed |
| 123 | Name | Resolution |
| 123 | Value | Fixed |
| 456 | Name | Asset |
| 456 | Value | def |
| 789 | Name | null |
| 789 | Value | null |
| 123 | Name | Asset |
| 123 | Value | abc |
| 456 | Name | Resoltuion |
| 456 | Value | Fixed |
| 789 | Name | Resoltution |
| 789 | Value | Fixed |
This is where my problem comes in. I now want to Pivot this info so that I end up with the view below:
Basically having all the Names as columns and the values in the correct column.
| Ticket | Status | Zip | Resolution | Asset |
| 123 | Completed | Fixed | abc | |
| 456 | 33487 | Fixed | def | |
| 789 | Completed | Fixed |
I receive the following error: "Expression.Error: There were too many elements in the enumeration to complete the operation"
I am guessing it has something to do with the same names and values showing up in different columns.
Am I using the correct approach? What am I doing wrong?
I looked at manually adding custom columns and using if statements to find the value for each column but replicating that over 44 sets of data will be impossible.
Any help will be appreciated.
PS. this is example data and not the actual data I am working with.
Hi AVH_Tech ,
Try this m code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCslMzk4tUdJRMoj3S8xNBTPCEnNKQSxDmJAhXMgIJmQEFYrViVYyNDIGigSXJJaUFgMZzvm5BTmpJakpQHZQanF+TmlJZn4ekOOWWQEWdCwuBluZmJQM1m9iagbkRWUWAEljYxMLcyQ1Kalp2I0BaTS3sMRlcV5pTg6CwqY/FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t, #"(blank).5" = _t, #"(blank).6" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}, {"(blank).4", type text}, {"(blank).5", type text}, {"(blank).6", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Ticket", Int64.Type}, {"0_Name", type text}, {"0_Value", type text}, {"1_Name", type text}, {"1_Value", type text}, {"2_Name", type text}, {"2_Value", type text}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Ticket"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", Int64.Type}, {"Attribute.2", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type2", {"Ticket", "Attribute.1"}, {{"Rows", each _, type table [Ticket=number, Attribute.1=number, Attribute.2=text, Value=text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.PromoteHeaders(Table.Transpose(Table.SelectColumns([Rows], {"Attribute.2", "Value"})))),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Attribute.1", "Rows"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Name", "Value"}, {"Name", "Value"}),
#"Pivoted Column" = Table.Pivot(#"Expanded Custom", List.Distinct(#"Expanded Custom"[Name]), "Name", "Value")
in
#"Pivoted Column"
3 Replies
camargos88Community Champion
Hi AVH_Tech ,
Try this m code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCslMzk4tUdJRMoj3S8xNBTPCEnNKQSxDmJAhXMgIJmQEFYrViVYyNDIGigSXJJaUFgMZzvm5BTmpJakpQHZQanF+TmlJZn4ekOOWWQEWdCwuBluZmJQM1m9iagbkRWUWAEljYxMLcyQ1Kalp2I0BaTS3sMRlcV5pTg6CwqY/FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t, #"(blank).5" = _t, #"(blank).6" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}, {"(blank).4", type text}, {"(blank).5", type text}, {"(blank).6", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Ticket", Int64.Type}, {"0_Name", type text}, {"0_Value", type text}, {"1_Name", type text}, {"1_Value", type text}, {"2_Name", type text}, {"2_Value", type text}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Ticket"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", Int64.Type}, {"Attribute.2", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type2", {"Ticket", "Attribute.1"}, {{"Rows", each _, type table [Ticket=number, Attribute.1=number, Attribute.2=text, Value=text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.PromoteHeaders(Table.Transpose(Table.SelectColumns([Rows], {"Attribute.2", "Value"})))),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Attribute.1", "Rows"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Name", "Value"}, {"Name", "Value"}),
#"Pivoted Column" = Table.Pivot(#"Expanded Custom", List.Distinct(#"Expanded Custom"[Name]), "Name", "Value")
in
#"Pivoted Column"Thanks, I had to make some tweaks but it is doing what I needed it to. My biggest struggle is now performance.
Thank you for the assistance.
- mahoneypat
Microsoft Employee
This looked like a fun one to do in query, so I did it too. Probably very similar to the previous post, but FYI in case it is useful.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRCi5JLCktBjKc83MLclJLUlOA7KDU4vyc0pLM/Dwgxy2zAizoWFycWgKkE5OSlWJ1opVMTM2AvKjMAiBpbGxiYY6kJiU1DbsxII3mFpa4LM4rzclBUNj0xwIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Ticket = _t, #"0_Name" = _t, #"0_Value" = _t, #"1_Name" = _t, #"1_Value" = _t, #"2_Name" = _t, #"2_Value" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Ticket", Int64.Type}, {"0_Name", type text}, {"0_Value", type text}, {"1_Name", type text}, {"1_Value", type text}, {"2_Name", type text}, {"2_Value", type text}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Ticket"}, "Attribute", "Value"),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Unpivoted Other Columns", {{"Ticket", type text}}, "en-US"),{"Ticket", "Attribute"},Combiner.CombineTextByDelimiter("&", QuoteStyle.None),"Merged"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Merged Columns", "Merged", Splitter.SplitTextByEachDelimiter({"_"}, QuoteStyle.Csv, false), {"Merged.1", "Merged.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Merged.1", type text}, {"Merged.2", type text}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Merged.2]), "Merged.2", "Value"),
#"Extracted Text Before Delimiter" = Table.TransformColumns(#"Pivoted Column", {{"Merged.1", each Text.BeforeDelimiter(_, "&"), type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Extracted Text Before Delimiter",{{"Merged.1", "Ticket"}}),
#"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([Name] <> "null")),
#"Pivoted Column1" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[Name]), "Name", "Value")
in
#"Pivoted Column1"If this works for you, please mark it as solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
