Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Union two datasets to circumvent Salesforce report 2000 row API

Hi,

 

I have a Salesforce report that I would like to connect to Power BI, but it is over 25K rows, so the direct Salesforce report API is not an option as the limit is 2K rows. I had an idea to download the current report as a CSV, use that as my data source, and then refresh the Salesforce report API each day to add any new rows to my original dataset that have been added that day. Is there a clever way to do this within PowerBI? Thank you 

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    amitchandak , Anonymous - Well, I think the easy way to do this would be to import the CSV file once, turn off refresh. Create another table using the API. Append the two tables, remove duplicates. You could run that until all 2000 API entries are duplicates and at that point you just update your CSV file.

     

    Not pretty but functional. Could definitely be improved upon but that would at least be a start.

5 Replies

    • Greg_Deckler's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      6 years ago

      amitchandak , Anonymous - Well, I think the easy way to do this would be to import the CSV file once, turn off refresh. Create another table using the API. Append the two tables, remove duplicates. You could run that until all 2000 API entries are duplicates and at that point you just update your CSV file.

       

      Not pretty but functional. Could definitely be improved upon but that would at least be a start.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Thanks! I'll try this

  • ReportBuilder07's avatar
    ReportBuilder07
    Icon for Helper III rankHelper III
    2 years ago

    Hi, I know this reply is very late lol, but were you able to find a solution? As a workaround, maybe you can try to test your connection with a 3rd party connector and you won't have to unite 2 datasets later. 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. 

     

  • metrica's avatar
    metrica
    Icon for Post Prodigy rankPost Prodigy
    3 months ago

    Hi Anonymous,

     

    The CSV-union approach is clever, but worth knowing about a few trade-offs before you commit to it:

    • It catches new rows, but doesn't propagate updates to existing rows (the CSV snapshot stays frozen)
    • If new rows per day ever exceed 2,000, you silently lose data
    • The CSV baseline still has to be manually refreshed periodically
    • Power BI Service refresh on CSV files requires gateway setup

    A cleaner path for your 25K-row report is to use a connector that doesn't have the 2K cap in the first place. Power BI Connector for Salesforce on AppExchange pulls the full report (25K rows or more) directly into Power BI - no CSV staging, no union logic, no manual snapshot management. Refreshes daily through standard Power BI scheduling.

     

    Free 30-day trial if you'd like to test it on your specific report. Setup guides at https://metricasoftware.com/, demo available on request.

     

    Cheers,

    Metrica Team.