Creare una query con parametri (Power Query)

È possibile usare un parametro per modificare automaticamente un valore in una query ed evitare di modificare la query ogni volta per modificare il valore. È sufficiente modificare il valore del parametro. Dopo aver creato un parametro, questo viene salvato in una query con parametri speciale che può essere facilmente modificata direttamente da Excel.

1. Selezionare Dati > Recupera dati > Altre origini > avviare editor di Power Query.

2. Nella editor di Power Query selezionare Home > Gestisci parametri > Nuovi parametri.

3. Nella finestra di dialogo Gestisci parametro selezionare Nuovo.

4. Impostare quanto segue in base alle esigenze:

   Nome	Questo dovrebbe riflettere la funzione del parametro, mantenendola il più breve possibile.
   Descrizione	Questo può contenere tutti i dettagli che aiuteranno gli utenti a usare correttamente il parametro.
   Dati obbligatori	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. È anche necessario selezionare un valore predefinito e un valore corrente di seguito. Query Selezionare una query di elenco, simile a una colonna strutturata List separata da virgole e racchiusa tra parentesi graffe. Ad esempio, un campo di stato Problemi potrebbe avere tre valori: {"Nuovo", "In corso", "Chiuso"}. È necessario creare la query di elenco in anticipo aprendo il Editor avanzato (selezionare Home > Editor avanzato), rimuovere il modello di codice, immettere l'elenco di valori nel formato elenco query e quindi selezionare Fatto. Dopo aver completato la creazione del parametro, la query di elenco viene visualizzata nei valori dei parametri.
   Tipo	Specifica il tipo di dati del parametro.
   Valori suggeriti	Se lo si desidera, aggiungere un elenco di valori o specificare una query per fornire suggerimenti per l'input.
   Valore predefinito	Questa opzione viene visualizzata solo se l'opzione Valori suggeriti è impostata su Elenco di valori e specifica quale voce di elenco è l'impostazione predefinita. In questo caso, è necessario scegliere un valore predefinito.
   Valore corrente	A seconda della posizione in cui si usa il parametro, se la query è vuota potrebbe non restituire alcun risultato. Se l'opzione Obbligatorio è selezionata, valore corrente non può essere vuoto.

5. Per creare il parametro, selezionare OK.

Ecco un modo per gestire le modifiche alle posizioni delle origini dati e prevenire gli errori di aggiornamento. Ad esempio, presupponendo uno schema e un'origine dati simili, creare un parametro per modificare facilmente un'origine dati e prevenire gli errori di aggiornamento dei dati. A volte il server, il database, la cartella, il nome del file o il percorso cambia. Un responsabile di database scambia occasionalmente un server, una selezione mensile di file CSV viene spostata in un'altra cartella oppure è necessario passare facilmente da un ambiente di sviluppo/test/produzione a un altro.

Passaggio 1: Creare una query con parametri

Nell'esempio seguente sono presenti diversi file CSV importati con l'operazione di importazione della cartella (Seleziona dati > Recupera dati > Da file > Da cartella) dalla cartella C:\DataFilesCSV1. A volte però viene usata una cartella diversa come posizione in cui eliminare i file, ovvero C:\DataFilesCSV2. È possibile usare un parametro in una query come valore sostitutivo per la cartella diversa.

1. Selezionare Home > Gestisci parametri > nuovo parametro.

2. Immettere le informazioni seguenti nella finestra di dialogo Gestisci parametro :

   Nome	CSVFileDrop
   Descrizione	Percorso di rilascio file alternativo
   Dati obbligatori	Sì
   Tipo	Testo
   Valori suggeriti	Qualsiasi valore
   Valore corrente	C:\DataFilesCSV1

3. Seleziona OK.

Passaggio 2: Aggiungere il parametro alla query di dati

1. Per impostare il nome della cartella come parametro, in Impostazioni query in Passaggi query selezionare Origine e quindi Modifica impostazioni.

2. Verificare che l'opzione Percorso file sia impostata su Parametro e quindi selezionare il parametro appena creato nell'elenco a discesa.

3. Seleziona OK.

Passaggio 3: Aggiornare il valore del parametro

Il percorso della cartella è appena cambiato, quindi ora è possibile aggiornare semplicemente la query con parametri.

1. Selezionare Connessioni> dati & scheda Query > Query , fare clic con il pulsante destro del mouse sulla query con parametri e quindi scegliere Modifica.

2. Immettere il nuovo percorso nella casella Valore corrente , ad esempio C:\DataFilesCSV2.

3. Selezionare Home > Chiudi & Carica.

4. Per confermare i risultati, aggiungere nuovi dati all'origine dati e quindi aggiornare la query con il parametro aggiornato (Selezionare dati > Aggiorna tutto).

A volte si vuole modificare facilmente il filtro di una query per ottenere risultati diversi senza modificare la query o creare copie leggermente diverse della stessa query. In questo esempio modifichiamo una data per modificare comodamente un filtro dei dati.

1. Per aprire una query, individuarne una precedentemente caricata dal editor di Power Query, selezionare una cella nei dati e quindi selezionare Query > Modifica. Per altre informazioni , vedere Creare, caricare o modificare una query in Excel.

2. Selezionare la freccia del filtro in qualsiasi intestazione di colonna per filtrare i dati e quindi selezionare un comando di filtro, ad esempio Filtri per data/ora > Dopo. Viene visualizzata la finestra di dialogo Filtra righe .
   
   

3. Selezionare il pulsante a sinistra della casella Valore , quindi eseguire una delle operazioni seguenti:

   • Per usare un parametro esistente, selezionare Parametro e quindi selezionare il parametro desiderato nell'elenco visualizzato a destra.

   • Per usare un nuovo parametro, selezionare Nuovo parametro e quindi creare un parametro.

4. Immettere la nuova data nella casella Valore corrente e quindi selezionare Home > Chiudi & Carica.

5. Per confermare i risultati, aggiungere nuovi dati all'origine dati e quindi aggiornare la query con il parametro aggiornato (Selezionare dati > Aggiorna tutto). Ad esempio, modificare il valore del filtro in una data diversa per visualizzare i nuovi risultati.

6. Immettere la nuova data nella casella Valore corrente .

7. Selezionare Home > Chiudi & Carica.

8. Per confermare i risultati, aggiungere nuovi dati all'origine dati e quindi aggiornare la query con il parametro aggiornato (Selezionare dati > Aggiorna tutto).

In questo esempio il valore del parametro della query viene letto da una cella della cartella di lavoro. Non è necessario modificare la query con parametri, basta 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.

1. Nel foglio di lavoro di una cartella di lavoro in cui è caricata la query da filtrare creare una tabella di Excel con due celle: un'intestazione e un valore.
    

   Filtro personale
   G

2. Selezionare una cella nella tabella di Excel, quindi selezionare Dati > Recupera dati > Da tabella/intervallo. Viene visualizzata la editor di Power Query.

3. Nella casella Nome del riquadro Impostazioni query a destra modificare il nome della query in modo che sia più significativo, ad esempio FilterCellValue. 

4. Per passare il valore nella tabella e non nella tabella stessa, fare clic con il pulsante destro del mouse sul valore in Anteprima dati e quindi selezionare Drill-down.

   Si noti che la formula è cambiata in = #"Changed Type"{0}[MyFilter]

   Quando si usa la tabella di Excel come filtro nel passaggio 10, Power Query fa riferimento al valore Table come condizione di filtro. Un riferimento diretto alla tabella di Excel causerebbe un errore.

5. Selezionare Home > Chiudi & Carica > Chiudi & Carica in. È ora disponibile un parametro di query denominato "FilterCellValue" usato nel passaggio 12.

6. Nella finestra di dialogo Importa dati selezionare Crea solo connessione e quindi fare clic su OK.

7. Aprire la query da filtrare con il valore della tabella FilterCellValue, precedentemente caricata dal editor di Power Query, selezionando una cella nei dati e quindi query> modifica. Per altre informazioni , vedere Creare, caricare o modificare una query in Excel.

8. Selezionare la freccia del filtro in qualsiasi intestazione di colonna per filtrare i dati e quindi selezionare un comando di filtro, ad esempio Filtri per testo > Inizia con. Viene visualizzata la finestra di dialogo Filtra righe . 

9. Immettere un valore qualsiasi nella casella Valore , ad esempio "G", quindi selezionare OK. In questo caso, il valore è un segnaposto temporaneo per il valore della tabella FilterCellValue immesso nel passaggio successivo.

10. 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"))

11. Selezionare il valore del filtro. Nella formula selezionare "G".

12. Usando M Intellisense, immettere la prima lettera della tabella FilterCellValue creata e quindi selezionarla nell'elenco visualizzato.

13. Selezionare Home > Chiudi > Chiudi & Carica.

Risultato

La query usa ora 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 di Excel originale nel passaggio 1, modificare "G" in "V" e quindi aggiornare la query.

È possibile controllare se le query con parametri sono consentite o meno.

1. Nella editor di Power Query selezionare Opzioni> file e Impostazioni > Opzioni query > editor di Power Query.

2. Nel riquadro a sinistra, in GLOBALE, selezionare editor di Power Query.

3. Nel riquadro a destra, in Parametri, selezionare o deselezionare Consenti sempre la parametrizzazione nelle finestre di dialogo di origine dati e trasformazione.