Need help with pivoting multiple columns

[AVH_Tech]

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.

[mahoneypat]

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

[camargos88 · Accepted solution]

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"

[AVH_Tech]

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.