Forum Discussion
Import files from folder and filter between consecutive dates in query editor.
Hi Power BI User;
I'm facing with a filter challenge. I'm updating data from a folder, in this folder there are two files (File 1 and File 2). The two files have a date column, I would like to filter from the second file (file 2) only the consecutive date from file 1. It is possibile implementing this filter in the query editor?
Thank you in advance.
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.
9 Replies
- Greg_Deckler
Community Champion
Sorry 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
- edhans
Probalby, but you are goingt o have to give us some sample data. See links below. Don't give screenshots of data, but screenshots of expected results are ok.
How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum
