Restrict records to current year

[johnpjustus]

Hi,

I am creating a dataflow from spread sheet in sharepoint using webapi connector.

  Source = Excel.Workbook(Web.Contents("https://test.sharepoint.com/sites/ORT-Shared%20Documents/Training/GSAA.xlsx"), null, true),

  #"Navigation 1" = Source{[Item = "IBG", Kind = "Sheet"]}[Data],

This spread sheet has few fields with lot of records. The date field has values all the way from 2001. I want to restrict values to current year.

How can I achieve that so I don't bring all the records back from 2001 to Power Query online, so that the dataset that I create using this data flow contains only current year data

Thanks,

John

[collinsg]

Hi John,

Adding this line of M code will filter for records in the current year.

= Table.SelectRows(#"Previous Step", each Date.IsInCurrentYear([Date]))

•  For "Previous Step" substitute the name of your previous step.
• For [Date] substitute the name of your date column.

To add a line of M code, select the step after which you want to filter and press "fx" beside the formula bar.

Hope this helps.

[johnpjustus]

Thank you so much and it worked when I followed the steps you outlined.

So I believe in doing so whenever the dataflow is scgeduled to run, it sends only the current year query to source correct?

Thanks,

John

[collinsg]

Hello John,

I will have to be careful with my answer because when you say "sends only...query to source" the precise answer, in general, depends on whether query folding occurs. However the precise answer may not matter to you if you're only interested in the "bottom line" result - i.e. will the query return records for the current year...the answer is "yes".

To go back to the precise answer, some sources, such as SqlServer can carry out filtering before returning a result. If this is the case Power Query will delegate filtering to the source and send a query to the source asking it to return filtered results.

If a source cannot carry out filtering (e.g. an Excel file), Power Query will fetch and scan the source line by line to do the filtering. In this case the query is not "sent" but the result is still filtered.

You can read more about query folding here.

Hope this helps.

[johnpjustus]

Thank you so much for the information, really helpful.

One last thing to check. Say I have the following file:

Now using the M-language I filtered for current year which is 2023. If I do filtering (Filter rows from the UI) for 2023 instead of M-language, the process is same correct?

And I understand the query folding. In this case, Power BI brings all the data to memory and then filters the data for 2023.

LocID	Location	Date	AgeGrp	PopMale	PopFemale	PopTotal
5	Antigua and Barbuda	1/4/2015	0-4	3.543	3.436	6.979
6	Argentina	1/5/2022	0-4	1836.187	1769.816	3606.003
7	Armenia	1/6/2023	0-4	107.368	93.715	201.083
8	Aruba	1/7/2023	0-4	3.402	3.257	6.659
9	Australia	1/8/2023	0-4	750.45	711.435	1461.885
10	Austria	1/9/2023	0-4	200.53	190.404	390.934
11	Afghanistan	1/10/2023	0-4	2715.07	2600.535	5315.605
12	Albania	1/11/2023	0-4	92.622	81.239	173.861
13	Algeria	1/12/2023	0-4	1966.415	1885.869	3852.284
14	Angola	1/13/2023	0-4	2233.041	2239.035	4472.076
15	Antigua and Barbuda	1/14/2023	0-4	3.543	3.436	6.979
16	Argentina	1/15/2023	0-4	1836.187	1769.816	3606.003
17	Armenia	1/16/2023	0-4	107.368	93.715	201.083
18	Aruba	1/17/2023	0-4	3.402	3.257	6.659
19	Australia	1/18/2023	0-4	750.45	711.435	1461.885
20	Austria	1/19/2023	0-4	200.53	190.404	390.934