Filtering data (Date/Time) taking forever from a large file.

[Darger]

Hello,

I'm pretty new to Power Query, please excuse my ignorance.

I'm connecting CRM 2016 to power query through an OData Feed. I can filter most columns relatively quickly. When I filter for date PQ tries to download the entire data source, at least 50 GB in size.

I tried:

-I removed all extra columns

-Filtering for date/time

-Splitting date/time, then only filter for Date

-Duplicating the date column and extracting the Year and trying to filter that column

They all cause the same problem, PQ begins downloading the entire data source. I tried leaving this overnight to see what would happen and after downloading 50BG PQ just crashed...

Do you have any advice on how to filter this data? I don't have access to the original source.

[BA_Pete · Accepted solution]

Hi @Darger ,

It sounds like you should be doing your initial filtering within the connection string, similar to how you would manage an API source, in order to implement the filters server-side. You may need to check your source documentation to find the correct parameter names, but you'd create a connection string something like this:

https://analytics.dev.azure.com/{organization}/{project}/_odata/v3.0-preview/WorkItems?
    $select=WorkItemId,Title,WorkItemType,State,CreatedDate
    &$filter=startswith(Area/AreaPath,'{area path}')
    &$orderby=CreatedDate desc
    &$top=10

MS Learn source:

https://learn.microsoft.com/en-us/azure/devops/report/powerbi/odataquery-connect?view=azure-devops 

Additionally, a lot of ODATA feeds actually support query folding, even though PQ may not display the 'View Native Query' option as you might expect. As such, you should check carefully that you haven't broken folding in a step previous to your filter step causing PQ to implement the filter locally.

MS Learn source:

https://learn.microsoft.com/en-us/power-query/power-query-folding 

Pete