Power Query ingestion for unification sources

What it does

Power Query data sources pull data into Customer Insights - Data by copying it. The dataflow runs on a schedule, lands the result as CSV in the Dataverse managed data lake attached to the environment, and unification reads that copy. It's the fallback path for anything you can't attach to directly.

Key facts

Only connectors with a tick in the Customer Insights (Dataflows) column of the Power Query connector reference are supported. The full Power Query connector list is much longer than what works here.
Data lands as CSV in the Dataverse managed lake. That's slower to ingest and slower to unify than the Delta-format attach connectors, and you don't get Delta time travel, so unification reprocesses more than it needs to.
A single dataflow evaluation times out at roughly five hours. Past that the refresh fails with a timeout error and you get nothing from that run.
Refresh limits mirror the Power BI dataflow limits, including a cap on concurrent evaluations across the tenant. Refreshes that collide fail rather than queue politely.
Data source names must start with a letter and contain only letters, numbers and underscores. No spaces, no punctuation.
Per-data-source refresh schedules for Power Query are deprecated. New sources default to Refresh manually and pick up the system refresh schedule instead. An existing source showing "Completed with warnings" usually still has a legacy schedule attached that wants removing.
On-premises sources need the environment associated with a Dataverse environment, then Power Platform dataflows plus an on-premises data gateway. The gateway and the Customer Insights environment must sit in the same Azure region if you want to reuse an existing gateway.
Virtual network data gateways are supported for private network connectivity, in preview.
Renaming a dataflow in the Power Platform admin center breaks the reference from Customer Insights. Don't.

When to use / skip

Use Power Query when the source genuinely can't be attached — a SaaS API, a SharePoint file drop, an on-prem SQL box behind a gateway, a CSV a business unit emails over. It's also the only place in the product where you get real transformation power before unification, so it earns its keep when the source is messy and nobody upstream will clean it.

Skip it for anything already sitting in a lakehouse, Dataverse, or ADLS in Delta or CDM format. Attaching to that data is faster, avoids a second copy, and sidesteps the whole class of "the dataflow timed out again" incidents. The common anti-pattern is a client who built everything on Power Query in 2022 and never revisited it; four years on, they're spending three hours a night copying data they already own in a lake three metres away.

Also skip it for high-volume tables. Anything with tens of millions of rows and wide schemas will flirt with the five-hour ceiling, and the failure mode is total, not partial.

Configuration decisions

Whether this source belongs in Power Query at all, or whether the upstream team can land it in a lake you can attach to instead. Make that call before you build the dataflow, not after.
How to split the work: one dataflow per source system, or ingestion dataflows separated from transformation dataflows. Splitting keeps each evaluation inside the timeout and makes failures easier to isolate.
Which columns survive the query. Trim to what unification and downstream business users actually need — every extra column costs ingestion time and later shows up in the column picker to confuse people.
Whether an on-premises or VNet gateway is needed, who owns it, and whether it's clustered. A single unclustered gateway on someone's VM is a go-live risk.
Where each source sits in the system refresh window, given the concurrency cap. Stagger heavy sources rather than firing everything at once.
Data types set inside the query, particularly dates. Getting these right in Power Query saves arguing with semantic type mapping later.

Gotchas

The five-hour timeout is per dataflow evaluation, not per query. Stuffing ten queries into one dataflow is the fastest way to hit it.
Concurrent evaluation limits are shared. A refresh can fail for reasons that have nothing to do with your source — someone else's dataflow was running.
The Dataverse managed lake copy means you now have two versions of the truth and a lag between them. Anyone querying the source system directly will see different numbers to the profile.
Legacy per-source refresh schedules still exist on older environments and quietly produce "Completed with warnings". People ignore the warning for months.
Gateway version drift causes configuration errors that look like connector faults. Check the gateway version before you debug anything else.
Primary key quality is your problem, not Power Query's. Nulls or duplicates in the column you nominate will fail the unification step further down, well after the ingestion looked fine.

Consultant notes

Ask early in discovery which sources are already in Fabric, Dataverse or ADLS. Every one you can attach instead of copy is an hour off the nightly window and one fewer thing to monitor.
Demo a deliberate failure: let a dataflow time out and show the client where the error surfaces. It sets expectations far better than a happy-path walkthrough.
Push back on doing heavy business transformation in Power Query. It's fine for shaping and typing, but complex logic here is invisible to the client's data team and impossible to test properly.
Before go-live, confirm gateway ownership, gateway clustering and who gets the alert when a refresh fails. This is nearly always unowned.
Document the naming convention for data sources on day one. The no-spaces, letter-first rule means someone will invent an ugly name under pressure and it will outlive the project.

Worth another look if the client moves onto Fabric, or if Microsoft changes the dataflow timeout or the deprecated refresh schedules finally disappear.