Creare formule di Power Query in Excel

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

1. Barra multifunzione dell'editor di Power Query che consente di modellare i dati

2. Riquadro Query che consente di individuare origini dati e tabelle

3. Menu di scelta rapida che consentono di accedere facilmente ai comandi della barra multifunzione

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

5. Riquadro Impostazioni query in cui sono elencate le proprietà e ogni passaggio della query

Dietro le quinte, 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 di Power Query, che è possibile usare per creare espressioni semplici e complesse. Per altre informazioni sulla sintassi, gli argomenti, le osservazioni, le funzioni e gli esempi, vedere Linguaggio della formula M di Power Query.

Usando un elenco di campionati di calcio come esempio, usare Power Query per prendere dati non elaborati trovati in un sito Web e trasformarlo 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> dal 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 spostamento selezionare la tabella Risultati [Modifica] a sinistra e quindi selezionare Trasforma dati nella parte inferiore. Viene visualizzato l'editor di Power Query.

3. Per modificare il nome della query predefinita, 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 scegliere Home > Rimuovi colonna > Rimuovi altre colonne.

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

6. Per rimuovere le righe in cui è presente la parola "Anno", selezionare la freccia di filtro in Colonna1,deselezionare la casella di controllo accanto a "Anno", quindi scegliere 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 Le intestazioni di colonna modificate sono significative	= Table.RenameColumns(#"Filtered Rows",{{"Column1", "Year"}, {"Column4", "Winner"}, {"Column5", "Final Score"}})

Importante    Prestare attenzione a modificarei passaggi Origine, Spostamentoe Tipo modificato perché vengono creati da Power Query per definire e   configurare l'origine dati.

Mostrare o nascondere la barra della formula

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

• Selezionare Visualizza >layout > barra della formula.

Edit a formula in the formula bar

1. Per aprire una query, individuarne una precedentemente caricata dall'editor di Power Query, selezionare una cella nei dati e quindi selezionare> Modifica. Per altre informazioni, vedere 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 e quindi selezionare l'icona 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' o premere INVIO per visualizzare i nuovi risultati nell'anteprima dati.

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

Creare una formula nella barra della formula

Per un semplice esempio di formula, convertire un valore di testo in maiuscole/minuscole usando la funzione Text.Proper.

1. Per aprire una query vuota, in Excel selezionare> Ottieni dati > da altre origini > Query vuota. Per altre informazioni, vedere Creare, caricare o modificare una query in Excel.

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

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

Risultato:

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

Importante    Prestare attenzione a modificarei passaggi Origine, Spostamentoe 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 le finestre di dialogo che variano a seconda del passaggio. Non è necessario conoscere la sintassi della formula.

1. Per aprire una query, individuarne una precedentemente caricata dall'editor di Power Query, selezionare una cella nei dati e quindi selezionare> Modifica. Per altre informazioni, vedere 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 desiderate e quindi scegliere OK.

Inserire un passaggio

Dopo aver completato un passaggio della query che rimodella i dati, viene aggiunto un passaggio di query sotto il passaggio della query corrente. ma quando si inserisce un passaggio di query al centro dei passaggi, nei passaggi successivi potrebbe verificarsi un errore. Power Query visualizza un avviso Inserisci passaggio quando si prova a inserire un nuovo passaggio e il nuovo passaggio modifica i campi, ad esempio i nomi delle colonne, usati in uno dei passaggi che seguono il passaggio inserito.

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

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

3. Digitare la nuova formula usando il formato:
   
   =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 "Sig.", 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 applicatifare clic con il pulsante destro del mouse sul passaggio e quindi scegliere Sposta su o Sposta giù.

Elimina passaggio

• Selezionare l'icona 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 è disponibile anche a sinistra della barra della formula.

In questo esempio convertiamo il testo in una colonna in maiuscole/minuscole usando una combinazione di formule nell'editor avanzato. 

Ad esempio, si ha una tabella Excel, denominata Ordini, con una colonna ProductName che si vuole convertire in maiuscole/minuscole. 

Prima:

Dopo:

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

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

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

Fase 1: Aprire l'editor avanzato

1. In Excel selezionare Dati> Ottieni dati > altre origini > query vuota. Per altre informazioni, vedere Creare, caricare o modificare una query in Excel.

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

Fase 2: Definire l'origine dati

1. Creare l'espressione let usando il Excel. CurrentWorkbook come indicato di seguito:
   
   let#x1
   
   in
       Source
   
   

2. Per caricare la query in un foglio di lavoro, selezionare Fine equindi> 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 > Modifica. Per altre informazioni, vedere Creare, caricare o modificare una query in Excel (Power Query).

2. Nell'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" = Table.PromoteHeaders(Source)#x3
   
       #"First Row as Header"

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

Risultato:

Fase 4: Modificare ogni valore di una colonna in base alle maiuscole/minuscole

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

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

3. Nell'espressione let convertire ogni valore di 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 equindi> Chiudi & Carica > Chiudi & Carica.

Risultato:

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

Visualizzare o nascondere la barra della formula

1. Selezionare Opzioni> file e Impostazioni > query.

2. Nel riquadro sinistro, in GLOBALE,selezionare Editor 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 > query .

2. Nel riquadro sinistro, in GLOBALE,selezionare Editor Power Query.

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

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