Esercitazione: Modellare e combinare i dati in Power BI Desktop

Con Power BI Desktop è possibile connettersi a molti tipi diversi di origini dati, quindi modellare i dati in base alle proprie esigenze, consentendo di creare report visivi da condividere con altri utenti. Il data shaping implica la trasformazione dei dati: ridenominazione di colonne o tabelle, modifica del testo in numeri, rimozione di righe, impostazione della prima riga come intestazioni e così via. La combinazione dei dati significa connettersi a due o più origini dati, modellandole in base alle esigenze, quindi consolidandole in una singola query.

Questa esercitazione illustra come:

• Modellare i dati utilizzando editor di Power Query.
• Connessione a origini dati diverse.
• Combinare tali origini dati e creare un modello di dati da usare nei report.

Questa esercitazione illustra come modellare una query usando Power BI Desktop, evidenziando le attività più comuni. La query usata qui è descritta in modo più dettagliato, tra cui come creare la query da zero, in Introduzione a Power BI Desktop.

editor di Power Query in Power BI Desktop usa i menu di scelta rapida e Trasforma barra multifunzione. 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 dal menu visualizzato.

Modifica della forma dei dati

Per modellare i dati in editor di Power Query, è possibile fornire istruzioni dettagliate per editor di Power Query modificare i dati durante il caricamento e la presentazione dei dati. L'origine dati originale non è interessata; solo questa particolare visualizzazione dei dati viene modificata o modellata.

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

1. Importare i dati da un'origine Web. Selezionare l'elenco a discesa Recupera dati e quindi scegliere Web.

   

2. Incollare questo URL nella finestra di dialogo Da Web e selezionare OK.

   https://www.fool.com/research/best-states-to-retire
   

   

3. 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.

1. Verrà visualizzata la finestra editor di Power Query. È possibile visualizzare i passaggi predefiniti applicati finora, nel riquadro Query Impostazioni 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.

   

2. Modificare il nome della tabella dal valore predefinito Table 1 a Retirement Data, quindi premere INVIO.

   

3. 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 la classificazione esistente.

4. Nella barra multifunzione Aggiungi colonna selezionare Colonna personalizzata.

   

5. 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
   

6. Assicurarsi che il messaggio di stato non siano stati rilevati errori di sintassi e selezionare OK.

   

7. In Query Impostazioni l'elenco PASSAGGI APPLICATI mostra ora il nuovo passaggio Aggiunto personalizzato appena definito.

   

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.

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

   

   Nota

   I nuovi valori di punteggio non sono stati modificati a causa dell'ordinamento dei passaggi. 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.

2. Fare clic con il pulsante destro del mouse su un passaggio per visualizzare il relativo menu di scelta rapida.

   

3. Spostare verso l'alto l'ultimo passaggio, Removed Columns, in appena sopra il passaggio Aggiunta personalizzata .

   

4. Selezionare il passaggio Aggiunta personalizzata .

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

   

   Esistono diversi modi per ottenere altre informazioni su ogni errore. Se si seleziona la cella senza fare clic sulla parola Errore, editor di Power Query visualizza le informazioni sull'errore.

   

   Se si seleziona direttamente la parola Errore, editor di Power Query crea un passaggio applicato nel riquadro Query Impostazioni e visualizza informazioni sull'errore. Poiché non è necessario visualizzare informazioni sull'errore altrove, selezionare Annulla.

5. 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 nella scheda Visualizza .

      

      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)              
      

   editor di Power Query sostituisce i dati con i valori modificati e Aggiunta del passaggio Personalizzato completato senza errori.

   Nota

   È anche possibile selezionare Rimuovi errori, usando la barra multifunzione o il menu di scelta rapida, che rimuove tutte le righe con errori. Tuttavia, in questa esercitazione si vogliono mantenere tutti i dati nella tabella.

6. 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 al nuovo punteggio. È possibile selezionare un passaggio applicato in un punto qualsiasi dell'elenco e continuare a modellare i dati in quel punto della sequenza. editor di Power Query inserisce automaticamente un nuovo passaggio direttamente dopo il passaggio applicato attualmente selezionato.

7. In PASSAGGI APPLICATI selezionare il passaggio che precede la colonna personalizzata, ovvero il passaggio Rimuovi 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. Si noti che il passaggio applicato è attualmente selezionato.

   

8. Selezionare Inserisci.

   Poiché si inserisce un passaggio, editor di Power Query ricorda che i passaggi successivi potrebbero causare l'interruzione della query.

   

9. Modificare il valore dei dati in 100,0.

   editor di Power Query sostituisce i dati per Oregon. Quando si crea un nuovo passaggio applicato, editor di Power Query denominarlo in base all'azione, in questo caso, Valore sostituito. Se nella query sono presenti più passaggi con lo stesso nome, editor di Power Query aggiunge un numero crescente al nome di ogni passaggio applicato successivo.

10. Selezionare l'ultimo passaggio applicato, righe ordinate.

    Si noti che i dati sono stati modificati per quanto riguarda la nuova classificazione dell'Oregon. Questa modifica si verifica perché è stato inserito il passaggio Valore sostituito nella posizione corretta, prima del passaggio Aggiunto personalizzato .

    I dati sono stati modellati nella misura in cui è necessario. Ora ci si connette a un'altra origine dati e si combinano i dati.

Combinare i dati

I dati relativi a vari stati sono interessanti e saranno utili per creare ulteriori attività di analisi e query. 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 un modo per associare i nomi di stato alle relative 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 modellare i dati, seguire questa procedura:

1. Nella barra multifunzione Home in editor di Power Query selezionare Nuovo Web di origine>.

2. Immettere l'indirizzo del sito Web per le abbreviazioni di stato, https://en.wikipedia.org/wiki/List_of_U.S._state_abbreviationse quindi selezionare Connessione.

   Lo strumento di navigazione visualizza il contenuto del sito Web.

   

3. Selezionare Codici e abbreviazioni per gli stati degli Stati Uniti, il distretto federale, i territori e altre aree geografiche.

   Suggerimento

   Il data shaping di questa tabella richiederà un po' di dati fino a quello desiderato. Esiste un modo più rapido o semplice per eseguire i passaggi seguenti? Sì, è possibile 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 ottenere i dati in forma, seguire questa procedura:

1. Rimuovere la riga superiore. Poiché è un risultato del modo in cui è stata creata la tabella della pagina Web, non è necessaria. Nella barra multifunzione Home selezionare Rimuovi righe rimuovi prime righe>.

   

   Verrà visualizzata la finestra di dialogo Rimuovi prime righe . Specificare 1 riga da rimuovere.

2. 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.

3. Poiché la tabella Dati ritiro non contiene informazioni per Washington DC o territori, è necessario filtrarli dall'elenco. Selezionare il nome e lo stato dell'elenco a discesa della colonna region_1 , quindi deselezionare tutte le caselle di controllo ad eccezione di Stato.

   

4. 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 CTRLe selezionare la colonna ANSI. Nella scheda Home della barra multifunzione selezionare Rimuovi colonne > Rimuovi altre colonne.

   

   Nota

   La sequenza di passaggi applicati in 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 editor di Power Query per ridurre la larghezza, alcuni elementi della barra multifunzione vengono condensati per sfruttare al meglio lo spazio visibile. Quando si aumenta la larghezza della finestra editor di Power Query, gli elementi della barra multifunzione si espandono per sfruttare al meglio l'area della barra multifunzione aumentata.

5. Rinominare le colonne e la tabella. Esistono alcuni modi per rinominare una colonna: selezionare prima di tutto la colonna, quindi selezionare Rinomina nella scheda Trasforma sulla 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.

   

6. Rinominare le colonne in Nome stato e Codice stato. Per rinominare la tabella, immettere i codici di stato nomenel riquadro Query Impostazioni.

   

Combinare query

Ora che la tabella Codici di stato è stata modellata nel modo desiderato, è possibile combinare queste due tabelle, o query, in una sola. Poiché le tabelle ora disponibili sono il risultato delle query applicate ai dati, vengono spesso definite query.

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

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

In questo caso, si vuole unire le query:

1. Nel riquadro sinistro di editor di Power Query selezionare la query in cui si desidera unire l'altra query. In questo caso, si tratta di dati di ritiro.

2. Selezionare Unisci > query di merge dalla scheda Home della barra multifunzione.

   

   Potrebbe essere richiesto di impostare i livelli di privacy per assicurarsi che i dati vengano combinati senza includere o trasferire dati che non si desidera trasferire.

   Verrà visualizzata la finestra Merge . Viene richiesto di selezionare la tabella da unire nella tabella selezionata e le colonne corrispondenti da usare per l'unione.

3. Selezionare State (Stato ) nella tabella Retirement Data (Dati ritiro) e quindi selezionare la query State Codes (Codici di stato).

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

   

4. Seleziona OK.

   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 unita vengono condensate nella colonna, ma è possibile espandere la tabella e includere le colonne desiderate.

5. Per espandere la tabella unita e selezionare le colonne da includere, selezionare l'icona di espansione ( ).

   Viene visualizzata la finestra Espandi.

   

6. In questo caso, si vuole solo la colonna State Code . Selezionare la colonna, deselezionare Usa nome colonna originale come prefisso e quindi selezionare OK.

   Se fosse stata lasciata la casella di controllo selezionata per Usa nome colonna originale come prefisso, la colonna unita sarà denominata State Codes.State Code.

   Nota

   Per esplorare come inserire la tabella Codici di stato, è possibile sperimentare un po'. Se non si desiderano i risultati, è sufficiente eliminare tale passaggio dall'elenco PASSAGGI APPLICATI nel riquadro Query Impostazioni e la query torna allo stato prima di applicare il passaggio Espandi. È possibile eseguire questa operazione tutte le volte che si desidera fino a quando il processo di espansione non ha l'aspetto desiderato.

   È ora disponibile una singola query (tabella) che combina due origini dati, ognuna delle quali è stata modellata per soddisfare le esigenze. 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.

7. Per applicare le modifiche e chiudere 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.

   

Contenuto correlato

Per altre informazioni su Power BI Desktop e sulle relative funzionalità, vedere le risorse seguenti:

• Che cos'è Power BI Desktop?
• Panoramica sulle query in Power BI Desktop
• Origini di dati in Power BI Desktop
• Connettersi ai dati in Power BI Desktop
• Attività di query comuni in Power BI Desktop