Solved: Power Query Pivot stuck

Gopal2704 · ‎07-11-2023

Hi Everyone !
I have been trying to pivot the below data by wrt to Account_L2 and Values.
There are around 5 million such rows
But it is taking a hell lot of time and finally crashing my power BI.

The source is Dataflow.
Please help

Month

Year

ZONE 1

ZONE 2

CLUSTER 1

CLUSTER 2

CLUSTER 3

CLUSTER 4

COUNTRY

FDT Cluster

NBM Countries

Franchise

Franchise#2

Division#1

Division#2

PRODUCT FAMILY

PRODUCT

MAIN PRODUCT

EXT - SEGMENT

EXT - ASSET TYPE

EXT - PRODUCT FAMILY NEW

INT - SEGMENT

INT - ASSET TYPE

INT - PRODUCT FAMILY NEW

MARKET#1

MARKET#2

Other information #1

Other information #2

Account_L2

INT - FDT SEGMENT

Rates

ATT_DIV

ATT_EnActivite

MC Code

Account Code

Product Code

Extracted Rates

Attribute

Value

Dec

2021

GENERAL MEDICINES

GROSS MARGIN

GENERAL MEDICINES

713000

Dec

2021

GENERAL MEDICINES

GROSS MARGIN

GENERAL MEDICINES

-713000

Dec

2021

GENERAL MEDICINES

GROSS MARGIN

GENERAL MEDICINES

0

Dec

2021

GENERAL MEDICINES

GROSS MARGIN

GENERAL MEDICINES

0

BA_Pete · ‎07-11-2023

Hi @Gopal2704 ,

Pivot/Unpivot are 'whole-table' operations i.e. Power Query must load the ENTIRE table into memory in order to perform the transformation.

For such large tables, you have a few options:

-1- Don't perform the pivot at all and see if it's possible to use the data in its native state e.g. by using matrix visuals etc.

-2- Perform the pivot operation at source e.g. write a view on the server that pivots the data before being brought in to Dataflow/Power Query.

-3- Use the original source (SQL Server etc.) as your query source (instead of a Dataflow) and utilise query folding to perform the operation at source.

-4- Break up/filter your table into much smaller pieces before performing the pivot/unpivot.

-5- Invest in RAM to go into your gateways/desktop/laptop, cranking them all up to 48GB-64GB+.

You may also find performance improvements by doing the pivot in DAX, although this will likely also be highly dependent on machine RAM availability as well.

Pete

Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!

View solution in original post

BA_Pete · ‎07-11-2023

Hi @Gopal2704 ,

Pivot/Unpivot are 'whole-table' operations i.e. Power Query must load the ENTIRE table into memory in order to perform the transformation.

For such large tables, you have a few options:

-1- Don't perform the pivot at all and see if it's possible to use the data in its native state e.g. by using matrix visuals etc.

-2- Perform the pivot operation at source e.g. write a view on the server that pivots the data before being brought in to Dataflow/Power Query.

-3- Use the original source (SQL Server etc.) as your query source (instead of a Dataflow) and utilise query folding to perform the operation at source.

-4- Break up/filter your table into much smaller pieces before performing the pivot/unpivot.

-5- Invest in RAM to go into your gateways/desktop/laptop, cranking them all up to 48GB-64GB+.

You may also find performance improvements by doing the pivot in DAX, although this will likely also be highly dependent on machine RAM availability as well.

Pete

Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!

Gopal2704 · ‎07-17-2023

Thank you for providing a detailed solution Pete !!

Power Query Pivot stuck

Helpful resources

Power BI Monthly Update - April 2026

New to Fabric Survey

Power BI DataViz World Championships - June 2026

FabCon &SQLCon Highlights

Get Fabric certified for FREE!

Power Query Pivot stuck

Helpful resources

Power BI Monthly Update - April 2026

New to Fabric Survey

Power BI DataViz World Championships - June 2026

FabCon &SQLCon Highlights