cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SClarke501
Frequent Visitor

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

CarCar-Red
CarCar-Blue
CarCar-Green
PlanePlane-Green
PlanePlane-White
PlanePlane-Black
BoatBoat-White
BoatBoat-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-RedPlane-GreenBoat-White
Car-BluePlane-WhiteBoat-Brown
Car-GreenPlane-Blacknull

 

It doesn't even work when I remove all but one type i.e. Car so there would be no null values.

1 ACCEPTED SOLUTION
Vijay_A_Verma
Super User
Super User

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"

View solution in original post

1 REPLY 1
Vijay_A_Verma
Super User
Super User

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"

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors