Forum Discussion
"Remove Duplicates" taking forever to refresh for 2 column table
I have a Dimension Table that is produced by referencing the original data table, removing all but 2 columns (the key and a description field). Up to this point, the refresh is quick. However, when I then "Remove Duplicates", the refresh takes forever. Here is the M-code:
let
Source = #"FACT Table",
#"Removed Other Columns" = Table.SelectColumns(Source,{"Commercial Destination Key", "Destination Description"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Other Columns", {"Destination Description"})
in
#"Removed Duplicates"
I get the same result if I select all columns to remove duplicates.
#"Removed Duplicates" = Table.Distinct(#"Removed Other Columns")
Any ideas or advice would be greatly appreciated!
Hi Bernstra ,
The slow refresh occurs because “Remove Duplicates” can’t be pushed to the source, so Power Query must load all rows and deduplicate them locally, which is inefficient for large tables. The optimal solution is to create a dimension with distinct (Commercial Destination Key, Destination Description) values from both fact tables. Achieve this most efficiently at the source using a SQL view, or in Power Query by appending the key and description columns from both fact tables, then applying Table.
Distinct to the key. After building the unique dimension, set up Many-to-One relationships from each fact table. Make sure you deduplicate using both columns and that their data types match to maintain query folding. This approach ensures complete coverage across both fact tables and delivers faster, more reliable refreshes.
Thank you,Tejaswi.
8 Replies
- lbendlin
Super User
Removing duplicates cannot be done in a stream, so you are forcing Power Query to read the ENTIRE data before it can do that.
Consider letting Power BI do the deduplication for you (it automatically aggregates), or do it in the data source.
- danextian
Hi Bernstra
The current query re-applies the transformations from the referenced query in addition to its own, effectively executing the original query twice (please refer to Referencing Power Query queries). This becomes increasingly costly with complex transformations and large datasets. Ideally, the shape of the query being loaded should be as close to the source as possible. If that’s not doable, consider creating the dimension table using DAX instead.
Example:
SUMMARIZE ( 'FACT Table', 'FACT Table'[Commercial Destination Key], 'FACT Table'[Destination Description] )This would definitely be the easiest and quickest way, but I have 2 Fact tables. I'm not sure how I would connect the second Fact table to this Dimension table created in this way.
rohit1991Hi Bernstra ,
This usually happens when “Remove Duplicates” can’t be folded back to the data source and must run in memory—especially slow on large tables. A few quick tips:
-
Try removing duplicates as early as possible in your query steps.
-
Check if your data source supports query folding (“View Native Query”).
-
Make sure both columns have consistent data types.
-
If needed, use Table.Buffer() before removing duplicates, but be careful with large tables.
-
Hi Bernstra ,
Has your issue been resolved?
If the response provided by rohit1991, danextian and lbendlin addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.
If yes, kindly accept the useful reply as a solution and give us Kudos. It would be appreciated.
Thank you for your understanding!Hi Bernstra ,
The slow refresh occurs because “Remove Duplicates” can’t be pushed to the source, so Power Query must load all rows and deduplicate them locally, which is inefficient for large tables. The optimal solution is to create a dimension with distinct (Commercial Destination Key, Destination Description) values from both fact tables. Achieve this most efficiently at the source using a SQL view, or in Power Query by appending the key and description columns from both fact tables, then applying Table.
Distinct to the key. After building the unique dimension, set up Many-to-One relationships from each fact table. Make sure you deduplicate using both columns and that their data types match to maintain query folding. This approach ensures complete coverage across both fact tables and delivers faster, more reliable refreshes.
Thank you,Tejaswi.
Hi Bernstra ,
I wanted to follow up and see if you had a chance to review the information shared. If you have any further questions or need additional assistance, feel free to reach out.
Thank you.
