How to Send WooCommerce Orders to Power BI

Connect WooCommerce to Power BI without a plugin or a database export: a Spojit workflow pushes new orders into a Power BI dataset every 30 minutes so sales dashboards stay current.

What This Integration Does

The usual ways to get WooCommerce into Power BI are a reporting plugin, a direct connection to the WordPress MySQL database (which hosting providers rarely allow), or a weekly CSV export. This workflow uses the WooCommerce REST API instead. Every 30 minutes it reads orders created since the last run, flattens each one into a row (order number, date, status, total, tax, shipping, item count, country, payment method, coupon) and pushes the rows into a Power BI push dataset. Line items go to a second table so you can report on products as well as orders.

Runs are incremental: each one asks WooCommerce only for orders created after a window that overlaps the previous run slightly, and drops order IDs it already sent. Pushes append rows, so status changes after an order is created (refunds, cancellations) are best handled as described in the Tips.

Prerequisites

• A WooCommerce connection in Spojit with a REST API key that has read access (WooCommerce -> Settings -> Advanced -> REST API).
• A Power BI HTTP Requests connection (OAuth 2.0) and a push dataset, set up as in the "How to Push JSON Data into Power BI from Any Workflow" tutorial in this help center, with two tables:
  • Orders: order_id (Int64), number (String), created_at (DateTime), status (String), total (Double), total_tax (Double), shipping_total (Double), items (Int64), country (String), payment_method (String), coupon (String).
  • OrderLines: order_id (Int64), created_at (DateTime), sku (String), product (String), quantity (Int64), line_total (Double).

Step 1: Schedule Trigger

Add a Trigger node with type Schedule, cron */30 * * * *, in the store's timezone.

Step 2: List Orders Since the Last Run

Add a Connector node on WooCommerce in Direct Mode with list-orders:

• after: the trigger time minus 40 minutes, in ISO 8601 (use the Date & Time connector's subtract and format tools).
• status: any
• per_page: 100

Name the output orders. If a busy half hour can exceed 100 orders, page through with a Loop in While mode, increasing page until a page comes back with fewer than 100 orders.

Step 3: Shape Order Rows

Add a Transform node with Iterate over set to the order list (copy the exact path from the step's output panel). Output variable order_rows:

{
  "order_id": {{ item.id }},
  "number": "{{ item.number }}",
  "created_at": "{{ item.date_created_gmt }}Z",
  "status": "{{ item.status }}",
  "total": {{ item.total }},
  "total_tax": {{ item.total_tax }},
  "shipping_total": {{ item.shipping_total }},
  "items": (count of item.line_items),
  "country": "{{ item.shipping.country }}",
  "payment_method": "{{ item.payment_method_title }}",
  "coupon": "{{ item.coupon_lines.0.code }}"
}

WooCommerce sends money fields as strings such as "49.90"; set those Transform fields to type number so Power BI receives numbers. date_created_gmt has no timezone suffix, so appending Z marks it as UTC. Use the Array Tools connector's length tool for the item count.

Step 4: Shape Line Item Rows

Add a second Transform that produces one row per line item. The simplest route is a Code Runner step (execute-javascript) that loops over the orders and their line_items, returning { order_id, created_at, sku, product: name, quantity, line_total: Number(total) } for each. Name the output line_rows.

Step 5: Push Both Tables

Add two Connector nodes on the Power BI HTTP connection with http-post:

• /groups/YOUR_WORKSPACE_ID/datasets/YOUR_DATASET_ID/tables/Orders/rows with body { "rows": {{ order_rows }} }
• /groups/YOUR_WORKSPACE_ID/datasets/YOUR_DATASET_ID/tables/OrderLines/rows with body { "rows": {{ line_rows.result }} } (Code Runner returns its value under result)

Before pushing, drop orders whose ID you already sent in the previous run (keep the last run's IDs in a File Store file or a small database table), so the overlap window never creates duplicates.

Step 6: Build the Report

In Power BI, create a relationship between Orders[order_id] and OrderLines[order_id]. Useful first visuals: revenue by day, average order value, orders by country, top products by line_total, and coupon usage.

Tips

• Status changes: push datasets cannot update rows, so an order that is refunded tomorrow still shows its original status. Add a daily run that lists orders with status refunded and cancelled for the last few days and pushes them into a StatusChanges table, then use the latest status per order in your measures.
• Exclude test and draft orders: filter out checkout-draft and pending orders in the Transform if your store creates many abandoned checkouts.
• Backfill: run once manually with after set to the start of the year to load history (up to 200,000 rows per table with basicFIFO retention).
• Webhooks instead of polling: for near-real-time dashboards, trigger the same push from WooCommerce's order.created webhook.

Common Pitfalls

• Strings for money: pushing "49.90" into a Double column fails the request. Convert in the Transform.
• Local versus GMT dates: date_created is in the store's timezone without an offset; date_created_gmt is UTC. Use the GMT field so Power BI's date handling is correct.
• REST API blocked: security plugins and some hosts block /wp-json/wc/v3. If the step returns 401 or 403 with a valid key, allow the REST API for the key's user.
• Push dataset retirement: Microsoft plans to stop creation of new push datasets on 31 October 2027. For long-term history, sync orders to a database Power BI reads.

Testing

Run the workflow manually with a one-day window. Compare the order count and total revenue with WooCommerce -> Analytics for the same day (allowing for refunds). When they match, turn the schedule on.

Learn More

• WooCommerce connector documentation
• HTTP Requests connector documentation
• Code Runner documentation