API limitation > How can I bring all records from Shopify into Power BI?

[_Ester_]

Hello all!
I’ve prepared a report in Power BI to track some insights on customer orders (the e-commerce platform is Shopify).

The report is designed to update itself, if not in real-time, at least periodically.

With the help of a technician, I managed to connect Shopify to Power BI via API.

However, I’ve just discovered that there’s a limit of 50 records that can be imported 😞

I’ve managed to increase the limit to 250 by adding “?limit=250” to the end of the URL. For example, for the "orders" and "customers" tables:

https://{apikey}:{password}@{hostname}/admin/api/2023-4/orders.json?limit=250
https://{apikey}:{password}@{hostname}/admin/api/2023-4/customers.json?limit=250 

But from what I understand, I can’t increase this limit further.

My goal:

• I need all the data from the beginning of the e-commerce.
• I need to be able to update the data at least monthly.
  New records should be added;
  records that match the order_id and customer_id should be replaced and updated to the latest version;
  existing records, if there is no match (meaning no newer data), should remain unchanged.

Possible solutions I’ve considered:

• Do something with the API (I’m completely clueless here).
• Download all the historical data from the e-commerce once, then download the latest 250 records every so often.
  Questions: Can I merge the latest 250 records with the previous ones automatically through the API, or do I have to load two separate tables and then merge them?
  Can I somehow automate the merge between the old and new tables?
• Use a third tool. For example, download all the historical data from Shopify as a CSV and load it into Power BI. Periodically, I would download the last month’s data as a CSV, load it into Power BI, and merge it with the historical data table.
  Or using BigQuery (I have an intermediate knowledge of SQL, but I have no idea how to connect BigQuery to Power BI).

Is there any realistic or creative solution that comes to mind?

Please explain it simply if possible, as I’m just starting to learn Power BI and don’t know anything about APIs.

Thank you so much!

[QueryWhiz]

Hi @_Ester_ were you able to find a solution? As a workaround, maybe you can try to test your connection with a 3rd party connector. I've tried windsor.ai, supemetrics 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 Shopify connector in the data sources list:

After that, you need to follow instructions and install the windsor.ai app from the Shopify App Store:

then on preview and destination page you will see a preview of your Shopify fields

There just select the fields you need. Finally, just select PBI as your data destination and finally just copy and paste the url on PBI --> Get Data --> Web --> Paste the url.

[Kedar_Pande · Accepted solution]

@_Ester_ ,
It sounds like you’ve done great work so far.

Since the API has a limit of 250 records, a good approach is to download your historical data first (e.g., as a CSV) and then periodically pull the latest 250 records. Use append queries in Power BI to merge this new data with your historical dataset, matching by order_id and customer_id.