Forum Discussion
Import files from folder and filter between consecutive dates in query editor.
You want an Anti-Merge. Starting with table 2, merge the date column with the date column of Table 1, but select Left-Anti-Join.
It will return a table like this. This shows I've already deleted the merge column. There is no need to expand it as I only wanted to limit the rows of table 2, not actually bring in additional info from table 1.
See this Power BI file to see the full M code.
If your dates get wonky, change the Localization steps. I used Bahamas so your DD/MM/YYYY would work on my US region computer.
Community SupportAnonymous ,
It seems the dates which have been marked as green are not consecutive date. Could you please clarify more details about the logic of your requirement?
Regards,
Jimmy Tao
You are right, the only consecutive dates that I would like to filter from the files 2 are the following:
| 12/01/2020 | JT709I | BJ144F | 2103 | 2841 |
| 13/01/2020 | OK911R | AF937G | 5734 | 6670 |
| 13/01/2020 | SH554H | MJ457X | 5893 | 7427 |
| 13/01/2020 | PC633W | TZ711V | 5047 | 5213 |
The final output would be to have in PBI the FILE 2 which contains only the rows that correspond successive dates from FILE 1. It is possibile to improve this in the query editor?
Thank you in advance
- edhans
Community Champion
You want an Anti-Merge. Starting with table 2, merge the date column with the date column of Table 1, but select Left-Anti-Join.
It will return a table like this. This shows I've already deleted the merge column. There is no need to expand it as I only wanted to limit the rows of table 2, not actually bring in additional info from table 1.
See this Power BI file to see the full M code.
If your dates get wonky, change the Localization steps. I used Bahamas so your DD/MM/YYYY would work on my US region computer.
This solution is very smart! Thank you very much!
- edhans
Glad I was able to help Anonymous