Shopify API

Question

I am trying to bring shopify&nbsp; orders into Power BI desktop but seems I can not import more than 250 orders, this query brings 250 orders and fails at 251. anyone has any idea?Thank you.&nbsp;let&nbsp; &nbsp; key = "API",&nbsp; &nbsp; url = "https://abc.myshopify.com/admin/api/2024-04/orders.json",&nbsp; &nbsp; delay = #duration(0, 0, 2, 0),&nbsp;&nbsp; &nbsp; GetOrdersWithSinceId = (since_id as nullable text) as record =&gt;&nbsp; &nbsp; &nbsp; &nbsp; let&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; queryParameters = if since_id = null then&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; status = "any",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; limit = "250",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; order = "created_at asc"&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; else&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; status = "any",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; limit = "250",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; order = "created_at asc",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; since_id = since_id&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; response = Json.Document(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Web.Contents(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; url,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Headers = [#"X-Shopify-Access-Token" = key],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Query = queryParameters&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; in&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; response,&nbsp; &nbsp; GetMaxId = (orders as list) as nullable text =&gt;&nbsp; &nbsp; &nbsp; &nbsp; try&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Text.From(List.Max(List.Transform(orders, each _[id])))&nbsp; &nbsp; &nbsp; &nbsp; otherwise&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; null,&nbsp; &nbsp; GetAllOrders = List.Generate(&nbsp; &nbsp; &nbsp; &nbsp; () =&gt; [since_id = null, result = GetOrdersWithSinceId(null), orders = GetOrdersWithSinceId(null)[orders]],&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; each List.Count([orders]) &gt; 0,&nbsp; // condition to continue&nbsp; &nbsp; &nbsp; &nbsp; each [&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; since_id = GetMaxId([orders]),&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; result = Function.InvokeAfter(() =&gt; GetOrdersWithSinceId(since_id), delay),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; orders = result[orders]&nbsp; &nbsp; &nbsp; &nbsp; ],&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; each [orders]&nbsp;&nbsp;&nbsp; &nbsp; ),&nbsp; &nbsp; ExtractOrders = List.Combine(List.Transform(GetAllOrders, each _)),&nbsp; &nbsp; OrdersTable = Table.FromList(ExtractOrders, Splitter.SplitByNothing(), null, null, ExtraValues.Error),&nbsp; &nbsp; #"Expanded Column1" = Table.ExpandRecordColumn(OrdersTable, "Column1", {"id", "created_at", "total_price", "customer", "line_items"}),&nbsp; &nbsp; #"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1", {&nbsp; &nbsp; &nbsp; &nbsp; {"created_at", type datetimezone}&nbsp; &nbsp; }),&nbsp; &nbsp; #"Sorted Rows" = Table.Sort(#"Changed Type",{{"created_at", Order.Ascending}})in&nbsp; &nbsp; #"Sorted Rows"&nbsp;

eugenelebedev · Answer

Shopify API is too difficult to create a custom Power BI connector in my opinion. I would recommend instead to extract data into an Azure SQL Server database. This way you work around most of the Shopify API limitations. Here is a ready-made solution for that: https://vidi-corp.com/shopify-power-bi-connector/

wanderingbi · Answer

The Shopify API seems to limit input arrays to 250 entries:

Shopify API rate limits

"Input arguments that accept an array have a maximum size of 250. Queries and mutations return an error if an input array exceeds 250 items."

anonymous · Answer

this is right but any idea how to deal with it?&nbsp;

reportguru · Answer

Hi Anonymous&nbsp;were you able to find a solution? As other comment mentioned, having a custom connector for Shopify is hard as it requires some technical knowledge. As a workaround, maybe you can try to test your connection with a 3rd party connector.&nbsp;I've tried&nbsp;windsor.ai, supemetrics and funnel.io. I stayed with windsor because it is much cheaper so just to let you know other options.&nbsp;In case you wonder,&nbsp;to make the connection&nbsp;first&nbsp;search for the Shopify connector in the data sources list:&nbsp;&nbsp;After that, you need to follow instructions and install the windsor.ai app from the Shopify App Store:&nbsp;&nbsp;then on&nbsp;preview and destination&nbsp;page you will see a preview of your Shopify fields&nbsp;&nbsp;There just select the fields you need. Finally, just select PBI as your data destination and finally just copy and paste the url on&nbsp;PBI --&gt; Get Data --&gt; Web --&gt; Paste the url.&nbsp;

aryna · Answer

Hi Anonymous&nbsp;, there’s a ready-made solution that can save you a lot of time. They offer a free Shopify Power BI template that already includes the key Shopify tables and prebuilt data relationships, so you can just connect your data and get started: https://vidi-corp.com/shopify-analytics-dashboards/It’s a great option if you’re not familiar with Shopify data modeling or if you simply want to speed up the setup process.

Forum Discussion

Shopify API

5 Replies

Related content

Pagination in Shopify API

GraphQL- Shopify Pagination

Pagination of Shopify API

Shopify API - JSON Query Parsing

Monitor Shopify Sales | Innotemplates