Filtering Dynamics CRM

[jplopes]

Hey there,

I'm importing a data from a Dynamics CRM source. But since it's a pretty big source, it ends up that it gets too slow or even returns with some kind of error.

To solved it, I have selected only couple columns I need.

But now, I need to configure it in a way to bring less data filtering by variable. For example, in a year column (containing 2014, 2015, 2016 ie), I need to filter it only by 2016. I know I could import all data and then use a filter in a next step; but this would load 2014, 2015, which are useless for my analysis.

Follow the code I have changed (its in portugues). The name of the colummns are in green and the name of the table is red.

let
Origem = let
Origem = OData.Feed("https://XXXXXXXX.crm2.dynamics.com/XRMServices/2011/OrganizationData.svc/OpportunitySet?$select=Name, affero_quantidade, affero_Valor, affero_valor_total_oportunidade, CustomerId, OwnerId, new_ProbabilidadedaOportunidadeAberta, affero_produto, affero_bu, new_VendaCross, StepName, StateCode, EstimatedCloseDate, affero_ano, affero_data_retorno"),
OpportunitySet_table = Origem{[Name="OpportunitySet",Signature="table"]}[Data]
in
Origem

I want something like this: affero_ano = 2016

Thanks in advance,

Bests,
João Pedro

[Greg_Deckler]

Hmm, was trying to test this but can't even get the $select syntax to work. I get the error:

DataSource.Error: Microsoft.Mashup.Engine1.Library.Resources.HttpResource: Request failed:
OData Version: 3 and 4, Error: The remote server returned an error: (400) Bad Request. (Query options $expand, $filter, $orderby, $inlinecount, $skip and $top cannot be applied to the requested resource.)
OData Version: 4, Error: The remote server returned an error: (400) Bad Request. (Query options $expand, $filter, $orderby, $inlinecount, $skip and $top cannot be applied to the requested resource.)
OData Version: 3, Error: The remote server returned an error: (400) Bad Request. (Query options $expand, $filter, $orderby, $inlinecount, $skip and $top cannot be applied to the requested resource.)
Details:
DataSourceKind=OData
DataSourcePath=https://[tenant].crm.dynamics.com/XRMServices/2011/OrganizationData.svc

Which is a bit senseless since all of the things that are mentioned that I cannot do, I'm not doing. My Power Query was simply:

let
    Source = OData.Feed("https://[tenant].crm.dynamics.com/XRMServices/2011/OrganizationData.svc?$select=CreatedOn"),
    OpportunitySet_table = Source{[Name="OpportunitySet",Signature="table"]}[Data]
in
    OpportunitySet_table

However, given the error message I am receiving, I am guessing that you cannot use the $filter syntax to pre-filter your ODATA query, which was the path I was headed down:

https://msdn.microsoft.com/en-us/library/hh169248(v=nav.90).aspx

[jfattic]

Has anyone figured out how to filter Dynamics API queries to minimize the data being imported? We have a massive amount of data and it takes forever to do anything.