Append or Union (or another way) Performance problem

[Anonymous]

Due to the API limit of 2000 rows in Salesforce, I have had to create 75 seperate Salesforce reports to inport into my Data model. I then need to group them together into three tables (Sold Deals = 25 Reports, Lost Deals = 25 Reports, and Pipeline Deals = 25 Reports) The reports that need to be grouped all have exactly the same format and fields. I have imported them all as Queries, and then used UNION to combine them into three tables (Sold, Lost and Pipeline).

However, the model is almost unusable due to the terribly slow performance.

I am newish to Power BI, and have read about performance, and how to maximise it, but I need all the fields in my tables, and I don't know what else to try.

Can anyone please advise me and point me in th eright direction? I have a fantastic and extremely valuable report, but the end users will never use it unless I can get it to work faster.

Thank you.

Andrew

[ReportMaster]

Hi @Anonymous were you able to find a solution? as a workaround, maybe you can try to test your connection with a 3rd party connector that uses the Salesforce objects connector, so you shouldn't face this issue with rows limit. I've tried windsor.ai, supermetrics and funnel.io. I stayed with windsor because it is much cheaper so just to let you know other options. In case you wonder, to make the connection first search for the Salesforce connector in the data sources list:

After that, just grant access to your Salesforce account using your credentials, then on preview and destination page you will see a preview of your Salesforce fields:

There just select the fields you need. It is also compatible with custom fields and custom objects, so you'll be able to export them through windsor.  Finally, just select PBI as your data destination and finally just copy and paste the url on PBI --> Get Data --> Web --> Paste the url. 

[Anonymous]

Hi @Anonymous,

In fact, I also not has effective solutions to solve performance issue which cased with api result limit.

In my opinion, I'd like to suggest you to pull/export all result as local files and use power bi to get data from them, it should increase load data performance.

In addition, you can also try to write a custom function to analytics and re-invoke api to get all records at same time.

How to perform loop in M Query and collate result of the same

Notice: current custom functions not works on service side.

Regards,

Xiaoxin Sheng