Esercitazione: Modificare la forma dei dati e combinarli in Power BI Desktop

Articolo
07/26/2024

Con Power BI Desktop è possibile connettersi a molti tipi diversi di origini dati e quindi modificare la forma dei dati in base alle esigenze, consentendo la creazione di report visivi da condividere con altri utenti. Per data shaping si intende la trasformazione dei dati: ridenominazione di colonne o tabelle, trasformazione del testo in numeri, rimozione di righe, impostazione della prima riga come intestazione e così via. Per combinazione dei dati si intende la connessione di due o più origini dati, il data shaping necessario e quindi il consolidamento dei dati in una singola query utile.

Questa esercitazione illustra come:

Modellare i dati usando l'editor di Power Query.
Connettersi a varie origini dati.
Combinare le origini dati e creare un modello di dati da usare nei report.

Questa esercitazione descrive come modificare la forma di una query usando Power BI Desktop ed evidenzia le attività più comuni. La query usata in questo esempio viene descritta in modo più dettagliato, con indicazioni anche su come creare la query partendo da zero, in Introduzione a Power BI Desktop.

L'editor di Power Query in Power BI Desktop usa i menu di scelta rapida e la barra multifunzione Trasforma. La maggior parte delle opzioni che è possibile selezionare nella barra multifunzione è disponibile anche facendo clic con il pulsante destro del mouse su un elemento, ad esempio una colonna, e scegliendo un'opzione dal menu visualizzato.

Modifica della forma dei dati

Per modellare i dati nell'editor di Power Query, è possibile fornire istruzioni dettagliate per l'editor di Power Query per modificare i dati durante il caricamento e la presentazione dei dati. L'origine dati originale non subisce alcuna modifica. Viene modificata, o sottoposta a data shaping, solo questa vista specifica dei dati.

I passaggi specificati (ad esempio rinominare una tabella, trasformare un tipo di dati o eliminare una colonna) vengono registrati dall'Editor di Power Query. Ogni volta che questa query si connette all'origine dati, l'Editor di Power Query esegue tali passaggi in modo che i dati abbiano sempre la forma specificata. Questo processo si verifica ogni volta che si usa l'Editor di Power Query oppure per chiunque usi la query condivisa, ad esempio nel servizio Power BI. Questi passaggi vengono acquisiti, in sequenza, nel riquadro Impostazioni query in PASSAGGI APPLICATI. Ognuno di questi passaggi verrà illustrato in questo articolo.

Screenshot dell'editor di Power Query con il riquadro Impostazioni query e l'elenco Passaggi applicati.

Importare i dati da un'origine Web. Selezionare l'elenco a discesa Recupera dati, quindi scegliere Web.
Incollare questo URL nella finestra di dialogo Da Web e selezionare OK.
```
https://www.fool.com/research/best-states-to-retire
```
Nella finestra di dialogo Strumento di navigazione selezionare Table 1, quindi scegliere Trasforma dati.

Suggerimento

Alcune informazioni nelle tabelle dell'URL precedente possono essere modificate o aggiornate occasionalmente. Di conseguenza, potrebbe essere necessario modificare le selezioni o i passaggi in questo articolo di conseguenza.

Viene visualizzata la finestra dell'editor di Power Query. È possibile visualizzare i passaggi predefiniti applicati finora, nel riquadro Impostazioni query in PASSAGGI APPLICATI.
- Origine: connessione al sito Web.
- Tabella estratta da Html: selezione della tabella.
- intestazioni alzate di livello: modifica della riga superiore dei dati in intestazioni di colonna.
- Tipo modificato: modifica dei tipi di colonna, importati come testo, nei relativi tipi dedotti.
Modificare il nome della tabella dal valore predefinito Table 1 a Retirement Data, quindi premere INVIO.
I dati esistenti vengono ordinati in base a un punteggio ponderato, come descritto nella pagina Web di origine in Metodologia. Aggiungere una colonna personalizzata per calcolare un punteggio diverso. Si eseguirà quindi l'ordinamento della tabella in questa colonna per confrontare la classificazione del punteggio personalizzato con il rank esistente.
Nella barra multifunzione Aggiungi colonna selezionare colonna personalizzata.

Nella finestra di dialogo Colonna personalizzata, immettere Nuovo punteggio in Nome nuova colonna. Per la Formula colonna personalizzata immettere i dati seguenti:

( [Quality of life] + [Housing cost] + [Healthcare cost and quality] + [Crime rate rate] + [#"Public health/COVID-19 response"] + [Sales taxes] + [#"Non-housing costs"] + [Weather] ) / 8

Verificare che il messaggio di stato sia Non sono stati rilevati errori di sintassi e selezionare OK.
In Impostazioni query, l'elenco PASSAGGI APPLICATI mostra ora il nuovo passaggio Aggiunta personalizzata appena definita.

Modificare i dati

Prima di lavorare con questa query, verranno apportate alcune modifiche per modificarne i dati:

Modificare le classificazioni rimuovendo una colonna.

Si supponga, ad esempio, che Weather non sia un fattore nei risultati. La rimozione di questa colonna dalla query non influisce sugli altri dati.
Correggere eventuali errori.

Poiché è stata rimossa una colonna, è necessario modificare i calcoli nella colonna Nuovo punteggio modificandone la formula.
Ordinare i dati.

Ordinare i dati in base alla colonna Nuovo punteggio e confrontare la colonna Rank esistente.
Sostituire i dati.

Verrà illustrato come sostituire un valore specifico e come inserire un passaggio applicato.

Queste modifiche sono descritte nei passaggi seguenti.

Per rimuovere la colonna Meteo, selezionare la colonna, scegliere la scheda Home della barra multifunzione e quindi selezionare Rimuovi colonne.

Nota

I valori Nuovo punteggio non sono stati modificati a causa dell'ordinamento dei passaggi. L'editor di Power Query registra i passaggi in sequenza, ma indipendentemente l'uno dall'altro. Per applicare azioni in una sequenza diversa, è possibile spostare ogni passaggio applicato verso l'alto o verso il basso.
Fare clic con il pulsante destro del mouse su un passaggio per visualizzare il relativo menu di scelta rapida.
Spostare l'ultimo passaggio, Rimosse colonne, subito sopra il passaggio Aggiunta colonna personalizzata.
Selezionare il passaggio Aggiunta colonna personalizzata.

Si noti che la colonna Nuovo punteggio mostra ora errore anziché il valore calcolato.

È possibile ottenere altre informazioni su ogni errore in vari modi. Se si seleziona la cella senza fare clic sulla parola Errore, l'editor di Power Query visualizza le informazioni sull'errore.

Se si seleziona direttamente la parola Errore, l'Editor di Power Query crea una sezione Passaggi applicati nel riquadro Impostazioni query e visualizza le informazioni sull'errore. Poiché non è necessario visualizzare informazioni sull'errore altrove, selezionare Annulla.
Per correggere gli errori, sono necessarie due modifiche, rimuovendo il nome della colonna Weather e modificando il divisore da 8 a 7. È possibile apportare queste modifiche in due modi:
1. Fare clic con il pulsante destro del mouse sul passaggio Colonna personalizzata e scegliere Modifica impostazioni. Verrà visualizzata la finestra di dialogo Colonna personalizzata usata per creare la colonna Nuovo punteggio. Modificare la formula come descritto in precedenza, fino a quando non ha un aspetto simile al seguente:
2. Selezionare la colonna Nuovo punteggio, quindi visualizzare la formula dei dati della colonna abilitando la casella di controllo barra della formula dalla scheda Visualizzazione.
  
  Modificare la formula come descritto in precedenza, fino a quando non appare così, quindi premere INVIO.
```
= Table.AddColumn(#"Removed Columns", "New score", each ( [Quality of life] + [Housing cost] + [Healthcare cost and quality] + [Crime rate rate] + [#"Public health/COVID-19 response"] + [Sales taxes] + [#"Non-housing costs"] ) / 7)              
```
L'Editor di Power Query sostituisce i dati con i valori modificati e il passaggio Aggiunta colonna personalizzata viene completato senza errori.

Nota

È anche possibile selezionare Rimuovi errori, usando la barra multifunzione o il menu di scelta rapida, che rimuove tutte le righe che contengono errori, Tuttavia, in questa esercitazione si vogliono mantenere tutti i dati nella tabella.
Ordinare i dati in base alla colonna nuovo punteggio. Selezionare prima di tutto l'ultimo passaggio applicato Aggiunta personalizzata per visualizzare i dati più recenti. Selezionare quindi l'elenco a discesa accanto all'intestazione Nuova colonna punteggio e scegliere Ordinamento decrescente.

I dati vengono ora ordinati in base a Nuovo punteggio. È possibile selezionare un passaggio applicato in un punto qualsiasi dell'elenco e continuare a modellare i dati in quel punto della sequenza. L'Editor di Power Query inserisce automaticamente un nuovo passaggio dopo quello attualmente selezionato in Passaggi applicati.
In PASSAGGI APPLICATI selezionare il passaggio che precede la colonna personalizzata, ovvero il passaggio Rimosse colonne. In questo caso si sostituirà il valore della classifica dei costi housing in Oregon. Fare clic con il pulsante destro del mouse sulla cella appropriata contenente il valore del costo Housing di Oregon e quindi scegliere Sostituisci valori. Notare il passaggio applicato attualmente selezionato.
Selezionare Inserisci.

Poiché si inserisce un passaggio, l'editor di Power Query ricorda che i passaggi successivi potrebbero causare l'interruzione della query.
Impostare il valore dei dati su 100.0.

L'editor di Power Query sostituisce i dati per Oregon. Quando si crea un nuovo passaggio applicato, l'Editor di Power Query assegna al passaggio un nome in base all'azione, in questo caso Sostituito valore. Se nella query sono presenti più passaggi con lo stesso nome, l'editor di Power Query aggiunge un numero crescente al nome di ogni passaggio applicato successivo.
Selezionare l'ultimo passaggio applicato, Ordinate righe.

Si noti che sono cambiati i dati relativi alla nuova classificazione di Oregon. Questa modifica si verifica perché è stato inserito il passaggio Sostituito valore nella posizione corretta, prima del passaggio Aggiunta colonna personalizzata.

Il data shaping è stato completato nel modo desiderato. Ci si connetterà ora a un'altra origine dati per combinare i dati.

Combinare i dati

I dati sui diversi stati sono interessanti e saranno utili per la creazione di analisi e query aggiuntive. Tuttavia, la maggior parte dei dati sugli stati usa un'abbreviazione di due lettere per i codici di stato, non il nome completo dello stato. È necessario trovare un modo per associare i nomi degli stati e le rispettive abbreviazioni.

Esiste un'altra origine dati pubblica che fornisce tale associazione, ma richiede una quantità equa di data shaping prima di poterla connettere alla tabella di ritiro. Per modificare la forma dei dati, seguire questa procedura:

Nella barra multifunzione Home dell'Editor di Power Query selezionare Nuova origine > Web.
Immettere l'indirizzo del sito Web per le abbreviazioni degli stati, https://en.wikipedia.org/wiki/List_of_U.S._state_abbreviations e quindi selezionare Connetti.

Lo strumento di navigazione visualizza il contenuto del sito Web.
Selezionare Codici e abbreviazioni per gli stati degli Stati Uniti, il distretto federale, i territori e altre aree geografiche.

Suggerimento

Saranno necessarie alcune operazioni di data shaping per ridurre la quantità di dati della tabella. Esiste un modo più veloce o più semplice per eseguire la procedura seguente? e cioè creare una relazione tra le due tabelle e modellare i dati in base a tale relazione. I passaggi di esempio seguenti sono utili per imparare a usare le tabelle. Tuttavia, le relazioni consentono di usare rapidamente i dati di più tabelle.

Per modificare la forma dei dati, seguire questa procedura:

Rimuovere la prima riga. Poiché è il risultato del modo in cui è stata creata la tabella della pagina Web, non è necessaria. Nella barra multifunzione Home selezionare Rimuovi righe > Rimuovi righe principali.

Verrà visualizzata la finestra di dialogo Rimuovi prime righe. Specificare 1 riga da rimuovere.
Alzare di livello la nuova riga superiore alle intestazioni con Usa intestazioni come prima riga dalla scheda Home o dalla scheda Trasforma della barra multifunzione.
Poiché la tabella Dati ritiro non contiene informazioni per Washington DC o territori, è necessario filtrarli dall'elenco. Selezionare il Nome e stato dell'elenco a discesa della colonna di region_1, quindi deselezionare tutte le caselle di controllo tranne Stato.
Rimuovere tutte le colonne non richieste. Poiché è necessario solo il mapping di ogni stato alla relativa abbreviazione ufficiale di due lettere (nome e stato dell'area e colonne ANSI), è possibile rimuovere le altre colonne. Selezionare prima di tutto la colonna Nome e stato dell'area, quindi tenere premuto CTRL e selezionare la colonna ANSI. Nella scheda Home della barra multifunzione selezionare Rimuovi colonne > Rimuovi altre colonne.

Nota

La sequenza di passaggi applicati nell'editor di Power Query è importante e influisce sulla forma dei dati. È anche importante considerare come un passaggio potrebbe influire su un altro passaggio successivo. Ad esempio, se si rimuove un passaggio dai passaggi applicati, i passaggi successivi potrebbero non comportarsi come previsto originariamente.

Nota

Quando si ridimensiona la finestra dell'Editor di Power Query per ridurne la larghezza, alcuni elementi della barra multifunzione vengono compressi per ottimizzare l'uso dello spazio visibile. Quando si aumenta la larghezza della finestra dell'Editor di Power Query, gli elementi della barra multifunzione vengono estesi per ottimizzare l'uso dell'area ingrandita.
Rinominare le colonne e la tabella. Esistono alcuni modi per rinominare una colonna: selezionare prima la colonna e quindi selezionare Rinomina nella scheda Trasforma della barra multifunzione oppure fare clic con il pulsante destro del mouse e scegliere Rinomina. L'immagine seguente mostra entrambe le opzioni, ma è sufficiente sceglierne una.
Rinominare le colonne in State Name e State Code. Per rinominare la tabella, immettere il Nome Codici di stato nel riquadro Impostazioni query.

Combinare le query

Ora che è stata data la forma desiderata alla tabella Codici di stato, queste due tabelle, o query, verranno combinate in una sola. Poiché le tabelle ottenute sono il risultato delle query applicate ai dati, spesso vengono chiamate query.

Esistono due modi principali per combinare le query, ovvero unione e accodamento.

Per una o più colonne da aggiungere a un'altra query, è consigliabile unire le query.
Per una o più righe di dati da aggiungere a una query esistente, si accoda la query.

In questo caso verrà eseguito il merge delle query:

Dal riquadro sinistro dell'Editor di Power Query query selezionare la query in cui si vuole eseguire il merge dell'altra query. In questo caso, si tratta di dati di ritiro.
Selezionare Unisci query> Unisci query dalla scheda Home della barra multifunzione.

Potrebbe essere richiesto di impostare i livelli di privacy, per garantire che i dati vengano combinati senza includere o trasferire dati che non devono essere trasferiti.

Viene visualizzata la finestra Merge. Viene chiesto di selezionare la tabella di cui eseguire il merge nella tabella selezionata e le colonne corrispondenti da usare per il merge.
Selezionare Stato dalla tabella Data di ritiro, quindi selezionare la query codici di stato.

Quando si selezionano colonne corrispondenti, il pulsante OK è abilitato.
Seleziona OK.

L'editor di Power Query crea una nuova colonna alla fine della query, che contiene il contenuto della tabella (query) unita alla query esistente. Tutte le colonne della query sottoposta a merge vengono condensate nella colonna, ma è possibile espandere la tabella e includere le colonne desiderate.
Per espandere la tabella sottoposta a merge e selezionare le colonne da includere, fare clic sull'icona di espansione ( ).

Viene visualizzata la finestra Espandi.
In questo caso, è necessaria solo la colonna State Code. Selezionare tale colonna, deselezionare Usa il nome della colonna originale come prefisso e quindi selezionare OK.

Se la casella di controllo Usa il nome della colonna originale come prefisso fosse stata lasciata selezionata, il nome della colonna sottoposta a merge sarebbe State Codes.State Code.

Nota

Se si vuole esplorare come inserire la tabella codici di stato, è possibile sperimentare un po'. Se non piacciono i risultati, è sufficiente eliminare tale passaggio dall'elenco PASSAGGI APPLICATI nel riquadro Impostazioni query e la query torna allo stato prima per applicare tale passaggio Espandi. È possibile ripetere più volte l'operazione, fino a ottenere il risultato desiderato dal processo di espansione.

È ora disponibile una singola query (tabella) che combina due origini dati, ognuna delle quali è stata sottoposta a data shaping per soddisfare le esigenze specifiche. Questa query può essere una base per connessioni dati interessanti, ad esempio statistiche sui costi di abitazioni, qualità della vita o tasso di criminalità in qualsiasi stato.
Per applicare le modifiche e chiudere l'Editor di Power Query, selezionare Chiudi e applica nella scheda Home della barra multifunzione.

Il modello semantico trasformato viene visualizzato in Power BI Desktop, pronto per essere usato per la creazione di report.