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

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

Dinesh_Suranga's avatar
Dinesh_Suranga
Continued Contributor
3 years ago

Anonymous 

Do you have separate calendar table?

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    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

    No, I don't. Only the date columns with all the dates. 

    Anyway we could edit the script to read the dates mm/yyyy (05/2022)? Maybe it might work? Thanks.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous ,

    You can make a little change on the formula which provided by Dinesh_Suranga  as below(just for example to get the data from 05/2022):

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

       and Date.Year([DateColumn]) = Date.Year(DateTime.LocalNow()) --You can put 2022 here

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

    then "True"
    else "False"

    Best Regards

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Do i put the 2021 in the bracket below or outside? Thanks. Tried it but got errors.

     

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

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

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

    then "True"
    else "False"

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    thank you Anonymous and Dinesh_Suranga. Both lifesavers! have a good one. take care