Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

How to remove/exclude data from query

Hi there, 

 

I am getting data from folder. In there are multiple excel files. However in each excel I only need a certain date range example for October's excel file, I only need 1-31 October data. The rest can be ignored or removed. There is a date column in the excel and all the excel are formatted the same way. Appreciate any help. Thanks.

Tips and Tricks
  • Dinesh_Suranga's avatar
    Dinesh_Suranga
    3 years ago

    Anonymous ,

    Hi,

    1 Go to Add Column in top menu.

    2 Click on Custom Column

     

    3 Add following code.
    Please change [source.name] and [DateColumn] as your table.

    if [source.name] = "Excel 1 - All" and Date.MonthName( [DateColumn]) = "May" then "True" else

    if [source.name] = "Excel 2 - All" and Date.MonthName( [DateColumn]) = "June" then "True" else

    if [source.name] = "Excel 3 - All" and Date.MonthName( [DateColumn]) = "July" then "True" else

    if [source.name] = "Excel 4 - All" and Date.MonthName( [DateColumn]) = "August" then "True" else "False"

    Thank you.

  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous ,

    No, you can update the formula as below:

    1. If the year of the data column is equal to 2021

    if [source.name] = "Excel 1 - All.xls"

       and Date.Year([DateColumn]) = 2021

       and Date.MonthName([DateColumn]) = "May"

    then "True"
    else "False"

     

    2. If the year of the data column is equal to the year of current date

    if [source.name] = "Excel 1 - All.xls"

       and Date.Year([DateColumn]) = Date.Year(DateTime.LocalNow())

       and Date.MonthName([DateColumn]) = "May"

    then "True"
    else "False"

    Best Regards

12 Replies

  • Dinesh_Suranga's avatar
    Dinesh_Suranga
    Continued Contributor
    3 years ago

    Anonymous 

    Hi,

    Your Date column data type should be Date before do this.

    1 Select the date column.

    2 Click drop down icon in right side ( highlighted in red)
    3 Click on Date Filters
    4 Click on Between.

    5 Select date range you want.

    Thank you.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      However, multiple excels are already combined in the query. So I can see the excels under source.name. So I am trying to clean this up.

      Excel 1 - All the days in May, remove all the other dates before and after May.
      Excel 2 - All the days in June, remove all the other dates before and after June.
      Excel 3 - All the days in July, remove all the other dates before and after July.
      Excel 4 - All the days in August, remove all the other dates before and after August.

      Should I then import one by one and treat it as different query?

      • Dinesh_Suranga's avatar
        Dinesh_Suranga
        Continued Contributor
        3 years ago

        Anonymous 

        Hi,

        You can add a new column with two conditions.

        If source.name = "Excel 1" - All & Date.MonthName = May then "True"  else "False"
        Like this. Then you can filter True cells from that column. 

        Switch case will be easy than If funtion.

        If you want help to create formula please let me know.

        Thank you.