cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
NAOS Regular Visitor
Regular Visitor

Power Query - More efficient way to compare values between two columns/lis

Hi all,

Simplified start point: A table with property data which has a "property ID" (type text) and a "Quarter" (type text, in the form of 01/19 for First quarter of 2019, for example) column. The data is updated every quarter appending all properties id's to the already existing id's, so there will be a property id for every quarter unless the property has been sold or it is new. 

The problem: To check if a property is New (property id is not in previous quarter but it is in latest quarter) or Sold (property id is in previous quarter but it is not in latest quarter)

How I currentl solve this: I create two lists, one containing the properties' ids for the Previous and the other one for the latest quarter. 
So far so good...
The issue comes when comparing both lists. Using list.difference function with the two lists as parameters, the query takes ages to evaluate, even though each list contains no more than 1000 values each...

Any suggestions on how to make this more efficient?

3 REPLIES 3
Highlighted
Nick_M Established Member
Established Member

Re: Power Query - More efficient way to compare values between two columns/lis

NAOS Regular Visitor
Regular Visitor

Re: Power Query - More efficient way to compare values between two columns/lis

I did and it doesn't seem to make any changes.

Those query steps seem to be loading the data again from the source, which in this case is a folder with 10 or so .xlsx files.


loading.PNG

The files are not big, just about 7 to 8MB each. 

 Any other suggestions?

Nick_M Established Member
Established Member

Re: Power Query - More efficient way to compare values between two columns/lis

Without actaully seeing the file, it's a litte hard to troubleshoot beyond some simple thories. Any chance you post a file or something similiar if the data is sensitive?