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?
Have you tried using List.Buffer?
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.
The files are not big, just about 7 to 8MB each.
Any other suggestions?
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?