Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

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-yanjiang-msft
Community Support
Community Support

Hi @Anonymous ,

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors