Combining Multiple 'Web' Data Sources

[Revokez]

Hi

I'm trying to import some Shopify data using their API using the Web data source, the format of this is:

https://{API_KEY}:{PASSWORD}@{STORE_NAME}.myshopify.com/admin/orders.json

The actual import of this is fine, however Shopify limits the data from a single query to 50 by default and 250 if you apply the following:

https://{API_KEY}:{PASSWORD}@{STORE_NAME}.myshopify.com/admin/orders.json?limit=250

Unfortunately I require more than 250 rows to be imported. Shopify suggests this is handled by having multiple requests in this format:

https://{API_KEY}:{PASSWORD}@{STORE_NAME}.myshopify.com/admin/orders.json?limit=250&page=1

https://{API_KEY}:{PASSWORD}@{STORE_NAME}.myshopify.com/admin/orders.json?limit=250&page=2

https://{API_KEY}:{PASSWORD}@{STORE_NAME}.myshopify.com/admin/orders.json?limit=250&page=3

etc.

Unfortunately Shopify is very restricted in the way that it handles the above so the solution to this issue would need to be resolved on the Power BI side.

The question is, is there a way to string the above web data sources together under a single data source within Power BI so that I don't have to import them one by one and tie them up after the fact?

Thank you

[ReportRanger]

Hi @Revokez were you able to find a solution? As a workaround, maybe you can try to test your connection with a 3rd party connector, which does not have this limiations from Shopify API. 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.

[EugeneLebedev]

Instead of writing this code yourself you can get the ready-made code from here along with the service for deploying this code for you: https://vidi-corp.com/how-to-connect-shopify-to-power-bi/

This way you don't have to pay subscription for those connector services, just install the code once and that's it.

[Gordonlilj]

Hi,

You could probably make use of parameters in order to loop through the sources

Example:Looping through a table to get data from Web in Power BI 

[Revokez]

Thanks, @Gordonlilj , unfortunately I've stepped this through and it doesn't seem to work in this scenario, unless im handling it wrong. I've also attempted this using the inbuilt parameters functionality within Power BI and can get seperate perameters for the different '?pages=' however I'm stuck at the stage of then combining all of those parameters together under a new table.

[Gordonlilj]

You can create and invoke a function in order to combine everything into a single table.

I posted something similar here: Data Download from Website and connection with Power BI.

The solution should work the same in your case

[Revokez · Accepted solution]

Thanks @Gordonlilj 

I had actually just managed to achieve this before you posted, however I'll have a look at your post to see if that's a better solution. I muddled this in the following ways:

1. Import the first source as web source: https://{API_KEY}:{PASSWORD}@{STORE_NAME}.myshopify.com/admin/orders.json?limit=250

2. Transform the table how I required it.

3. Advanced editor this table:

Before let add: (myPages) =>

Change the URL for the web contents source to end with: ?limit=250"&myPages)),

4. Rename the property to 'fnproducts' and Close & Apply.

5. (There's probably a much cleaner way to do this but) Create an excel sheet with values in different rows of: &page=1 , &page=2, &page=3 (etc. depending on how many pages you want to pull in)

6. Import the excel sheet as a new data source.

7. Edit the query of the excel sheet

8. Add colum > Custom Column

9. Use the following custom column formula: fnproducts([Column1]) and hit OK.

10. Step through data privacy.

11. Remove column1 

12. Expand remaining column.

This then pulls in all of the data into a single table. I think it's probably a bit messier than it needs to be but it works - thank you for your help.