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

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

In questa esercitazione si apprenderà come:

  • Modellare i dati utilizzando 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.

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.

Modellazione di 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 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 da editor di Power Query. Ogni volta che questa query si connette all'origine dati, editor di Power Query esegue tali 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 Impostazioni query , in PASSAGGI APPLICATI. Questi passaggi verranno illustrati in questo articolo.

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

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

    Screenshot di editor di Power Query con il menu Recupera dati e l'origine Web selezionata.

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

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

    Screenshot della finestra di dialogo Da Web di editor di Power Query con l'URL della pagina di origine immesso.

  3. Nella finestra di dialogo Strumento di navigazione selezionare Table 1, quindi scegliere Trasforma dati.

    Screenshot della finestra di dialogo Strumento di navigazione di editor di Power Query con la tabella HTML 1 selezionata e il pulsante Trasforma dati evidenziato.

  4. Verrà visualizzata la finestra 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 di dati in intestazioni di colonna.
    • Modifica tipo: modifica dei tipi di colonna importati come testo nei tipi dedotti.

    Screenshot della finestra editor di Power Query con Impostazioni query evidenziate.

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

    Screenshot di editor di Power Query che mostra come modificare un nome di tabella in Impostazioni query.

  6. 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. La tabella verrà quindi ordinata in questa colonna per confrontare la classificazione del punteggio personalizzato con la classificazione esistente.

  7. Nella barra multifunzione Aggiungi colonna selezionare Colonna personalizzata.

    Screenshot della barra multifunzione Aggiungi colonna di editor di Power Query con il pulsante Colonna personalizzata evidenziato.

  8. 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
    
  9. Verificare che il messaggio di stato sia Non sono stati rilevati errori di sintassi e selezionare OK.

    Screenshot della finestra di dialogo Colonna personalizzata di editor di Power Query che mostra il nome della nuova colonna, la formula della colonna personalizzata e nessun errore di sintassi.

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

    Screenshot del riquadro Impostazioni query di editor di Power Query che mostra l'elenco Passaggi applicati con le azioni finora.

Modificare i dati

Prima di lavorare con questa query, è possibile apportare alcune modifiche per modificare 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 con 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.

    Screenshot del menu Home di editor di Power Query con il pulsante Rimuovi colonne evidenziato.

    Nota

    I valori del nuovo 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.

    Screenshot del menu di scelta rapida Passaggi applicati di editor di Power Query.

  3. Spostare l'ultimo passaggio, Rimosse colonne, subito sopra il passaggio Aggiunta colonna personalizzata.

    Screenshot dell'elenco Passaggi applicati di editor di Power Query con il passaggio Colonne rimosse ora spostato sopra il passaggio Colonna personalizzata.

  4. Selezionare il passaggio Aggiunta colonna personalizzata.

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

    Screenshot di editor di Power Query e della colonna Nuovo punteggio contenente i valori di errore.

    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.

    Screenshot di editor di Power Query che mostra la colonna Nuovo punteggio con i dettagli dell'errore.

    Se si seleziona direttamente la parola Errore, editor di Power Query crea un passaggio applicato nel riquadro Impostazioni query e visualizza informazioni sull'errore. Poiché non è necessario visualizzare le informazioni sugli errori 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 sarà simile alla seguente:

      Screenshot della finestra di dialogo Colonna personalizzata di editor di Power Query con errori di formula corretti.

    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 .

      Screenshot di editor di Power Query che mostra la colonna Nuovo punteggio e la relativa formula di dati con errori corretti.

      Modificare la formula come descritto in precedenza, fino a quando non è simile a questa, 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 rivisti e il passaggio Aggiunta 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 conservare 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 Ordina decrescente.

    Screenshot di editor di Power Query che mostra la colonna Nuovo punteggio con Ordinamento decrescente evidenziato.

    I dati vengono ora ordinati in base al nuovo punteggio. È possibile selezionare un passaggio applicato in qualsiasi punto 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 precedente alla colonna personalizzata, ovvero il passaggio Rimuovi colonne . Qui si sostituirà il valore della classifica dei costi di alloggio in Oregon. Fare clic con il pulsante destro del mouse sulla cella appropriata contenente il valore del costo dell'alloggio dell'Oregon e quindi selezionare Sostituisci valori. Notare il passaggio applicato attualmente selezionato.

    Screenshot della finestra editor di Power Query che mostra la colonna Costi di alloggio con la voce di menu Sostituisci valori evidenziata con il pulsante destro del mouse.

  8. Selezionare Inserisci.

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

    Screenshot della finestra di dialogo Inserisci verifica passaggio di editor di Power Query.

  9. Modificare il valore dei dati su 100.0.

    editor di Power Query sostituisce i dati per l'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 a ogni passaggio applicato successivo.

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

    Si noti che i dati sono stati modificati in merito alla nuova classificazione dell'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 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 trovare un modo per associare i nomi degli stati e le rispettive abbreviazioni.

Esiste un'altra origine dati pubblica che fornisce tale associazione, ma necessita di una quantità equa di forma 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 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.

    Screenshot della pagina Navigator di editor di Power Query che mostra la tabella Codici e abbreviazioni selezionata.

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

    Suggerimento

    Sarà necessario un po' di forma per ridimensionare i dati di questa tabella fino a ciò che si vuole. C'è un modo più veloce o più semplice per eseguire i passaggi seguenti? 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 informazioni sull'uso delle tabelle. Tuttavia, le relazioni consentono di usare rapidamente i dati da più tabelle.

Per modificare la forma dei dati, seguire questa procedura:

  1. 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 > principali.

    Screenshot di editor di Power Query evidenziando l'elenco a discesa Rimuovi righe e l'elemento Rimuovi righe principali.

    Viene visualizzata la finestra di dialogo Rimuovi righe principali . Specificare 1 riga da rimuovere.

  2. Promuovere la nuova riga superiore alle intestazioni con Use First Row As Headers dalla scheda Home oppure dalla scheda Trasforma nella barra multifunzione.

  3. Poiché la tabella Dati di ritiro non dispone di 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 dello stato.

    Screenshot di editor di Power Query che mostra un filtro di colonna con solo il valore Di stato selezionato.

  4. Rimuovere tutte le colonne non richieste. Poiché è necessario solo il mapping di ogni stato alla relativa abbreviazione di due lettere (nome e stato dell'area e colonne ANSI ), è possibile rimuovere le altre colonne. Prima di tutto selezionare il nome e lo stato della colonna area, quindi tenere premuto il tasto CTRL e selezionare la colonna ANSI . Nella scheda Home della barra multifunzione selezionare Rimuovi colonne > Rimuovi altre colonne.

    Screenshot di editor di Power Query evidenziando l'elenco a discesa Rimuovi colonne e l'elemento Rimuovi altre colonne.

    Nota

    La sequenza di passaggi applicati in editor di Power Query è importante e influisce sul modo in cui i dati vengono modellati. È 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 originariamente previsti.

    Nota

    Quando si ridimensiona la finestra editor di Power Query per rendere la larghezza più piccola, 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 la colonna, quindi selezionare Rinomina nella scheda Trasforma sulla barra multifunzione oppure fare clic con il pulsante destro del mouse e selezionare Rinomina. L'immagine seguente mostra entrambe le opzioni, ma è sufficiente sceglierne una.

    Screenshot di editor di Power Query evidenziando il pulsante Rinomina e anche l'elemento Rinomina con il pulsante destro del mouse.

  6. Rinominare le colonne in State Name e State Code. Per rinominare la tabella, immettere i codici di statodei nomi nel riquadro Impostazioni query.

    Screenshot della finestra editor di Power Query che mostra i risultati della forma dei dati dell'origine codici di stato in una tabella.

Combinare le query

Dopo aver modellato la tabella Codici di stato in modo che si vuole, è possibile combinare queste due tabelle o query in una. 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 che si desidera 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 Merge Query di tipo Merge Query > dalla scheda Home della barra multifunzione.

    Screenshot dell'elenco a discesa Merge Query di editor di Power Query con l'elemento Merge Querys evidenziato.

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

    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.

  3. Selezionare Stato nella tabella Dati di ritiro e quindi selezionare la query Codici di stato.

    Quando si seleziona una colonna corrispondente, il pulsante OK è abilitato.

    Screenshot della finestra di dialogo Merge di editor di Power Query.

  4. Selezionare 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 unite 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 ( ).

    Verrà visualizzata la finestra Espandi.

    Screenshot della finestra di dialogo Espandi colonna di editor di Power Query che mostra la colonna Codice stato evidenziata.

  6. 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 è stata lasciata selezionata per Usare il nome della colonna originale come prefisso, la colonna unita sarà denominata Codici di stato.Codice stato.

    Nota

    Se si vuole esplorare come inserire la tabella Codici di stato , è possibile sperimentare un po'. Se i risultati non sono simili, eliminare questo passaggio dall'elenco PASSAGGI APPLICATI nel riquadro Impostazioni query e la query viene restituita allo stato prima di applicare il 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 modellata per soddisfare le esigenze. Questa query può essere una base per connessioni dati interessanti, ad esempio statistiche sui costi abitativi, qualità della vita o tasso di criminalità in qualsiasi stato.

  7. Per applicare le modifiche e chiudere editor di Power Query, selezionare Chiudi & applica nella scheda Home della barra multifunzione.

    Il set di dati trasformato viene visualizzato in Power BI Desktop, pronto per l'uso nella creazione di report.

    Screenshot del pulsante Chiudi & applica editor di Power Query.

Passaggi successivi

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