Pick files with a Power BI or SQL query

Under development

The Source Type Dynamic BI data query can be chosen in the job dialog, and its fields are shown so you can see what is coming. They cannot be filled in yet, and a job with this source cannot run: it is never validated, so it never reaches Ready. This topic describes the feature as it is being built.

What a query on the BI data is for

Discovery records every file it finds in the reporting database SMART Migration keeps for the Power BI report: its name, path, size, dates, version count and metadata columns. A query on that data picks files by what they are, not by where they are:

Report on exactly those files

Run the query as a Discovery job: the Power BI report then shows just the files that match, across every source.

Migrate them again

Run it as a Migration job to bring exactly the matching files over once more — a second pass, a correction, a business owner's request — without a job per library.

A DORA copy of the critical files

In the few libraries a critical function depends on, mark the critical documents with a metadata column and keep a copy of exactly those in one place, refreshed every night — to reach in an emergency.

The query is the selection: the job has no library, share or OneDrive of its own, and each file is read from wherever it is.

A copy that follows the documents, for DORA

A query is only as current as the BI data it reads. Keep that data current with an incremental Discovery of the libraries a critical function depends on, and a query job replicates their critical documents dynamically: whatever matches tonight is copied tonight, wherever it is in those libraries, without anyone editing a job or exporting a list.

Discover the protected libraries, incrementally
Give each library in the protected scope — the few that hold what critical functions need, never the whole tenant — a recurring Discovery job, daily, with Incremental (only files changed since the last successful run) on. After the first full scan, each run reads only what was created or modified since, so the BI data follows the documents every night at a fraction of the cost: new files, new versions, changed metadata.
Say what is critical
Mark the critical documents in a way the BI data holds: a metadata column such as Critical, a content type, a site, a version count — whatever the business already uses.
Query the critical files
Create a recurring Migration job with Source Type Dynamic BI data query and a query that picks those files, into the one place the copy belongs. Schedule it after the Discovery jobs — Discovery at 01:00, the copy at 03:00 — so it reads the data refreshed that night.
The copy keeps itself up to date
Each run takes the files that match now. A document marked critical today is copied tonight; a critical document edited today is copied again; a library added to the protected scope is reached as soon as its Discovery has run.

What happens at the source	What the next runs do
A document is newly marked critical	Discovery records the change, the query picks the file, and the copy takes it
A critical document is edited	Discovery records the new version, and the copy takes it again under the rule Add New Overwrite Existing By Newer
A document is no longer marked critical	The query no longer picks it; the copy made earlier is left as it is, as a migration never deletes at the destination
A library is added to the protected scope	Give it a recurring Discovery job, and the query reaches it from the next night

Dynamic replication for DORA

The copy is defined by what the documents are, not by where they are: wherever a critical document is created, moved or changed, the next night's runs find it and copy it. Discovery reads only what changed, so the whole cycle stays short enough to repeat every night, and Run History records every refresh — which files it took and how it ended — the evidence an auditor asks for.

A DORA copy of selected libraries, not a backup

Microsoft 365 throttles heavy and frequent reads, so no recurring job can keep a whole tenant in sync. Keep the recurring scope — the Discovery jobs and the copy together — to fewer than 20 libraries of under 50,000 files each as a rule of thumb, and never more than about 50 libraries in all. This setup is a resilience copy of selected libraries, to reach in an emergency. It is not a backup of SharePoint Online, SharePoint Server or file shares to Nextcloud: backing up a whole tenant or file server is a job for a backup product built for it, and the content outside the protected scope is migrated once or left where it is.

A full Discovery now and then

An incremental run takes what was added or modified since the last one. To bring the BI data fully in line with the sources — files deleted or moved there included — reset the Discovery jobs' From Date now and then, monthly for example, so the next run is a full scan.

For a report or a single migration, a query can reach every source a one-off Discovery has recorded, however many: that is one pass, not a copy kept in sync. See Choose which files a job takes

Two ways to write the query

Query language	How you write it	Best for
DAX query from Power BI	Filter a visual in the Power BI report to the files you want, record its query with Performance Analyzer, and let Convert to SQL turn it into SQL	Criteria you already click together in Power BI: slicers, filters, measures
SQL WHERE clause	Write the conditions yourself, as the WHERE clause of a select on the BI data — `version_count > 100 AND my_column = 'abc'`	Criteria the report does not show, or conditions across several columns

How SMART Migration runs the query

The job hands SMART Migration conditions, not a whole query. When it claims the job, SMART Migration receives the WHERE clause and adds it to its own select of the files on the BI data, evaluated afresh at every run.

Only files can come back. The select, its tables and what it returns are SMART Migration's; the clause only narrows it, so a query can never change data or read anything else.
One language at run time. A DAX query recorded in Power BI is converted into the same WHERE clause before the job is saved, so SMART Migration always reads SQL conditions.
Always current. The clause is applied to the BI data as it is when the job runs, never to a snapshot taken when the job was saved.

Record a DAX query in Power BI Desktop

Power BI writes a DAX query for every visual it draws, and Performance Analyzer shows it.

Filter a table of files
Open SMART BI.pbix in Power BI Desktop and filter a table of files to exactly the ones you want — for example VersionCount greater than 100, or a metadata column equal to abc.
Start Performance Analyzer
On the Optimize ribbon (or View in older versions) open Performance Analyzer, click Start recording, then Refresh visuals.
Copy the visual's query
Expand the table's entry in the Performance Analyzer pane and click Copy query.
Paste it into the job
In the job dialog, choose Dynamic BI data query as Source Type, DAX query from Power BI as Query language, paste the query into DAX query and click Convert to SQL.

Convert to SQL with Claude

Convert to SQL will send the DAX query to Claude, with the app's AI Help key, and put the WHERE clause it writes in the field for you to read and check before you save the job.

Only the query is sent — no job, run, file or customer data, as with AI Help.
You keep the last word: the clause is shown in the field, and the job runs what is saved there, not what Claude answered.
Without AI Help you write the WHERE clause yourself.

Examples of a query on the BI data

You want	The query picks
Files with a long version history	Files with a version count over 100
The documents of one project	Files whose metadata column holds the project's code
Large files nobody has opened for years	Files over a size, last modified before a date
The critical documents for a DORA copy	Files marked critical in a metadata column, in the protected libraries

A query or a list of files

A File List CSV job also cherry-picks files from many sources, but from a fixed list: exported once from Power BI, and limited to 30,000 rows per export. A query is evaluated each time the job runs, so a recurring job always takes the files that match now — new matches included — with no export in between and no row limit.

Keep the BI data complete

A query can only find what Discovery has recorded. Run Discovery on every source the query should reach, and keep the Run History rows: deleting them takes their data out of the BI data the query reads.

Important: the Dashboard's figures change only once SMART Migration has purged the runs. Refresh the report when the Power BI heading on the Dashboard shows a green Up to date instead of a red mark. See Run History

See also