Forum Discussion
Cindysc1218
May 27, 2022Copper Contributor
Excel and Power Query: PQ is taking hours to execute
Hi, everyone: I am new to Power Query and not sure why it is taking hours to execute the code below. Because the data is sensitive, I am unable to share the file. However, I can say that the fi...
Riny_van_Eekelen
May 27, 2022Platinum Contributor
Cindysc1218 I'm not suggesting anything, but merely notice that you seem to add a column with identical tables containing filtered records to each of the 300K rows. And I'm just wondering why you don't filter the date column first and then the account number. But as said that's difficult to judge without seeing the data. What stops you from trying on a copy of your file. If it doesn't work, I clearly missed the point.
Or perhaps you could just add a column stating:
= [Ship Date] >= [#"Post Date - 30"] and x[Ship Date] <= [#"Post Date + 30"] and x[#"Acct"] = [#"Account No."]
This should result in TRUE or FALSE. After that, filter TRUE to keep matching rows.
Cindysc1218
May 27, 2022Copper Contributor
I hope I understand you correctly.
For your first suggestion, I filtered dates in regular Excel before using PQ. For your second suggestion, doesn't that mean every row in the first table has to join with every row in the second table in order to find out if the conditional is true or not?
For your first suggestion, I filtered dates in regular Excel before using PQ. For your second suggestion, doesn't that mean every row in the first table has to join with every row in the second table in order to find out if the conditional is true or not?
- Riny_van_EekelenMay 27, 2022Platinum Contributor
Cindysc1218 Can't answer as I'm having a hard time visualizing your data. Sorry.
- Cindysc1218May 27, 2022Copper ContributorNo worries. Thank you for the suggestions anyway.