Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
Solved! Go to Solution.
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:
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:
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
84 | |
84 | |
72 | |
49 |
User | Count |
---|---|
143 | |
130 | |
108 | |
64 | |
55 |