Creare formule di Power Query in Excel

La editor di Power Query offre un'esperienza di creazione e modifica della query di dati per Excel che è possibile usare per modificare la forma dei dati da molte origini dati. Per visualizzare la finestra editor di Power Query, importare dati da origini dati esternein un foglio di lavoro di Excel, selezionare una cella nei dati e quindi selezionare Query > Modifica. Di seguito è riportato un riepilogo dei componenti principali.

1. Barra multifunzione editor di Power Query usata per modellare i dati

2. Riquadro Query usato per individuare le origini dati e le tabelle

3. Menu di scelta rapida utili collegamenti ai comandi della barra multifunzione

4. Anteprima dati che visualizza i risultati dei passaggi applicati ai dati

5. Riquadro Impostazioni query che elenca le proprietà e ogni passaggio della query

Ogni passaggio di una query si basa su una formula visibile nella barra della formula.

A volte può essere necessario modificare o creare una formula. Le formule usano il linguaggio delle formule Power Query, che consente di creare espressioni sia semplici che complesse. Per altre informazioni su sintassi, argomenti, osservazioni, funzioni ed esempi, vedere Power Query linguaggio delle formule M.

Usando un elenco di campionati di calcio come esempio, usare Power Query per prendere i dati non elaborati trovati in un sito Web e trasformarli in una tabella ben formattata. Osservare come vengono creati i passaggi della query e le formule corrispondenti per ogni attività nel riquadro Impostazioni query in Passaggi applicati e nella barra della formula.

Procedura

1. Per importare i dati, selezionare Dati > Da Web, immettere "http://en.wikipedia.org/wiki/UEFA_European_Football_Championship" nella casella URL e quindi selezionare OK.

2. Nella finestra di dialogo Strumento di navigazione selezionare la tabella Risultati [Modifica] a sinistra e quindi selezionare Trasforma dati in basso. Viene visualizzato l'editor Power Query.

3. Per modificare il nome predefinito della query, nel riquadro Impostazioni query , in Proprietà, eliminare "Risultati [Modifica]" e quindi immettere "Campioni UEFA".

4. Per rimuovere le colonne indesiderate, selezionare la prima, la quarta e la quinta colonna e quindi selezionare Home > Rimuovi colonna > Rimuovi altre colonne.

5. Per rimuovere i valori indesiderati, selezionare Colonna1, selezionare Home > Sostituisci valori, immettere "dettagli" nella casella Valori da trovare e quindi selezionare OK.

6. Per rimuovere le righe che contengono la parola "Anno", selezionare la freccia di filtro in Colonna1, deselezionare la casella di controllo accanto a "Anno" e quindi fare clic su OK.

7. Per rinominare le intestazioni di colonna, fare doppio clic su ognuna di esse e quindi modificare "Column1" in "Year", "Column4" in "Winner" e "Column5" in "Final Score".

8. Per salvare la query, selezionare Home > Chiudi & Carica.

Risultato

La tabella seguente contiene un riepilogo di ogni passaggio applicato e della formula corrispondente.

Passaggio e attività della query	Formula
Origine Connettersi a un'origine dati Web	= Web.Page(Web.Contents("http://en.wikipedia.org/wiki/UEFA_European_Football_Championship"))
Spostamento Selezionare la tabella per la connessione	=Source{2}[Data]
Modificato tipo Modificare i tipi di dati , che Power Query esegue automaticamente	= Table.TransformColumnTypes(Data2,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}})
Rimosse altre colonne Rimuovere le altre colonne per visualizzare solo le colonne di interesse	= Table.SelectColumns(#"Changed Type",{"Column1", "Column4", "Column5"})
Valore sostituito Sostituire i valori per pulire i valori in una colonna selezionata	= Table.ReplaceValue(#"Removed Other Columns","Details","",Replacer.ReplaceText,{"Column1"})
Righe filtrate Filtrare i valori in una colonna	= Table.SelectRows(#"Replaced Value", each ([Column1] <> "Year"))
Colonne rinominate Intestazioni di colonna modificate in modo che siano significative	= Table.RenameColumns(#"Filtered Rows",{{"Column1", "Year"}, {"Column4", "Winner"}, {"Column5", "Final Score"}})

Importante    Prestare attenzione quando si modificano i passaggi Origine, Spostamento  e Tipo modificato perché vengono creati da Power Query per definire e configurare l'origine dati.

Visualizzare o nascondere la barra della formula

La barra della formula viene visualizzata per impostazione predefinita, ma se non è visibile è possibile visualizzarla nuovamente.

• Selezionare Visualizza > Layout > barra della formula.

Edit a formula in the formula bar

1. Per aprire una query, individuane una che è stata precedentemente caricata dall'Editor di Power Query, seleziona una cella nei dati e quindi scegli Query > Modifica. Per altre informazioni leggi Creare, caricare o modificare una query in Excel.

2. Nel riquadro Impostazioni query , in Passaggi applicati, selezionare il passaggio da modificare.

3. Nella barra della formula individuare e modificare i valori dei parametri, quindi selezionare l'icona Invio o premere INVIO. Ad esempio, modificare questa formula per mantenere anche Column2:
   
   Before: = Table.SelectColumns(#"Changed Type",{"Column4", "Column1", "Column5"})
   After:= Table.SelectColumns(#"Changed Type",{"Column2", "Column4", "Column1", "Column5"})

4. Selezionare l'icona invio o premere INVIO per visualizzare i nuovi risultati nell'anteprima dei dati.

5. Per visualizzare il risultato in un foglio di lavoro di Excel, selezionare Home > Chiudi & Carica.

Creare una formula nella barra della formula

Per un esempio di formula semplice, convertiamo un valore di testo nella combinazione appropriata di maiuscole/minuscole usando la funzione Text.Proper.

1. Per aprire una query vuota, in Excel selezionare Dati > Recupera dati > Da altre origini > Query vuota. Per altre informazioni leggi Creare, caricare o modificare una query in Excel.

2. Nella barra della formula immettere=Text.Proper("text value")e quindi selezionare l'icona Invio o premere INVIO.
   
   I risultati vengono visualizzati in Anteprima dati.

3. Per visualizzare il risultato in un foglio di lavoro di Excel, selezionare Home > Chiudi & Carica.

Risultato:

 Quando si crea una formula, Power Query convalida la sintassi della formula. Tuttavia, quando si inserisce, riordina o elimina un passaggio intermedio in una query, è possibile che si verifichi un'interruzione di una query.  Verifica sempre i risultati in Anteprima dati.

Importante    Prestare attenzione quando si modificano i passaggi Origine, Spostamento  e Tipo modificato perché vengono creati da Power Query per definire e configurare l'origine dati.

Modificare una formula usando una finestra di dialogo

Questo metodo usa finestre di dialogo che variano a seconda del passaggio. Non è necessario conoscere la sintassi della formula.

1. Per aprire una query, individuane una che è stata precedentemente caricata dall'Editor di Power Query, seleziona una cella nei dati e quindi scegli Query > Modifica. Per altre informazioni leggi Creare, caricare o modificare una query in Excel.

2. Nel riquadro Impostazioni query , in Passaggi applicati, selezionare l'icona Modifica impostazioni del passaggio da modificare o fare clic con il pulsante destro del mouse sul passaggio e quindi scegliere Modifica impostazioni.

3. Nella finestra di dialogo apportare le modifiche e quindi scegliere OK.

Inserire un passaggio

Dopo aver completato un passaggio della query che modifica la forma dei dati, viene aggiunto un passaggio della query sotto il passaggio della query corrente. ma quando si inserisce un passaggio della query al centro dei passaggi, è possibile che si verifichi un errore nei passaggi successivi. Power Query visualizza un avviso Inserisci passaggio quando si tenta di inserire un nuovo passaggio e il nuovo passaggio modifica i campi, ad esempio i nomi di colonna, usati in uno dei passaggi successivi al passaggio inserito.

1. Nel riquadro Impostazioni query , in Passaggi applicati, selezionare il passaggio che deve precedere immediatamente il nuovo passaggio e la formula corrispondente.

2. Selezionare l'icona Aggiungi passaggio a sinistra della barra della formula. In alternativa, fare clic con il pulsante destro del mouse su un passaggio e scegliere Inserisci passaggio dopo. Viene creata una nuova formula nel formato :
   
   = <nameOfTheStepToReference>, ad esempio =Production.WorkOrder.

3. Digitare la nuova formula nel formato seguente:
   
   =Class.Function(ReferenceStep[,otherparameters])
   
   Si supponga ad esempio di avere una tabella con la colonna Gender e di voler aggiungere una colonna con il valore "Ms". o "Mr.", a seconda del sesso della persona. La formula sarebbe:
   
   =Table.AddColumn(<ReferencedStep>, "Prefix", each if [Gender] = "F" then "Ms." else "Mr.")

Riordinare un passaggio

• Nel riquadro Impostazioni query , in Passaggi applicati, fare clic con il pulsante destro del mouse sul passaggio e quindi scegliere Sposta su o Sposta giù.

Elimina passaggio

• Selezionare l'icona Elimina a sinistra del passaggio oppure fare clic con il pulsante destro del mouse sul passaggio e quindi scegliere Elimina o Elimina fino alla fine. L'icona Elimina è disponibile anche a sinistra della barra della formula.

In questo esempio convertiamo il testo di una colonna in lettere maiuscole e minuscole usando una combinazione di formule nel Editor avanzato. 

Ad esempio, si ha una tabella di Excel, denominata Ordini, con una colonna NomeProdotto che si vuole convertire in maiuscolo. 

Prima:

Dopo:

Quando si crea una query avanzata, si crea una serie di passaggi della formula della query basati sull'espressione let. Usare l'espressione let per assegnare nomi e calcolare i valori a cui fa riferimento la clausola in , che definisce il passaggio. Questo esempio restituisce lo stesso risultato di quello visualizzato nella sezione "Creare una formula nella barra della formula".

let  
    Source = Text.Proper("hello world")
in  
    Source  

Si noterà che ogni passaggio si basa su un passaggio precedente facendo riferimento a un passaggio per nome. Come promemoria, il Power Query linguaggio delle formule fa distinzione tra maiuscole e minuscole.

Fase 1: aprire il Editor avanzato

1. In Excel selezionare Dati > Recupera dati > Altre origini > Query vuota. Per altre informazioni leggi Creare, caricare o modificare una query in Excel.

2. Nella editor di Power Query selezionare Home > Editor avanzato, che si apre con un modello di espressione let.

Fase 2: definire l'origine dati

1. Creare l'espressione let usando la funzione Excel.CurrentWorkbook come segue:
   
   let
       Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content]
   in
       
   
   #x4Source

2. Per caricare la query in un foglio di lavoro, selezionare Fine e quindi selezionare Home > Chiudi & Carica > Chiudi & Carica.

Risultato:

Fase 3: alzare di livello la prima riga in intestazioni

1. Per aprire la query, nel foglio di lavoro selezionare una cella nei dati e quindi selezionare Query > Modifica. Per altre informazioni, vedere Creare, caricare o modificare una query in Excel (Power Query).For more information see Create, load, or edit a query in Excel (Power Query).

2. Nella editor di Power Query selezionare Home > Editor avanzato, che si apre con l'istruzione creata nella fase 2: Definire l'origine dati.

3. Nell'espressione let aggiungere #"Prima riga come intestazione" e la funzione Table.PromoteHeaders nel modo seguente:
   
   let
       
   Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],   
       #"First Row as Header"#x3#"First Row as Header" = Table.PromoteHeaders(Source)
   

4. Per caricare la query in un foglio di lavoro, selezionare Fine e quindi selezionare Home > Chiudi & Carica > Chiudi & Carica.

Risultato:

Fase 4: modificare ogni valore di una colonna con l'iniziale maiuscola

1. Per aprire la query, nel foglio di lavoro selezionare una cella nei dati e quindi selezionare Query > Modifica. Per altre informazioni leggi Creare, caricare o modificare una query in Excel.

2. Nella editor di Power Query selezionare Home > Editor avanzato, che si apre con l'istruzione creata nella fase 3: Convertire la prima riga in intestazioni.

3. Nell'espressione let convertire ogni valore della colonna ProductName in testo corretto usando la funzione Table.TransformColumns, facendo riferimento al passaggio precedente della formula della query "First Row as Header", aggiungendo #"Capitalized Each Word" all'origine dati e quindi assegnando #"Capitalized Each Word" al risultato in.
   
   let
       Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
       #"First Row as Header" = Table.PromoteHeaders(Source),
       #"Capitalized Each Word" = Table.TransformColumns(#"First Row as Header",{{"ProductName", Text.Proper}})
   in
       #"Capitalized Each Word"

4. Per caricare la query in un foglio di lavoro, selezionare Fine e quindi selezionare Home > Chiudi & Carica > Chiudi & Carica.

Risultato:

È possibile controllare il comportamento della barra della formula nel editor di Power Query per tutte le cartelle di lavoro.

Visualizzare o nascondere la barra della formula

1. Selezionare Opzioni> file e Impostazioni > Opzioni query.

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

3. Nel riquadro destro, in Layout, selezionare o deselezionare Visualizza barra della formula.

Attivare o disattivare M Intellisense

1. Selezionare Opzioni> file e Impostazioni > Opzioni query .

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

3. Nel riquadro destro, in Formula, selezionare o deselezionare Abilita M Intellisense nella barra della formula, nell'editor avanzato e nella finestra di dialogo colonne personalizzate.

Nota:   La modifica di questa impostazione avrà effetto alla successiva apertura della finestra editor di Power Query.