How to compare a record in a table with another table on a number of conditions ?

[Brutus97]

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!!!

[v-yingjl]

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

[AlB]

@Brutus97 

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 

[Brutus97]

It is like the example below in Excel..

[Brutus97]

Hello A|B,

I'm trying to make example tables but I get HTML errors when I want to post the reply.. 

[AlB]

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 

[Brutus97]

Did you see the Excel screenshot? Is it clear for you?