Procedure consigliate per il flusso del linguaggio Power Query

In questa esercitazione imparerai a conoscere il flusso del linguaggio di Power Query e alcune delle relative procedure consigliate. Imparerai anche come trasformare facilmente i tuoi dati per ottenere i migliori risultati possibili. Le trasformazioni dei dati ottimizzeranno il tuo report e lo renderanno accattivante.

Sommario

• Impostare i tipi di dati senza flusso del linguaggio Power Query
  • Imposta i tipi di dati utilizzando la scheda Trasforma
  • Impostare i tipi di dati utilizzando l'intestazione di colonna
  • Impostare i tipi di dati utilizzando le icone delle colonne
• Impostare i tipi di dati con il flusso del linguaggio Power Query
  • Rimuovi le colonne non necessarie
  • Assegna i tipi di dati appropriati alle colonne e limita le righe
  • Consolida i passaggi ridondanti nel flusso del linguaggio Power Query
• Organizza le query per un migliore flusso del linguaggio Power Query
• Riepilogo del flusso del linguaggio Power Query
• Conclusione

Impostare i tipi di dati senza flusso del linguaggio Power Query

Le trasformazioni minime che dovresti sempre eseguire sono la ridenominazione e l'impostazione dei tipi di dati per le tue colonne.

I tipi di dati vengono utilizzati per classificare i valori in M ​​. Un'icona accanto al nome della colonna mostra il tipo di dati assegnato per un campo. In questa tabella di esempio, puoi vedere un'icona ABC123 accanto ai nomi delle colonne. Tale icona rappresenta il tipo di dati Qualsiasi che indica che un tipo di dati non è stato assegnato a quella colonna. 

Fuori dai libri, il rilevamento automatico del tipo è abilitato. Ciò genera automaticamente passaggi di tipo modificato nelle query. Anche se è abilitato, assicurati sempre che Power Query indovini correttamente i tipi di dati per ciascuna colonna in tutte le tabelle.

Per abilitare o disabilitare tale impostazione, attenersi alla seguente procedura.

Sotto l' opzione Globale , puoi selezionare una delle tre opzioni di rilevamento del tipo disponibili.

Sotto l'opzione File corrente, puoi attivare o disattivare le opzioni per il rilevamento del tipo a seconda delle impostazioni globali.

Imposta i tipi di dati utilizzando la scheda Trasforma

L'interfaccia utente offre diversi modi per impostare i tipi di dati per le tue colonne. Nella sezione Qualsiasi colonna della scheda Trasforma, troverai il pulsante Rileva tipo di dati .

Se selezioni tutte le colonne e fai clic su quel pulsante, imposterà automaticamente i tipi di dati di tutte le colonne. Tuttavia, poiché analizza solo le prime 200 righe, è comunque necessario verificare se Power Query ha il tipo di dati corretto.

Se selezioni una colonna dalla tua tabella, puoi vedere il suo tipo di dati nella sezione Qualsiasi colonna.

Se fai clic sul pulsante a discesa e selezioni Testo, l'icona sulla colonna selezionata cambierà da ABC123 a solo ABC .

Verrà quindi visualizzato un passaggio Tipo modificato nel riquadro Passaggi applicati.

Nella scheda Home troverai anche l'opzione per impostare il tipo di dati.

Impostare i tipi di dati utilizzando l'intestazione di colonna

Un altro modo è fare clic con il pulsante destro del mouse su un'intestazione e selezionare Cambia tipo . Da lì, puoi scegliere il tipo di dati appropriato per le tue colonne.

Impostare i tipi di dati utilizzando le icone delle colonne

Il modo più comune per impostare i tipi di dati è fare clic sull'icona accanto al nome della colonna. Ti mostrerà tutti i tipi di dati disponibili.

Nella tabella di esempio impostare il tipo di dati della colonna OrderDate su Date .

A seconda del tipo di dati della colonna selezionata, Power Query fornisce un set di trasformazioni e opzioni che si applicano a quel tipo di dati specifico nella scheda Trasforma , nella scheda Aggiungi colonna e nella sezione Filtro avanzato .

Quindi, se selezioni una colonna data e vai alla scheda Trasforma, vedrai le opzioni sotto il pulsante Data.

Tuttavia, se selezioni una colonna di testo, le opzioni sotto il pulsante Data non saranno più disponibili.

Per il filtraggio intelligente, le colonne di testo hanno filtri di testo e le colonne di data hanno filtri di data.

Per la colonna successiva, modifica il nome della colonna in Chiave cliente e il tipo di dati in Numero intero .

Successivamente, modifica il tipo di dati delle colonne Canale, Codice valuta e Codice magazzino in Testo.

Successivamente, modifica il nome dell'Indice della regione di consegna in Chiave della regione di consegna e il relativo tipo di dati in Numero intero . Quindi, fai lo stesso per le colonne Product Description Index e Order Quantity .

Infine, per le colonne Prezzo unitario, Totale riga e Costo unitario, impostare il tipo di dati su Numero decimale fisso .

Impostare i tipi di dati con il flusso del linguaggio Power Query

A causa delle modifiche nelle colonne, nel riquadro Passaggi applicati sono stati creati molti passaggi. Il problema è che, a causa dell'esecuzione ripetuta dello stesso tipo di trasformazioni, la query della tabella di esempio diventa inefficiente. Questo è qualcosa che dovresti evitare.

Per rendere efficiente la tua query, prova a creare un singolo passaggio applicando quella trasformazione specifica a tutte le tue colonne prima di creare un altro passaggio.

Duplica la query della tabella di esempio e rinominala Best Practice . Nel riquadro Passaggi applicati, fai clic con il pulsante destro del mouse sulla prima trasformazione eseguita nella query precedente e seleziona Elimina fino alla fine . Quindi, confermare l'eliminazione del passaggio nella finestra di dialogo Elimina passaggio .

Inoltre, ecco alcune best practice. La prima cosa è creare parametri per contenere la posizione dell'origine dati. Ciò semplifica la risoluzione dei problemi quando il nome di un file è stato modificato.

Per creare parametri, fare clic su Gestisci parametri nella scheda Home e selezionare Nuovo parametro .

Un altro modo consiste nel fare clic con il pulsante destro del mouse sul riquadro delle query e selezionare Nuovo parametro .

Successivamente, verrà visualizzata una finestra di dialogo Gestisci parametri . Denominare il parametro FileLocation e impostare Type su Text. Per i Valori suggeriti, impostalo su Elenco di valori in modo da poter incollare la stringa e modificare o aggiungere più posizioni tra cui passare.

Quindi, vai su Esplora file e seleziona il tuo file. Copia il percorso e incolla i parametri. Una volta fatto, premi OK .

Torna alla query Best Practice e fai clic sul passaggio Origine nel riquadro Passaggi applicati. Quindi, modifica il percorso del file codificato nella barra della formula con FileLocation .

Rimuovi le colonne non necessarie

Rimuovi tutte le colonne non necessarie per risparmiare spazio e migliorare le prestazioni. Porta solo i dati di cui hai bisogno perché aggiungere tabelle e colonne è molto più semplice che rimuoverle.

Il modo più semplice per farlo è tramite il passaggio Scegli colonne nella scheda Home. Se fai clic su quel pulsante, verrà visualizzata una finestra di dialogo che ti consente di scegliere le colonne da conservare. Dovresti progettare e modellare le tue tabelle con uno scopo specifico per adattarsi al meglio all'analisi che intendi eseguire.

Da lì, deseleziona le colonne che non ti servono nella tua tabella. Per questa tabella di esempio, la cardinalità della colonna OrderNumber è alta. È meglio deselezionare quella colonna perché influisce sulle dimensioni del file e sulle prestazioni complessive.

Se non hai bisogno di dati sulla posizione per l'analisi della query di esempio, è anche consigliabile deselezionare Delivery Region Index . Infine, poiché la tabella ha già un prezzo unitario e una quantità, la colonna Totale riga non è necessaria.

Dopo aver deselezionato le colonne, premere OK . Se desideri modificare la selezione della colonna, deseleziona semplicemente l'icona a forma di ingranaggio accanto al nome del passaggio nel riquadro Passaggi applicati.

Assegna i tipi di dati appropriati alle colonne e limita le righe

Successivamente, assegna i tipi di dati per tutte le colonne. Seleziona tutte le colonne e fai clic su Rileva tipo di dati nella scheda Trasforma.

Rileva tipo di dati rileva automaticamente i tipi di dati delle colonne in base all'analisi delle prime 200 righe. Quindi, controlla e assicurati che Power Query imposti i tipi di dati corretti.

È consigliabile limitare il numero di righe. Se il tuo esercizio finanziario inizia il 1° luglio, puoi omettere i dati di giugno o impostare un parametro.

Tuttavia, c'è un problema se vuoi essere in grado di modificare un valore di parametro nel servizio LuckyTemplates . Dopo aver pubblicato il report, i valori dei parametri devono essere di tipo Text o Decimal.

Per far fronte a ciò, crea un filtro sui dati facendo clic sul pulsante a discesa nella colonna OrderDate. Successivamente, fai clic su Filtri data e seleziona Dopo .

Nella finestra di dialogo, modificare il primo parametro in is after or equal to e quindi inserire la data. In questo esempio, la data immessa era 1 luglio 2014.

Dopo aver premuto OK , il filtro verrà applicato alla tabella. Quindi, crea un parametro e chiamalo DatesFrom . Impostare Tipo su Testo e inserire la data nel parametro Valore corrente.

Ora torna alla query Best Practice e sostituisci l'intrinseco #date con Date.From(DatesFrom) .

Facendo clic sul segno di spunta senza la funzione Date.From verrà restituito un errore. Questo perché DatesFrom è un tipo di testo mentre il campo ha un tipo di data. La funzione Date.From converte il testo in data.

Rinomina tutte le colonne che non saranno nascoste nel modello di dati. Il nome deve essere conciso, autodescrittivo e intuitivo. Tieni presente che stai progettando un modello di dati per le persone che utilizzeranno il tuo rapporto.

Consolida i passaggi ridondanti nel flusso del linguaggio Power Query

La prossima cosa da fare è consolidare i passaggi ridondanti (come rinominare, rimuovere e modificare i tipi di dati delle colonne). Inoltre, passaggi come il riordino delle colonne sono cose a cui dovresti prestare attenzione quando cerchi ridondanze.

Le tabelle caricate nel modello di dati non vengono mai visualizzate nel report. Ciò rende irrilevante l'ordine delle colonne.

Un'altra best practice consiste nel rinominare i passaggi nel riquadro Passaggi applicati. I nomi dei passaggi sono autodescrittivi e vengono utilizzati come variabili nel codice M.

I nomi che contengono spazi o caratteri speciali sono scritti utilizzando la notazione tra virgolette. Significa che i nomi sono racchiusi tra virgolette doppie e hanno il cancelletto davanti a loro, il che rende difficile leggere il codice M. È possibile omettere gli spazi o inserire un carattere di sottolineatura tra di essi.

Anche l'aggiunta di ulteriori dettagli sulla documentazione inserendo commenti nella finestra dell'editor avanzato è una procedura consigliata in Power Query. Puoi anche farlo nella descrizione della proprietà del passaggio . Vengono visualizzate come annotazioni di descrizione comando quando si passa con il mouse su un passaggio con un punto esclamativo nel riquadro Passaggi applicati.

Sapere perché hai fatto una certa scelta nello sviluppo iniziale è estremamente utile quando devi rivisitare un file dopo un po' di tempo. Per aggiungere i dettagli della documentazione, fare clic con il pulsante destro del mouse su un passaggio nel riquadro Passaggi applicati e selezionare Proprietà .

Apparirà una finestra di dialogo Proprietà passaggio in cui è possibile scrivere il motivo del filtraggio o della trasformazione.

Organizza le query per un migliore flusso del linguaggio Power Query

Una delle best practice più comuni in Power Query è l'organizzazione delle query . Creare cartelle per parametri, funzioni, query di staging e query che verranno caricate nel modello di dati. In questo esempio, seleziona le query FileLocation e DatesFrom e fai clic con il pulsante destro del mouse su di esse. Quindi, seleziona Sposta in gruppo e fai clic su Nuovo gruppo .

Successivamente, aggiungi un nome per le query selezionate e premi OK .

Dopo aver raggruppato le query, il riquadro delle query avrà questo aspetto.

Per tutte le query di staging, assicurati di disabilitare il caricamento deselezionando Abilita caricamento.

Un'altra cosa da discutere in questo tutorial è il flusso linguistico. Ognuno dei passaggi nel riquadro Passaggi applicati trasforma un valore che puoi vedere quando fai clic su di essi.

I dati iniziali sono arrivati ​​e sono stati avviati nella fase di navigazione e sono state selezionate le colonne. Successivamente, i tipi di dati sono stati modificati ed è stato impostato un intervallo di date. Anche le colonne sono state rinominate.

Tutti i passaggi restituiscono un valore di tipo tabella. Se apri la finestra Editor avanzato, vedrai un'espressione let e una clausola in . Tra di loro, c'è un elenco di passaggi o nomi di variabili con espressioni loro assegnate.

La query restituisce tutto ciò che segue la clausola in che si riferisce al passaggio finale nell'elenco delle variabili. Il motore M seguirà quindi la catena di dipendenza dalla clausola in per eliminare tutto ciò che non è necessario e per riportare le trasformazioni all'origine, se possibile.

Riepilogo del flusso del linguaggio Power Query

Se guardi la formula, puoi vedere il flusso dei passaggi eseguiti nella query. Vedrai anche le funzioni utilizzate dall'interfaccia utente in ogni passaggio.

Il primo passaggio eseguito nella query è stata la selezione della colonna. Quando il passaggio è stato eseguito utilizzando l'interfaccia utente, è stata chiamata la funzione Table.SelectColumns . Come primo parametro, ha preso una tabella che faceva riferimento al nome della variabile del passaggio precedente. Quindi, ha elencato tutti i nomi delle colonne selezionate.

Il secondo passaggio ha trasformato i tipi di colonna chiamando la funzione Table.TransformColumnTypes . Il suo primo parametro chiamato l'output del passaggio precedente. Ha quindi elencato una serie di elenchi di trasformazione.

Il terzo passaggio imposta un filtro dell'intervallo di date utilizzando la funzione Table.SelectRows . Ha preso una query di tipo tabella come primo argomento. In questo esempio, ha fatto riferimento all'output della fase Cambia tipo.

L'ultimo passaggio ha rinominato le colonne utilizzando la funzione Table.RenameColumns . L'output del passaggio precedente è stato utilizzato come primo argomento. Quindi, ha elencato una serie di elenchi di ridenominazione.

Tutte le funzioni applicate tramite l'interfaccia utente iniziano con la parola Table . Tutti hanno preso un valore di tabella come primo parametro e poi hanno trasformato quel valore.

Anche se il codice sembra sequenziale perché ogni passaggio fa riferimento al passaggio precedente, la sequenza non è richiesta. Se sposti un passaggio, la query verrà comunque eseguita perché il motore M segue sempre la catena di dipendenza.

Avere l'interfaccia utente è comodo ma presupporrà sempre che si vogliano trasformare i risultati della trasformazione precedente. Nella maggior parte dei casi, probabilmente sarà vero e nella remota possibilità che non lo sia, dovrai aggiornare manualmente quel valore di riferimento nel codice.

Tipi di dati e connettori di Power Query
Set di dati di LuckyTemplates: tipi e convenzioni di denominazione
Best practice per il caricamento e la trasformazione dei dati

Conclusione

La trasformazione dei dati è necessaria per mantenere i dati raggruppati e organizzati. Rende lo sviluppo dei dati più veloce perché puoi facilmente rintracciare i problemi nel flusso del linguaggio Power Query e modificare le modifiche nel report.

Melissa