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")
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")
