Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jplopes
New Member

Filtering in the importation of a Dynamics CRM Source

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

 

 

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

Hi jplopes,

 

Based on your description, you want to add filter on OData feed, right?

If it is a case, you can use ‘URL Conventions’ to achieve your requirement, sample:

let

    Source = OData.Feed("services.odata.org/V4/Northwind/Northwind.svc/Orders?

    $select=OrderID, CustomerID, ShipName

&$filter=(OrderID eq 10250) ")

in

Source

 

Before convent:

Capture2.PNG

 

After:

Capture.PNG

 

 

I have modified your formula:

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

    &$filter=(affero_ano eq 2016)")

in

  Origem

 

 

For more detail information, you can refer to below article:

URL Conventions (OData Version 3.0)

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

1 REPLY 1
v-shex-msft
Community Support
Community Support

Hi jplopes,

 

Based on your description, you want to add filter on OData feed, right?

If it is a case, you can use ‘URL Conventions’ to achieve your requirement, sample:

let

    Source = OData.Feed("services.odata.org/V4/Northwind/Northwind.svc/Orders?

    $select=OrderID, CustomerID, ShipName

&$filter=(OrderID eq 10250) ")

in

Source

 

Before convent:

Capture2.PNG

 

After:

Capture.PNG

 

 

I have modified your formula:

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

    &$filter=(affero_ano eq 2016)")

in

  Origem

 

 

For more detail information, you can refer to below article:

URL Conventions (OData Version 3.0)

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.