How to remove/exclude data from query

[hellodazza]

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.

[Dinesh_Suranga · Accepted solution]

@hellodazza ,

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.

[Dinesh_Suranga]

@hellodazza 

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.

[hellodazza]

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]

@hellodazza 

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.

[hellodazza]

Sorry how do u do with switch case? 

For if function can provide step by step? 

Thanks.

[Dinesh_Suranga · Accepted solution]

@hellodazza ,

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.

[hellodazza]

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]

@hellodazza 

Do you have separate calendar table?

[hellodazza]

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.

[v-yiruan-msft]

Hi @hellodazza ,

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

[hellodazza]

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"

[v-yiruan-msft · Accepted solution]

Hi @hellodazza ,

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

[hellodazza]

thank you @v-yiruan-msft and @Dinesh_Suranga. Both lifesavers! have a good one. take care