Potresti avere abbastanza familiarità con le query con parametri con il loro uso in SQL o Microsoft Query. Tuttavia, i parametri di Power Query presentano differenze fondamentali:
- I parametri possono essere usati in qualsiasi passaggio della query. Oltre a funzionare come filtro dati, i parametri possono essere usati per specificare elementi come il percorso di un file o il nome di un server.
- I parametri non richiedono input. È invece possibile modificarne rapidamente il valore usando Power Query. È anche possibile archiviare e recuperare i valori dalle celle di Excel.
- I parametri vengono salvati in una query con parametri semplice, ma sono separati dalle query di dati in cui vengono usati. Dopo la creazione, è possibile aggiungere un parametro alle query, in base alle esigenze.
Avvertenza Se si vuole l'altro modo per creare query con parametri, vedere Creare una query con parametri in Microsoft Query.
Creare un parametro
È possibile usare un parametro per modificare automaticamente un valore in una query ed evitare di modificare ogni volta che il valore viene modificato. È sufficiente modificare il valore del parametro. Una volta creato un parametro, questo viene salvato in una query speciale che è possibile modificare comodamente direttamente da Excel.
Selezionare Dati>Recuperare dati>Altre origini>Avviare l'editor di Power Query.
Nell'editor di Power Query selezionare Home>Gestisci parametri > Nuovi parametri.
Nella finestra di dialogo Gestisci parametro (Manage Parameter ), selezionate Nuovo (New).
Impostare quanto segue in base alle esigenze:
Nome Questo dovrebbe riflettere la funzione del parametro, ma mantienila il più breve possibile. Descrizione Questo può contenere qualsiasi dettaglio che aiuterà le persone a usare correttamente il parametro. Obbligatorio Eseguire una delle operazioni seguenti:
Qualsiasi valore È possibile immettere qualsiasi valore di qualsiasi tipo di dati nella query con parametri.
Elenco dei valori È possibile limitare i valori a un elenco specifico immettendoli nella piccola griglia. È inoltre necessario selezionare un valore predefinito e un valore corrente di seguito.
Query Selezionare una query di elenco simile a una colonna strutturata Elenco separata da virgole e racchiusa tra parentesi graffe.
Ad esempio, un campo Stato problemi può avere tre valori: {"New", "Ongoing", "Closed"}. È necessario creare la query di elenco in anticipo aprendo l'Editor avanzato (selezionare HomeEditor>avanzato), rimuovendo il modello di codice, immettendo l'elenco di valori nel formato dell'elenco di query e quindi selezionando Fine.
Al termine della creazione del parametro, la query di elenco viene visualizzata nei valori dei parametri.Tipo Specifica il tipo di dati del parametro. Valori suggeriti Se necessario, aggiungere un elenco di valori o specificare una query per fornire suggerimenti per l'input. Valore predefinito Viene visualizzata solo se Valori suggeriti è impostato su Elenco di valori e specifica quale voce di elenco è l'impostazione predefinita. In questo caso, è necessario scegliere un'impostazione predefinita. Valore corrente A seconda della posizione in cui si usa il parametro, se è vuoto la query potrebbe non restituire alcun risultato. Se è selezionata l'opzione Obbligatorio , il valore corrente non può essere vuoto. Per creare il parametro, selezionare OK.
Utilizzare un parametro per modificare un'origine dati
Ecco un modo per gestire le modifiche apportate ai percorsi delle origini dati ed evitare errori di aggiornamento. Ad esempio, supponendo che uno schema e un'origine dati siano simili, creare un parametro per modificare facilmente un'origine dati ed evitare errori di aggiornamento dei dati. A volte il server, il database, la cartella, il nome del file o il percorso cambiano. Forse un gestore di database occasionalmente sostituisce un server, un drop mensile di file CSV va in una cartella diversa o è necessario passare facilmente da un ambiente di sviluppo/test/produzione.
Passaggio 1: Creare una query con parametri
Nell'esempio seguente, sono disponibili diversi file CSV importati usando l'operazione di importazione della cartella (Selezione di Dati>recuperati>dati da FilesFrom>Folder) dalla cartella C:\DataFilesCSV1. A volte, tuttavia, viene occasionalmente usata una cartella diversa come posizione in cui eliminare i file, C:\DataFilesCSV2. È possibile usare un parametro in una query come valore sostitutivo per la cartella diversa.
Selezionare Home>Gestisci parametri>Nuovo parametro.
Nella finestra di dialogo Gestisci parametro (Manage Parameter ) immettete le seguenti informazioni:
Nome CSVFileDrop Descrizione Percorso di rilascio file alternativo Obbligatorio Sì Tipo Text Valori suggeriti Qualsiasi valore Valore corrente C:\DataFilesCSV1 Selezionare OK.
Passaggio 2: Aggiungere il parametro alla query di dati
- Per impostare il nome della cartella come parametro, in Impostazioni query, in Passaggi query, selezionare Origine, quindi selezionare Modifica impostazioni.
- Verificare che l'opzione Percorso file sia impostata su Parametro, quindi selezionare il parametro appena creato dall'elenco a discesa.
- Selezionare OK.
Passaggio 3: Aggiornare il valore del parametro
Il percorso della cartella è stato appena modificato, quindi ora è possibile aggiornare semplicemente la query con parametri.
- Selezionare Connessioni dati>& scheda Query>, fare clic con il pulsante destro del mouse sulla query con parametri e quindi scegliere Modifica.
- Immettere la nuova posizione nella casella Valore corrente , ad esempio C:\DataFilesCSV2.
- Seleziona Home,>chiudi & carica.
- Per confermare i risultati, aggiungere nuovi dati all'origine dati e quindi aggiornare la query di dati con il parametro aggiornato (Seleziona Aggiorna tutti i dati>).
Utilizzare un parametro per filtrare i dati
A volte è possibile modificare in modo semplice il filtro di una query in modo da ottenere risultati diversi senza modificare la query o creare copie leggermente diverse della stessa query. In questo esempio viene modificata una data per cambiare comodamente un filtro dati.
Per aprire una query, individuane una che è stata precedentemente caricata dall'editor di Power Query, seleziona una cella nei dati e quindi seleziona Modifica query>. Per altre informazioni leggi Creare, caricare o modificare una query in Excel.
Selezionare la freccia del filtro in qualsiasi intestazione di colonna per filtrare i dati e quindi selezionare un comando di filtro, ad esempio Filtri >data/oradopo. Viene visualizzata la finestra di dialogo Filtra righe .
Selezionare il pulsante a sinistra della casella Valore e quindi eseguire una delle operazioni seguenti:
- Per usare un parametro esistente, selezionare Parametro, quindi selezionare il parametro desiderato dall'elenco visualizzato a destra.
- Per utilizzare un nuovo parametro, selezionare Nuovo parametro, quindi creare un parametro.
Immettere la nuova data nella casella Valore corrente e quindi selezionare Chiudi &>Carica.
Per confermare i risultati, aggiungere nuovi dati all'origine dati e quindi aggiornare la query di dati con il parametro aggiornato (Seleziona Aggiorna tutti i dati>). Ad esempio, modificare il valore del filtro impostando una data diversa per visualizzare i nuovi risultati.
Immettere la nuova data nella casella Valore corrente .
Seleziona Home,>chiudi & carica.
Per confermare i risultati, aggiungere nuovi dati all'origine dati e quindi aggiornare la query di dati con il parametro aggiornato (Seleziona Aggiorna tutti i dati>).
Usare un valore di cella per filtrare i dati
In questo esempio, il valore del parametro di query viene letto da una cella della cartella di lavoro. Non è necessario modificare la query con parametri, è sufficiente aggiornare il valore della cella. Ad esempio, si vuole filtrare una colonna in base alla prima lettera, ma cambiare facilmente il valore in qualsiasi lettera dalla A alla Z.
Nel foglio di lavoro di una cartella di lavoro in cui è caricata la query che si vuole filtrare creare una tabella di Excel con due celle, un'intestazione e un valore.
MyFilter G Selezionare una cella nella tabella di Excel, quindi selezionare Recupera>dati>da tabella/intervallo. Viene visualizzato l'editor di Power Query.
Nella casella Nome del riquadro Impostazioni query a destra modificare il nome della query in modo che sia più significativo, ad esempio FilterCellValue.
Per passare il valore nella tabella e non la tabella stessa, fare clic con il pulsante destro del mouse sul valore in Anteprima dati e quindi scegliere Drill-down.
Si noti che la formula è stata modificata in= #"Changed Type"{0}[MyFilter]
Quando si usa la tabella di Excel come filtro nel passaggio 10, Power Query fa riferimento al valore della tabella come condizione di filtro. Un riferimento diretto alla tabella di Excel causerebbe un errore.Seleziona Home,>Chiudi & Carica, Chiudi>& Carica a. È ora disponibile un parametro di query denominato "FilterCellValue" che viene usato nel passaggio 12.
Nella finestra di dialogo Importa dati , selezionare Solo Crea connessione, quindi selezionare OK.
Aprire la query da filtrare con il valore nella tabella FilterCellValue, una query precedentemente caricata dall'editor di Power Query, selezionando una cella nei dati e quindi selezionando Modifica query>. Per altre informazioni leggi Creare, caricare o modificare una query in Excel.
Selezionare la freccia del filtro in qualsiasi intestazione di colonna per filtrare i dati e quindi selezionare un comando di filtro, ad esempioIniziano ifiltri> testo. Viene visualizzata la finestra di dialogo Filtra righe .
Immettere un valore qualsiasi nella casella Valore , ad esempio "G", quindi selezionare OK. In questo caso, il valore è un segnaposto temporaneo per il valore nella tabella FilterCellValue che verrà immesso nel passaggio successivo.
Selezionare la freccia sul lato destro della barra della formula per visualizzare l'intera formula. Ecco un esempio di condizione di filtro in una formula:
= Table.SelectRows(#"Changed Type", each Text.StartsWith([Name], "G"))
Selezionare il valore del filtro. Nella formula selezionare "G".
Con M Intellisense, immettere le prime lettere della tabella FilterCellValue creata, quindi selezionarla nell'elenco visualizzato.
Seleziona Home,>Chiudi, Chiudi>& Carica.
Risultato
La query ora usa il valore della tabella di Excel creata per filtrare i risultati della query. Per usare un nuovo valore, modificare il contenuto della cella nella tabella Excel originale nel passaggio 1, cambiare "G" in "V" e quindi aggiornare la query.
Controlla l'uso di query con parametri
È possibile controllare se le query con parametri sono consentite o meno.
- Nell'editor di Power Query, selezionareOpzioni e impostazioni>file> Opzioniquery,Editor>di Power Query.
- Nel riquadro a sinistra, in GLOBALE, selezionare Editor di Power Query.
- Nel riquadro a destra, in Parametri, selezionare o deselezionare Consenti sempre la parametrizzazione nelle finestre di dialogo origine dati e trasformazione.