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...
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

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Sorry how do u do with switch case? 

    For if function can provide step by step? 

    Thanks.

    • Dinesh_Suranga's avatar
      Dinesh_Suranga
      Continued Contributor
      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
        Not applicable
        3 years ago

        Hi Dinesh, 

        IT worked! If I only want to see data for the month and year(May,2022), What should i change to the code? Because in the data there are other years too (2019, 2020, 2021). Currently it is giving me all the May dates in different years. Thanks again.

        Cheers

        Darren