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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
vsslasd1
Helper III
Helper III

Performance Issues with CRM 365 Online Opportunities and related entities in PowerBI

Hello, 

we have an issue where an Opportunity table takes hours to load in PowerBI Desktop: 

This works fairly quickly for two workorders:

et

    Source = OData.Feed("https://companyname.api.crm.dynamics.com/api/data/v9.1/", null, [Implementation="2.0"]),

    opportunities_table = Source{[Name="opportunities",Signature="table"]}[Data],

    #"Expanded ws_primarytechnicianid" = Table.ExpandRecordColumn(opportunities_table, "ws_primarytechnicianid", {"fullname"}, {"ws_primarytechnicianid.fullname"}),

    #"Changed Type" = Table.TransformColumnTypes(#"Expanded ws_primarytechnicianid",{{"statecode", type text}}),

    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","0","Open",Replacer.ReplaceText,{"statecode"}),

    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","1","Won",Replacer.ReplaceText,{"statecode"}),

    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","2","Lost",Replacer.ReplaceText,{"statecode"}),

    #"Expanded new_primarylocation" = Table.ExpandRecordColumn(#"Replaced Value2", "new_primarylocation", {"new_locationid", "new_locname", "statuscode", "ws_name", "statecode", "ws_locationid"}, {"new_primarylocation.new_locationid", "new_primarylocation.new_locname", "new_primarylocation.statuscode", "new_primarylocation.ws_name", "new_primarylocation.statecode", "new_primarylocation.ws_locationid"}),

    #"Removed Columns" = Table.RemoveColumns(#"Expanded new_primarylocation",{"new_primarylocation.statuscode", "new_primarylocation.statecode"}),

    #"Expanded owninguser" = Table.ExpandRecordColumn(#"Removed Columns", "owninguser", {"fullname"}, {"owninguser.fullname"}),

    #"Added Conditional Column" = Table.AddColumn(#"Expanded owninguser", "probability", each if [ws_probability] = 100000000 then "90% - Have Verbal" else if [ws_probability] = 717760000 then "1% - All Others" else if [ws_probability] = 717760001 then "25% - Qualifying" else if [ws_probability] = 717760002 then "50% - Last 2 or 3" else if [ws_probability] = 717760003 then "75% - Negotiating  " else if [ws_probability] = 717760004 then "100% - Received Confirmation" else null),

    #"Expanded opportunity_opportunitysalesprocess" = Table.ExpandTableColumn(#"Added Conditional Column", "opportunity_opportunitysalesprocess", {"name"}, {"opportunity_opportunitysalesprocess.name"}),

    #"Removed Columns1" = Table.RemoveColumns(#"Expanded opportunity_opportunitysalesprocess",{"opportunity_opportunitysalesprocess.name"}),

 

 

   

    #"Removed Other Columns" = Table.SelectColumns(#"Removed Columns1",{"new_workordernumber", "actualclosedate", "ws_opportunitynumber", "new_subcontractcost", "new_materialcost_base", "ws_estimatedgrossmargin", "statecode", "new_calculatedmargin", "new_laborcost", "owninguser.fullname"}),

 

    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([new_workordernumber] = "1803-1068" or [new_workordernumber] = "1805-1415"))

in

    #"Filtered Rows"

 

But when the Filtered Rows statement is changed to not include null or blank values the query takes hours to execute. 

There are only about 44,000 rows in Opportunities. 

How Can this code be optimized ? 

 

Thank you

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

Hi @vsslasd1 

 

You might try the new feature: New Query Diagnostics

https://www.neowin.net/news/query-diagnostics-for-power-bi-desktop-reaches-general-availability 

 

Here's similar thread also could be your reference:

https://community.powerbi.com/t5/Desktop/How-to-Improve-Query-Reference-performance-for-large-tables... 

 

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

View solution in original post

1 REPLY 1
v-diye-msft
Community Support
Community Support

Hi @vsslasd1 

 

You might try the new feature: New Query Diagnostics

https://www.neowin.net/news/query-diagnostics-for-power-bi-desktop-reaches-general-availability 

 

Here's similar thread also could be your reference:

https://community.powerbi.com/t5/Desktop/How-to-Improve-Query-Reference-performance-for-large-tables... 

 

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.