Forum Discussion
Solved
Pivot columns with error
Hi All,
I am trying to pivot the the "column" but it shows error as "Expression.Error: There were too many elements in the enumeration to complete the operation.",
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIE4ZhSAwMj82KFsMSc0lSlWB2IlBMQG6HwjME8I9zajKAKDS3BXGMcKmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Column = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Column", type text}, {"Value", type text}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Column]), "Column", "Value")
in
#"Pivoted Column"
Source:
| ID | Column | Value |
| 1 | A | A's Value |
| 1 | B | 2 |
| 1 | B | 3 |
| 2 | A | A's Value |
| 2 | B | 19 |
| 3 | A | A's Value |
Desired Output:
| ID | A | B | B2 |
| 1 | A's Value | 2 | 3 |
| 2 | A's Value | 19 | |
| 3 | A's Value |
Appreciated if any idea!
ngct1112
You cannot Pivot as you have a duplicate of B for ID 1. You need to do some additional steps before performing Pivot.
Create a blank Query, go to the Advanced Editor, clear the existing code, and paste the codes given below and follow the steps.let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nRR0lFyzs8pzc0DMsISc0pTlWJ1opUMgTxHEI4pNTAwMi9WQJVyAmIjFJ4xmGeEW5sRVKGhJZhrjENlLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}}), #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]), #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"Column", type text}, {"Value", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type1", {"ID", "Column"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"All", each _, type table [ID=nullable number, Column=nullable text, Value=nullable text]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([All],"Index",1)), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}), #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"ID", "Column", "Value", "Index"}, {"ID", "Column", "Value", "Index"}), #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom", each if [Index] = 1 then [Column] else [Column] & Number.ToText([Index] )), #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Column", "Index"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Custom]), "Custom", "Value") in #"Pivoted Column"
2 Replies
ngct1112
You cannot Pivot as you have a duplicate of B for ID 1. You need to do some additional steps before performing Pivot.
Create a blank Query, go to the Advanced Editor, clear the existing code, and paste the codes given below and follow the steps.let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nRR0lFyzs8pzc0DMsISc0pTlWJ1opUMgTxHEI4pNTAwMi9WQJVyAmIjFJ4xmGeEW5sRVKGhJZhrjENlLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}}), #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]), #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"Column", type text}, {"Value", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type1", {"ID", "Column"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"All", each _, type table [ID=nullable number, Column=nullable text, Value=nullable text]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([All],"Index",1)), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}), #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"ID", "Column", "Value", "Index"}, {"ID", "Column", "Value", "Index"}), #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom", each if [Index] = 1 then [Column] else [Column] & Number.ToText([Index] )), #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Column", "Index"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Custom]), "Custom", "Value") in #"Pivoted Column"It looks good on my model. Appreciated
