cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GloriaG
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?

1 REPLY 1
v-kalyj-msft
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

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.