Power bi

[Ayushman1997]

#QuestionForGroup
Hi All.

I have a dataset of 2.9M rows which upon unpivoting and merging with 1 table becomes some 246M rows. After that I have to remove duplicates from the table(based on Col1, Col2, Col3…. Col7). After this step if I close and apply the dataset loads for 3/4 hours and then error pops as “buffer memory out of space”.

2.9M --->> 246M no problems. Its loading if I remove "remove duplicates" step. So the problem lies with this step for sure. 

Could anyone please help me out on this?

Possible solutions tried till now:
1. Taken a dataset of 75000 rows and did all steps and checked all working or not. After getting satisfactory results I uploaded the dashboard in power bi service and renamed the file of 2.9M rows same as file name of 75000 rows and refreshed it in power bi service. —- Result: This process didn’t work as it is not taking 2.9M rows.

2. Tried joining tables but this doesn’t work out as well. (Many to one join but corresponding values are unique.) Hence count is not matching

[rajendraongole1]

Hi @Ayushman1997  -Since your dataset is exploding from 2.9M → 246M rows, the issue likely stems from unpivoting and merging. Try the following tips like:

• Avoid unpivoting in Power Query; try reshaping the data in SQL or DAX.
• Filter and remove duplicates before merging.
• Reduce columns before unpivoting to keep the dataset small.
• Perform joins at the database level if possible.

Hope this helps.

[Ayushman1997]

The unpivot and merge doesn't have any problems as I checked without remove duplicates step and all 246M loaded in 25mins. So the problem lies in "remove duplicates" step. 

[rajendraongole1]

Hi @Ayushman1997  -Since your dataset is exploding from 2.9M → 246M rows, the issue likely stems from unpivoting and merging. Try the following tips like:

• Avoid unpivoting in Power Query; try reshaping the data in SQL or DAX.
• Filter and remove duplicates before merging.
• Reduce columns before unpivoting to keep the dataset small.
• Perform joins at the database level if possible.

Hope this helps.