Panoramica della valutazione delle query e riduzione delle query in Power Query

Questo articolo offre una panoramica di base del modo in cui le query M vengono elaborate e trasformate in richieste di origine dati.

Script M di Power Query

Qualsiasi query, creata da Power Query, scritta manualmente dall'utente nell'editor avanzato o immessa usando un documento vuoto, è costituita da funzioni e sintassi dal linguaggio delle formule M di Power Query. Questa query viene interpretata e valutata dal motore di Power Query per restituire i risultati. Lo script M funge da set di istruzioni necessarie per valutare la query.

Suggerimento

È possibile considerare lo script M come una ricetta che descrive come preparare i dati.

Il modo più comune per creare uno script M consiste nell'usare l'editor di Power Query. Ad esempio, quando ci si connette a un'origine dati, ad esempio un database di SQL Server, si noterà che sul lato destro della schermata è presente una sezione denominata passaggi applicati. In questa sezione vengono visualizzati tutti i passaggi o le trasformazioni usate nella query. In questo senso, l'editor di Power Query funge da interfaccia per creare lo script M appropriato per le trasformazioni successive e garantisce che il codice usato sia valido.

Nota

Lo script M viene usato nell'editor di Power Query per:

• Visualizzare la query come una serie di passaggi e consentire la creazione o la modifica di nuovi passaggi.
• Visualizzare una visualizzazione diagramma.

L'immagine precedente evidenzia la sezione passaggi applicati, che contiene i passaggi seguenti:

• Origine: stabilisce la connessione all'origine dati. In questo caso, si tratta di una connessione a un database di SQL Server.
• Navigazione: consente di passare a una tabella specifica nel database.
• Rimosse altre colonne: consente di selezionare le colonne della tabella da mantenere.
• Righe ordinate: ordina la tabella usando una o più colonne.
• Righe principali mantenute: filtra la tabella in modo da mantenere solo un determinato numero di righe dalla parte superiore della tabella.

Questo set di nomi di passaggi è un modo semplice per visualizzare lo script M creato automaticamente da Power Query. Esistono diversi modi per visualizzare lo script M completo. In Power Query è possibile selezionare Editor avanzato nella scheda Visualizza. È anche possibile selezionare Editor avanzato dal gruppo Query nella scheda Home. In alcune versioni di Power Query è anche possibile modificare la visualizzazione della barra della formula per visualizzare lo script di query accedendo alla scheda Visualizza e dal gruppo Layout selezionare Script di visualizzazione>Script Script.

La maggior parte dei nomi presenti nel riquadro Passaggi applicati viene usata anche come nello script M. I passaggi di una query vengono denominati usando un nome denominato identificatori nel linguaggio M. In alcuni casi i caratteri aggiuntivi vengono racchiusi tra i nomi dei passaggi in M, ma questi caratteri non vengono visualizzati nei passaggi applicati. Un esempio è #"Kept top rows", che viene categorizzato come identificatore tra virgolette a causa di questi caratteri aggiuntivi. È possibile usare un identificatore tra virgolette per consentire l'uso di qualsiasi sequenza di zero o più caratteri Unicode come identificatore, inclusi parole chiave, spazi vuoti, commenti, operatori e segni di punteggiatura. Per altre informazioni sugli identificatori nel linguaggio M, vedere Struttura lessicale.

Tutte le modifiche apportate alla query tramite l'editor di Power Query aggiorneranno automaticamente lo script M per la query. Ad esempio, usando l'immagine precedente come punto di partenza, se si modifica il nome del passaggio Mantieni le prime righe come prime 20 righe, questa modifica verrà aggiornata automaticamente nella visualizzazione script.

Anche se è consigliabile usare l'editor di Power Query per creare automaticamente tutti o la maggior parte dello script M, è possibile aggiungere o modificare manualmente parti dello script M. Per altre informazioni sulla lingua M, visitare il sito ufficiale della documentazione per la lingua M.

Nota

Lo script M, detto anche codice M, è un termine usato per qualsiasi codice che usa il linguaggio M. Nel contesto di questo articolo, lo script M fa riferimento anche al codice trovato all'interno di una query di Power Query e accessibile tramite la finestra dell'editor avanzato o tramite la visualizzazione script nella barra della formula.

Valutazione delle query in Power Query

Il diagramma seguente illustra il processo che si verifica quando una query viene valutata in Power Query.

1. Lo script M, disponibile nell'editor avanzato, viene inviato al motore di Power Query. Sono incluse anche altre informazioni importanti, ad esempio credenziali e livelli di privacy dell'origine dati.
2. Power Query determina quali dati devono essere estratti dall'origine dati e invia una richiesta all'origine dati.
3. L'origine dati risponde alla richiesta da Power Query trasferendo i dati richiesti a Power Query.
4. Power Query riceve i dati in ingresso dall'origine dati ed esegue qualsiasi trasformazione usando il motore di Power Query, se necessario.
5. I risultati derivati dal punto precedente vengono caricati in una destinazione.

Nota

Anche se in questo esempio viene mostrata una query con un database SQL come origine dati, il concetto si applica alle query con o senza un'origine dati.

Quando Power Query legge lo script M, esegue lo script tramite un processo di ottimizzazione per valutare in modo più efficiente la query. In questo processo determina quali passaggi (trasformazioni) della query possono essere scaricati nell'origine dati. Determina anche quali altri passaggi devono essere valutati usando il motore di Power Query. Questo processo di ottimizzazione è denominato riduzione delle query, in cui Power Query tenta di eseguire il push della maggior parte delle possibili esecuzioni nell'origine dati per ottimizzare l'esecuzione della query.

Importante

Vengono seguite tutte le regole del linguaggio di formula M di Power Query (noto anche come linguaggio M). In particolare, la valutazione differita svolge un ruolo importante durante il processo di ottimizzazione. In questo processo Power Query riconosce le trasformazioni specifiche della query da valutare. Power Query comprende anche quali altre trasformazioni non devono essere valutate perché non sono necessarie nell'output della query.

Inoltre, quando sono coinvolte più origini, il livello di privacy dei dati di ogni origine dati viene preso in considerazione durante la valutazione della query. Altre informazioni: Dietro le quinte del Firewall per la privacy dei dati

Il diagramma seguente illustra i passaggi da eseguire in questo processo di ottimizzazione.

1. Lo script M, disponibile nell'editor avanzato, viene inviato al motore di Power Query. Vengono fornite anche altre informazioni importanti, ad esempio le credenziali e i livelli di privacy dell'origine dati.
2. Il meccanismo di riduzione query invia richieste di metadati all'origine dati per determinare le funzionalità dell'origine dati, degli schemi di tabella, delle relazioni tra tabelle diverse nell'origine dati e altro ancora.
3. In base ai metadati ricevuti, il meccanismo di riduzione delle query determina le informazioni da estrarre dall'origine dati e il set di trasformazioni da eseguire all'interno del motore di Power Query. Invia le istruzioni a due altri componenti che si occupano di recuperare i dati dall'origine dati e di trasformare i dati in ingresso nel motore di Power Query, se necessario.
4. Dopo aver ricevuto le istruzioni dai componenti interni di Power Query, Power Query invia una richiesta all'origine dati usando una query sull'origine dati.
5. L'origine dati riceve la richiesta da Power Query e trasferisce i dati al motore di Power Query.
6. Una volta che i dati si trova all'interno di Power Query, il motore di trasformazione all'interno di Power Query (noto anche come motore mashup) esegue le trasformazioni che non possono essere ripiegate o scaricate nell'origine dati.
7. I risultati derivati dal punto precedente vengono caricati in una destinazione.

Nota

A seconda delle trasformazioni e dell'origine dati usata nello script M, Power Query determina se eseguirà lo streaming o il buffer dei dati in ingresso.

Panoramica della riduzione delle query

L'obiettivo della riduzione delle query consiste nell'eseguire l'offload o il push della valutazione di una query in un'origine dati in grado di calcolare le trasformazioni della query.

Il meccanismo di riduzione delle query consente di raggiungere questo obiettivo convertendo lo script M in un linguaggio che può essere interpretato ed eseguito dall'origine dati. Esegue quindi il push della valutazione nell'origine dati e invia il risultato di tale valutazione a Power Query.

Questa operazione offre spesso un'esecuzione di query molto più veloce rispetto all'estrazione di tutti i dati necessari dall'origine dati e all'esecuzione di tutte le trasformazioni necessarie nel motore di Power Query.

Quando si usa l'esperienza di recupero dei dati, Power Query guida l'utente attraverso il processo che in ultima analisi consente di connettersi all'origine dati. In questo caso, Power Query usa una serie di funzioni nel linguaggio M categorizzate come accesso alle funzioni dati. Queste funzioni specifiche usano meccanismi e protocolli per connettersi all'origine dati usando un linguaggio comprensibile per l'origine dati.

Tuttavia, i passaggi che seguono nella query sono i passaggi o le trasformazioni che il meccanismo di riduzione delle query tenta di ottimizzare. Controlla quindi se possono essere scaricati nell'origine dati invece di essere elaborati usando il motore di Power Query.

Importante

Tutte le funzioni dell'origine dati, comunemente visualizzate come passaggio Origine di una query, eseguono query sui dati nell'origine dati nel linguaggio nativo. Il meccanismo di riduzione delle query viene usato in tutte le trasformazioni applicate alla query dopo la funzione dell'origine dati in modo che possano essere tradotte e combinate in una singola query di origine dati o in quante trasformazioni che possono essere scaricate nell'origine dati.

A seconda della struttura della query, potrebbero esserci tre possibili risultati per il meccanismo di riduzione delle query:

• Riduzione completa delle query: quando tutte le trasformazioni di query vengono reintrodute nell'origine dati e si verifica un'elaborazione minima nel motore di Power Query.
• Riduzione parziale delle query: quando nella query sono presenti solo alcune trasformazioni e non tutte, è possibile eseguire il push nell'origine dati. In questo caso, solo un subset delle trasformazioni viene eseguito nell'origine dati e le altre trasformazioni di query si verificano nel motore di Power Query.
• Nessuna riduzione delle query: quando la query contiene trasformazioni che non possono essere convertite nel linguaggio di query nativo dell'origine dati, perché le trasformazioni non sono supportate o il connettore non supporta la riduzione delle query. Per questo caso, Power Query ottiene i dati non elaborati dall'origine dati e usa il motore di Power Query per ottenere l'output desiderato elaborando le trasformazioni necessarie a livello del motore di Power Query.

Nota

Il meccanismo di riduzione delle query è disponibile principalmente nei connettori per origini dati strutturate, ad esempio, microsoft SQL Server e feed OData. Durante la fase di ottimizzazione, il motore potrebbe talvolta riordinare i passaggi nella query.

Sfruttando un'origine dati con più risorse di elaborazione e con funzionalità di riduzione delle query, è possibile accelerare i tempi di caricamento delle query man mano che l'elaborazione avviene nell'origine dati e non nel motore di Power Query.

Passaggi successivi

Per esempi dettagliati dei tre possibili risultati del meccanismo di riduzione delle query, vedere Esempi di riduzione delle query.

Per informazioni sugli indicatori di riduzione delle query disponibili nel riquadro Passaggi applicati, vedere Indicatori di riduzione delle query