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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.