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
