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 ChampionSorry for not being clear, I'm updating 2 files in PBI by folder method. here an example:
FILE 1
Date Dimension 1 Dimension 2 Measure 1 Measure 2 05/01/2020 QX111R IR869Q 9558 8721 06/01/2020 VA710J BW316Q 32 1485 07/01/2020 TG490E JZ523B 635 7768 08/01/2020 JT709I BJ144F 2103 2841 09/01/2020 OK911R AF937G 5734 6670 10/01/2020 SH554H MJ457X 5893 7427 11/01/2020 PC633W TZ711V 5047 5213 FILE 2
Date Dimension 1 Dimension 2 Measure 1 Measure 2 05/01/2020 QX111R IR869Q 9558 8721 06/01/2020 VA710J BW316Q 32 1485 07/01/2020 TG490E JZ523B 635 7768 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 I would like to find a method in the query editor (if possibile) to filter from the FILE 2 only the consecutive date from file 1 and excluding from the file 2 the date that are present in the file 1.
v-yuta-msftCommunity Support
Anonymous ,
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
I'm sorry, but I don't know what this means.
"I would like to find a method in the query editor (if possibile) to filter from the FILE 2 only the consecutive date from file 1 and excluding from the file 2 the date that are present in the file 1."Can you show us expected output?