Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 f...
Need Help
  • edhans's avatar
    edhans
    6 years ago

    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's avatar
Anonymous
Not applicable
6 years ago

Sorry for not being clear, I'm updating 2 files in PBI by folder method. here an example:

 

FILE 1

DateDimension 1Dimension 2Measure 1Measure 2
05/01/2020QX111RIR869Q95588721
06/01/2020VA710JBW316Q321485
07/01/2020TG490EJZ523B6357768
08/01/2020JT709IBJ144F21032841
09/01/2020OK911RAF937G57346670
10/01/2020SH554HMJ457X58937427
11/01/2020PC633WTZ711V50475213

 

FILE 2

 

DateDimension 1Dimension 2Measure 1Measure 2
05/01/2020QX111RIR869Q95588721
06/01/2020VA710JBW316Q321485
07/01/2020TG490EJZ523B6357768
12/01/2020JT709IBJ144F21032841
13/01/2020OK911RAF937G57346670
13/01/2020SH554HMJ457X58937427
13/01/2020PC633WTZ711V50475213

 

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-msft's avatar
v-yuta-msft
Icon for Community Support rankCommunity Support
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

    You are right, the only consecutive dates that I would like to filter from the files 2 are the following:

     

    12/01/2020JT709IBJ144F21032841
    13/01/2020OK911RAF937G57346670
    13/01/2020SH554HMJ457X58937427
    13/01/2020PC633WTZ711V50475213

     

    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's avatar
      edhans
      Icon for Community Champion rankCommunity Champion
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

        This solution is very smart! Thank you very much!