cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
NAOS Member
Member

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
Super User
Super User

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

NAOS Member
Member

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?

Super User
Super User

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?

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)