Power Query Formula to compare data in different tables and create a custom column.

Maggie Barr

Board Regular
Joined
Jan 28, 2014
Messages
188
Hello and thank you in advance if you can help in any way,
I am on a PC using Excel 2019 and starting to "try" to learn power query as my data set has gotten too large for excel. I have my files all loaded, the relational database configured, and I have been able to put in some custom columns into a new master query that brings data together. I now am trying to figure out how and where within the program to run a test to see if an observation date for a species with a specific code is outside a specified date range for that same species with the same code on another sheet. I have a formula that I got from a generous person here at Mr.Excel, but I now need to learn how to do it in power query. I have spent hours watching tutorials, and I have five books in front of me, the Excel 2019 Bible, M is for (Data) Monkey, Learn to Write DAX, etc., but I have not figured out how to do this yet. I have modified this formula multiple ways in excel to test for within dates ranges, outside date ranges, and for different groups of species, but again, my problem is that my data set has outgrown excel, so I need to learn the new process.
Any level of advice as to where a function like this can be performed to create a column would be appreciated. In the mean time I will keep searching.
Details below.
Thank you,
Maggie Barr

In the formula below A4 is in a sheet titled FULL_DATA_WORKING_QUERY and a column titled Species;_Code and B4 is in that same sheet column titled Observation_Date. The references to the other sheet in the formula, 2nd_SpCodeDate_4Flags, is still the same sheet reference and the columns are the same, though the name for the columns referenced in that sheet are C is NEW_Start_Date and D is NEW_End_Date.

=IF(ISNUMBER(MATCH($A4,2nd_SpCodeDate_4Flags!$A:$A,0)),IF(COUNTIFS(2nd_SpCodeDate_4Flags!$A:$A,$A4,2nd_SpCodeDate_4Flags!$C:$C,"<="&DATE(YEAR(2nd_SpCodeDate_4Flags!$C$2),MONTH($B4),DAY($B4)),2nd_SpCodeDate_4Flags!$D:$D,">="&DATE(YEAR(2nd_SpCodeDate_4Flags!$C$2),MONTH($B4),DAY($B4)))=0,IF(DATE(YEAR(2nd_SpCodeDate_4Flags!$C$2),MONTH($B4),DAY($B4))>INDEX(2nd_SpCodeDate_4Flags!$D:$D,MATCH($A4,2nd_SpCodeDate_4Flags!$A:$A,0)),"TooLate","TooEarly"),"DateOkay"),"Sp;Code N/A")
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Forum statistics

Threads
1,213,520
Messages
6,114,101
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top