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!
