Import files from folder and filter between consecutive dates in query editor.

[Anonymous]

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.

[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

[Greg_Deckler]

Not sure I understand this, sample data would help tremendously. @ImkeF and @edhans may be able to pull off some magic.

[Anonymous]

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.

[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?

[v-yuta-msft]

@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

[Anonymous]

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 · Accepted solution]

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.

[Anonymous]

This solution is very smart! Thank you very much!

[edhans]

Glad I was able to help @Anonymous