Showing results for 
Search instead for 
Did you mean: 
New Member

Very slow refresh in Power Query with Sorted Rows & Removed Duplicates

Hi everone!


I have stuck for a long time during my work in PQ. I'd like to keep the most recent row in my database, so I used:


#"Added Custom1" = Table.AddColumn(#"Renamed Columns", "Merged", each [Est Number]&[ID]&[Place]&[Line]),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Merged", type text}}),
#"Sorted Rows" = Table.Buffer(Table.Sort(#"Changed Type1",{{"Action Date", Order.Descending}})),
#"Removed Duplicates" = Table.Distinct(#"Sorted Rows", {"Merged"})


and performing it is almost impossible. Refresh preview takes hours and also applying this query into PowerBI. I have to add other table transformation after removing duplicates, as adding conditional columns, maybe merging queries, but it can't be done with that time performance.


I also tried "Group by" method for that, but it was even worse, I couldn't even load column names after expanding data, because my laptop wanted to burn himself (Intel core i7).


I have one main file in Excel (260 MB, almost 1M rows) and each month I want to add new file (about 20 MB). Source for this query is Folder path. I tried to divide this main file into 10 seperatly files and try this with CSV format and clear cache. This all didn't help. 


From my position at the job, I can't use SQL server or sth familiar, we receive this files from others, it is already filtered as we need. So far I only work in PBI using Excel/CSV/folder path as a source.


Could anyone write a hint for me?

Community Support
Community Support

Hi @GloriaG ,

For Power Query reference performance, you could see pqian's comment in this thread.


For some tips about how to reduce the memory consumption, please see whether below article is helpful to your scenario:

Performance Tip for Power BI; Enable Load Sucks Memory Up


Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

March 2023 Update3

Power BI March 2023 Update

Find out more about the March 2023 update.

March Events 2023A

March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors