Shopify API

[alan19]

I am trying to bring shopify  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.

let

    key = "API",

    url = "https://abc.myshopify.com/admin/api/2024-04/orders.json",

    delay = #duration(0, 0, 2, 0), 

    GetOrdersWithSinceId = (since_id as nullable text) as record =>

        let

            queryParameters = if since_id = null then

                [

                    status = "any",

                    limit = "250",

                    order = "created_at asc"

                ]

            else

                [

                    status = "any",

                    limit = "250",

                    order = "created_at asc",

                    since_id = since_id

                ],

            response = Json.Document(

                Web.Contents(

                    url,

                    [

                        Headers = [#"X-Shopify-Access-Token" = key],

                        Query = queryParameters

                    ]

                )

            )

        in

            response,

    GetMaxId = (orders as list) as nullable text =>

        try

            Text.From(List.Max(List.Transform(orders, each _[id])))

        otherwise

            null,

    GetAllOrders = List.Generate(

        () => [since_id = null, result = GetOrdersWithSinceId(null), orders = GetOrdersWithSinceId(null)[orders]],  

        each List.Count([orders]) > 0,  // condition to continue

        each [

            since_id = GetMaxId([orders]), 

            result = Function.InvokeAfter(() => GetOrdersWithSinceId(since_id), delay),

            orders = result[orders]

        ],  

        each [orders]  

    ),

    ExtractOrders = List.Combine(List.Transform(GetAllOrders, each _)),

    OrdersTable = Table.FromList(ExtractOrders, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    #"Expanded Column1" = Table.ExpandRecordColumn(OrdersTable, "Column1", {"id", "created_at", "total_price", "customer", "line_items"}),

    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1", {

        {"created_at", type datetimezone}

    }),

    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"created_at", Order.Ascending}})

in

    #"Sorted Rows"

[Aryna]

Hi @alan19 , 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.

[ReportGuru]

Hi @alan19 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. 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]

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]

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

[alan19]

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