Forum Discussion
Solved
Why can't I Pivot this table?
I need to turn these Types into columns, but I'm getting:
Expression.Error: There weren't enough elements in the enumeration to complete the operation.
Type Description
| Car | Car-Red |
| Car | Car-Blue |
| Car | Car-Green |
| Plane | Plane-Green |
| Plane | Plane-White |
| Plane | Plane-Black |
| Boat | Boat-White |
| Boat | Boat-Brown |
I assumed I needed Description to be unique for each Type, so I appended the Type to the front. But the error still persists. I could have sworn I've pivoted data in a similar format before. I am using Don't Aggregate.
Desired Output:
Car Plane Boat
| Car-Red | Plane-Green | Boat-White |
| Car-Blue | Plane-White | Boat-Brown |
| Car-Green | Plane-Black | null |
It doesn't even work when I remove all but one type i.e. Car so there would be no null values.
You require a minimum of 3 columns for pivoting to happen. See below code
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wck4sUtIBkbpBqSlKsTrIIk45paloQu5Fqal5YLGAnMS8VKAomMYpHp6RWZKKRdwpJzE5GyzulJ9YAhQGUUiqkUWdivLLgWbHAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Type = _t, Description = _t]), #"Grouped Rows" = Table.Group(Source, {"Type"}, {{"Temp", each Table.AddIndexColumn(_,"Index",0), type table}}), #"Expanded Temp" = Table.ExpandTableColumn(#"Grouped Rows", "Temp", {"Description", "Index"}, {"Description", "Index"}), #"Pivoted Column" = Table.Pivot(#"Expanded Temp", List.Distinct(#"Expanded Temp"[Type]), "Type", "Description") in #"Pivoted Column"
1 Reply
You require a minimum of 3 columns for pivoting to happen. See below code
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wck4sUtIBkbpBqSlKsTrIIk45paloQu5Fqal5YLGAnMS8VKAomMYpHp6RWZKKRdwpJzE5GyzulJ9YAhQGUUiqkUWdivLLgWbHAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Type = _t, Description = _t]), #"Grouped Rows" = Table.Group(Source, {"Type"}, {{"Temp", each Table.AddIndexColumn(_,"Index",0), type table}}), #"Expanded Temp" = Table.ExpandTableColumn(#"Grouped Rows", "Temp", {"Description", "Index"}, {"Description", "Index"}), #"Pivoted Column" = Table.Pivot(#"Expanded Temp", List.Distinct(#"Expanded Temp"[Type]), "Type", "Description") in #"Pivoted Column"