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

[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.