Forum Discussion
How to compare a record in a table with another table on a number of conditions ?
Hello,
I'm strugling with the following in Power Query:
I have 2 tables: Salesorderlines and Prognosis.
The Salesorderline table contains the columns: item, quantity and delivery date.
The Prognosis table contains the columns: item, quantity and delivery date.
For every row in the Prognosis table, I want to check if it already exists in the (all the records of the) Salesorderline table, based on the following conditions:
Salesorderline.item = Prognosis.item
Salesorderline.delivery date > Prognosis date and Salesorderline.deliverydate-5 days> Prognosis date ( so f.e. deliverydate is 15 january, then 10,11,12,13,14 of january are within the selection)
If the conditions are fulfilled in a new column in the Prognosis table it should give "Already ordered" else "Not ordered yet"
How do I do this Power Query ??
Thank you in advance!!!
6 Replies
- AlB
Community Champion
Hi Brutus97
Would you show the two tables (if necessary with dummy data but preserving their structure) and also the expected result? Please share (at least the initial tables) in text-tabular format so that the contents can be copied. Or share the pbix if possible
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Did you see the Excel screenshot? Is it clear for you?
Hello A|B,
I'm trying to make example tables but I get HTML errors when I want to post the reply..
It is like the example below in Excel..
- AlB
Just copying the tables in Excel and doing a simple paste here should work. Otherwise place the tables in an Excel file (don't forget the expected result) and share the excel file.
You have to share the URL to the file hosted elsewhere: Dropbox, Onedrive... or just upload the file to a site like tinyupload.com (no sign-up required).
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
v-yingjlCommunity Support
Hi Brutus97 ,
There seems to be a lack of correspondence between rows. For example, about Item A12, why 2021-12-12 Prognosis date is corresponding to 2021-1-15 instead of 2021-1-22 delivery date, 2021-1-25 Prognosis date has no correspondence delivery date instead of 2021-1-22 or 2021-1-15 Prognosis date etc. , how to define this correspondence?
Could you please consider sharing more details about this issue for further discussion?
Best Regards,
Community Support Team _ Yingjie Li
