Forum Discussion

johnpjustus's avatar
johnpjustus
Helper IV
3 years ago

Restrict records to current year

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
Need Help

4 Replies

  • collinsg's avatar
    collinsg
    Solution Sage
    3 years ago

    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's avatar
      johnpjustus
      Helper IV
      3 years ago

      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's avatar
        collinsg
        Solution Sage
        3 years ago

        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.