Shopify Power BI integration: records and refresh checks
Connect Shopify reporting to Power BI through a verified data route, with clear order, refund and currency definitions and checks for incomplete refreshes.
- Published
- Last updated
Yes, you can connect Shopify to Power BI through exported files or a separately configured data pipeline. A documented starting route is Shopify’s order export and Microsoft’s Text/CSV connector. It gives you a reporting snapshot, with a new export needed before Power BI can show later store activity.
Choose the records and reporting period before you choose a chart. An order total, a product sale and a bank payout describe different events, so they need separate definitions.
Choose the supported data route
Use Shopify’s order CSV export for a bounded reporting snapshot, then import it through Power Query’s Text/CSV connector. This is a file import, not a native Shopify Power BI connector. It requires access to the store’s order export and to the file you load, without a Shopify app token.
- In Shopify admin, open Orders and filter the required orders or reporting period.
- Select Export and choose the order range and CSV format. Export transaction histories separately when captured payments are part of the report.
- In Power BI Desktop, use Get data, then Text/CSV. Select Transform Data so you can inspect types and split order-level records from product rows.
- Save the source file location and record the export’s coverage. Refreshing Power BI reads that source again. It doesn’t request a new Shopify export.
Shopify’s order export instructions describe the available fields. Start with orders and line items. Add refund events, customers or payment records only when the intended measures need them. A product-demand report needs line items. A settlement report also needs payout transactions.
For automated extraction, a custom pipeline can use Shopify’s GraphQL Admin application programming interface (API). Its order access rules give read_orders a default 60-day history. Older orders require the additional read_all_orders scope, with approval requirements determined by the app type. Request only the permissions needed for the selected objects.
That custom route needs an extraction service and a stored reporting source Power BI can read. A third-party connector adds its own subscription and extraction contract. Require its maintained documentation to name supported objects, historical coverage and update behaviour before adopting it. The file route here assumes none of those connector entitlements.
For a shared service report, budget for the Power BI publishing and workspace licence or capacity your organisation uses. Microsoft’s refresh documentation distinguishes Pro schedules from Premium-backed capacity. Pro supports up to eight scheduled refreshes daily. Premium, Premium per user or Fabric capacity supports up to 48. Those schedules refresh imported data, not the manual export in Shopify.
Verify the imported retail records
Match a small set of Shopify records to the imported rows before trusting any totals. Include a paid order with several products and an order changed after purchase. Shopify’s order CSV places additional line items on separate rows, so row count isn’t order count.
Keep an order table with one row per store and order identifier. Keep line items in a separate table. Use the exported ID where available and preserve it as text. Product names and customer email addresses are unsuitable join identifiers because they can change or repeat.
Use these fictional cases as a reconciliation worksheet. The amounts illustrate checks, not observed store results.
Sample records to reconcile
| Fictional case | Expected reporting treatment | Evidence to inspect |
|---|---|---|
| Order A has $100 of goods, $10 shipping and $11 tax | Goods, shipping and tax remain separate; the total collected is $121 | Order details and exported amounts |
| Order A later refunds $40 of goods and $4 tax, keeping shipping | Retained goods are $60; retained tax is $7; remaining collected value is $77 | Refund details and updated order record |
| Order B is cancelled before any payment capture | Keep the cancellation status; captured payments remain zero | Order timeline and transaction history |
| Order C has a payment-processing fee | Record the fee from the payment or payout source, separately from merchandise sales | Payout transaction and fee fields |
| Order D is paid in USD while the store reports in AUD | Preserve the source currency with each amount; compare like currencies | Currency fields and the corresponding Shopify transaction |
The order CSV has a refunded-amount field, but that alone doesn’t explain each refund’s product, tax or timing. Use the order’s refund details or sale-and-return records for those measures. A cancelled order can also have captured funds, so cancellation status alone cannot determine cash received.
For Shopify Payments fees, use the payout transaction export. Its transaction-level detail is a separate source from order sales. A gateway outside Shopify Payments needs its own settlement evidence.
Preserve timestamp offsets during import and choose one reporting time zone before deriving reporting days. Match that zone and the event type to the Shopify report you reconcile. Keep guest customers distinct from identified customers. A blank customer identifier must not become one shared customer.
Build and check the reporting model
Define measures at their intended row level, then compare them with the same Shopify records and period. Joining two line items to two payment transactions can create four rows and double both totals. Aggregate each child table to the required level before combining totals.
Use a one-to-many relationship from orders to line items. Count distinct order identifiers for order count. Define paid-order count using payment status or captured-payment evidence, with an explicit rule for partially paid orders. Keep refunds tied to their original orders without treating every refund as a new order.
Shopify’s finance report definitions distinguish gross sales from net sales. Gross sales exclude discounts, sales reversals, taxes, shipping and fees. Net sales subtract discounts and sales reversals from gross sales. Total sales also includes the applicable tax, shipping charges and fees.
Match each refund component separately. Refunded shipping and its tax belong in the shipping and tax measures. They don’t belong in merchandise sales reversals. Keep processing fees separate from fees charged to customers.
An order snapshot can answer how much an order currently retains after refunds. A period sales report can place the sale and later reversal in different periods. Name the measure accordingly. Don’t describe a current order snapshot as a full event history.
Reconcile a closed seven-day sample and every fictional-case equivalent your store actually has. Compare distinct orders and each amount separately. Record any difference beside its cause before widening the reporting period.
Find the cause of a mismatch
| Symptom | First check | Correction to make |
|---|---|---|
| An entire order is absent | Export filter, covered period and latest extract | Repair extraction before changing a measure |
| Totals multiply after a join | Row counts on each side of the relationship | Remove the many-to-many multiplication |
| A refund appears in another reporting period | Refund event time versus original order time | Use the event definition of the comparison report |
| Campaign revenue differs from order revenue | Attribution window and channel assignment | Compare the same attribution definition |
| Bank deposits differ from total sales | Payout timing, fees and adjustments | Reconcile settlement records separately |
Keep currency as a grouping field. Add a converted total only when the model records the exchange-rate source and conversion rule. Adding AUD $100 to USD $100 as a single $200 measure gives no meaningful retail total.
Maintain and recover refreshes
Assign one reporting owner to extraction and one to Power BI refresh, even if the same person holds both roles. Monitor the newest source event alongside the latest successful model refresh. A successful refresh of yesterday’s unchanged CSV still leaves today’s orders missing.
For the file route, keep an export covering the agreed period at the documented source location. Replace that snapshot after validation instead of appending overlapping exports. A local file needs a configured, running gateway when the Power BI service cannot reach it directly. Test that access using the service account and the published model.
For an API pipeline, follow Shopify’s cursor pagination through every page. A request supports up to 250 resources, so the first response isn’t the full order history. Page nested connections as well when their records are needed. Confirm the oldest extracted order meets the requested coverage and the app’s granted history access.
Microsoft’s incremental refresh requirements use RangeStart and RangeEnd date/time parameters. Incremental refresh is supported on Pro as well as the documented higher-capacity plans. The source must receive the period filter or otherwise support bounded extraction. A setting in Power BI doesn’t make a Shopify export incremental.
Allow for later changes to older orders. Refreshing only newly created orders misses refunds and edits. A custom pipeline needs an update strategy and a deliberate historical recheck window. Merge changed records by stable store and record identifiers, then advance the extraction checkpoint only after the complete batch is saved.
Use this recovery sequence after a failed or stale update:
- Identify whether extraction failed or Power BI failed to read an otherwise complete source.
- Restore the failed dependency, such as file access, gateway availability or an expired API credential. Reconfirm the granted scopes if order access changed.
- For a file snapshot, replace the affected period as a complete snapshot. For a pipeline, replay from the last saved checkpoint and merge existing identifiers.
- Rerun the bounded reconciliation sample. Confirm the newest expected order and a changed older order appear once, with their correct amounts.
Resume the schedule after those records reconcile. Keep the failed batch and its recovery record available to the reporting owner so a later discrepancy can be traced to the source or model.
Give your store its own shopping agent
Customers ask in their own words. Your agent finds, compares and checks out from your catalogue, right on your site.