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. 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 un'unica query utile.

In questa esercitazione si apprenderà come:

  • Modellare i dati usando 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 in modo ampio i menu di scelta rapida, nonché 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

Quando si modellano i dati in editor di Power Query, si forniscono istruzioni dettagliate per editor di Power Query da eseguire per modificare i dati durante il caricamento e la visualizzazione. 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 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 vengono sempre modellati nel modo specificato. Questo processo si verifica ogni volta che editor di Power Query o per chiunque usi la query condivisa, ad esempio nel Power BI servizio. Questi passaggi vengono acquisiti, in sequenza, nel riquadro Impostazioni Query in Passaggi applicati. Tutti questi passaggi verranno esaminati nei paragrafi seguenti.

Applied steps in Query Settings

Da Introduzione a Power BI Desktop si useranno i dati relativi al pensionamento, ottenuti con una connessione a un'origine dati Web, per eseguire il data shaping in base alle esigenze. Si aggiungerà una colonna personalizzata per calcolare il rango in base al presupposto che tutti i dati siano fattori uguali e si confronterà questa colonna con la colonna esistente Rank.

  1. Dalla barra multifunzione Aggiungi colonna selezionare Colonna personalizzata, che consente di aggiungere una colonna personalizzata.

    Select Custom Column

  2. Nella finestra Colonna personalizzata, in Nome nuova colonna immettere New Rank. In Formula colonna personalizzata immettere i dati seguenti:

    ([Cost of living] + [Weather] + [Health care quality] + [Crime] + [Tax] + [Culture] + [Senior] + [#"Well-being"]) / 8
    
  3. Verificare che il messaggio di stato sia Non sono stati rilevati errori di sintassi e selezionare OK.

    Custom Column page with no snytanx errors

  4. Per mantenere la coerenza dei dati della colonna, trasformare in numeri interi i nuovi valori della colonna. Per modificarli, fare clic con il pulsante destro del mouse sull'intestazione di colonna e quindi scegliere Modifica tipo Numero intero.

    Se è necessario scegliere più di una colonna, selezionare una colonna, tenere premuto MAIUSC, selezionare altre colonne adiacenti e quindi fare clic con il pulsante destro del mouse su un'intestazione di colonna. È anche possibile usare CTRL per selezionare colonne non adiacenti.

    Select Whole Number column data

  5. Per trasformare i tipi di dati della colonna, in cui si trasforma il tipo di dati corrente in un altro, selezionare Tipo di dati: Testo dalla barra multifunzione Trasforma.

    Select Data Type Text

  6. In Impostazioni query l'elenco Passaggi applicati riflette tutti i passaggi di data shaping applicati ai dati. Per rimuovere un passaggio dal processo di data shaping, selezionare la X a sinistra del passaggio.

    Nell'immagine seguente l'elenco Passaggi applicati riflette i passaggi aggiunti finora:

    • Origine:connessione al sito Web.

    • Tabella estratta da Html:selezione della tabella.

    • Tipo modificato:modifica delle colonne di numeri basati su testo da Testo a Numero intero.

    • Aggiunta di Custom:aggiunta di una colonna personalizzata.

    • Modificato Type1:ultimo passaggio applicato.

      List of Applied Steps

Modificare i dati

Prima di poter usare questa query, è necessario apportare alcune modifiche per sistemare i dati:

  • Modificare le classificazioni rimuovendo una colonna.

    Si è deciso che Cost of living non è un fattore significativo nei risultati. Dopo la rimozione di questa colonna si noterà che i dati rimangono invariati.

  • Correggere alcuni errori.

    Dato che è stata rimossa una colonna, è necessario adattare i calcoli nella colonna New Rank modificando una formula.

  • Ordinare i dati.

    Ordinare i dati in base alle colonne New Rank e Rank.

  • Sostituire i dati.

    Verrà illustrato come sostituire un valore specifico ed evidenziata la necessità di inserire un passaggio applicato.

  • Modificare il nome della tabella.

    Poiché Table 0 non è un descrittore utile per la tabella, il nome verrà modificato.

  1. Per rimuovere la colonna Cost of living, selezionare la colonna, scegliere la scheda Home della barra multifunzione e quindi selezionare Rimuovi colonne.

    Select Remove Columns

    Si noti che i valori di New Rank non sono cambiati, a causa dell'ordine dei passaggi. Poiché editor di Power Query registra i passaggi in sequenza, ma in modo indipendente, l'uno dall'altro, è possibile spostare ogni passaggio applicato verso l'alto o verso il basso nella sequenza.

  2. Fare clic con il pulsante destro del mouse su un passaggio. editor di Power Query un menu che consente di eseguire le attività seguenti:

    • Rinomina: rinominare il passaggio.
    • Elimina:eliminare il passaggio.
    • Eliminafino alla fine:rimuovere il passaggio corrente e tutti i passaggi successivi.
    • Sposta prima di: sposta il passaggio verso l'alto nell'elenco.
    • Sposta dopo: consente di spostare il passaggio verso il basso nell'elenco.
  3. Spostare l'ultimo passaggio, Rimosse colonne, subito sopra il passaggio Aggiunta colonna personalizzata.

    Move up step in Applied Steps

  4. Selezionare il passaggio Aggiunta colonna personalizzata.

    Si noti che i dati ora mostrano un errore che sarà necessario risolvere.

    Error result in column data

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

    Error information in Power Query Editor

    Se si seleziona direttamente la parola Errore, editor di Power Query un passaggio applicato nel riquadro Impostazioni query e visualizza le informazioni sull'errore.

  5. Poiché non è necessario visualizzare informazioni sugli errori, selezionare Annulla.

  6. Per risolvere gli errori, selezionare la colonna New Rank, quindi visualizzare la formula dei dati della colonna selezionando la casella di controllo Barra della formula nella scheda Visualizza.

    Select Formula Bar

  7. Rimuovere il parametro Cost of living e decrementare il divisore, modificando la formula come segue:

     Table.AddColumn(#"Removed Columns", "New Rank", each ([Weather] + [Health care quality] + [Crime] + [Tax] + [Culture] + [Senior] + [#"Well-being"]) / 7)
    
  8. Selezionare il segno di spunta verde a sinistra della casella della formula o premere INVIO.

editor di Power Query sostituisce i dati con i valori revisionati 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, ma in questa esercitazione non è stato fatto perché si volevano mantenere i dati nella tabella.

  1. Ordinare i dati in base alla colonna New Rank. Selezionare prima di tutto l'ultimo passaggio applicato, Tipo modificato 1 per visualizzare i dati più recenti. Selezionare quindi l'elenco a discesa accanto all'intestazione di colonna New Rank e selezionare Ordinamento crescente.

    Sort data in New Rank column

    I dati sono ora ordinati in base a New Rank. Se tuttavia si osserva la colonna Rank, si noterà che i dati non sono ordinati correttamente nei casi in cui il valore di New Rank è un valore equivalente. Il problema verrà risolto nel passaggio successivo.

  2. Per risolvere il problema di ordinamento dei dati, selezionare la colonna New Rank e sostituire la formula nella Barra della formula con la formula seguente:

     = Table.Sort(#"Changed Type1",{{"New Rank", Order.Ascending},{"Rank", Order.Ascending}})
    
  3. Selezionare il segno di spunta verde a sinistra della casella della formula o premere INVIO.

    Le righe sono ora ordinate sia in base a New Rank che a Rank. In Passaggi applicati è anche possibile selezionare un passaggio in un punto qualsiasi dell'elenco e continuare con il data shaping in questo punto della sequenza. editor di Power Query inserisce automaticamente un nuovo passaggio subito dopo il passaggio applicato attualmente selezionato.

  4. In Passaggi applicati selezionare il passaggio che precede la colonna personalizzata, ovvero il passaggio Rimosse colonne. In questo esempio verrà sostituito il valore del rango Weather in Arizona. Fare clic con il pulsante destro del mouse sulla cella appropriata che contiene il rango Weather dell'Arizona e scegliere Sostituisci valori. Notare il passaggio applicato attualmente selezionato.

    Select Replace Values for column

  5. Selezionare Inserisci.

    Poiché si sta inserendo un passaggio, editor di Power Query segnala il rischio di eseguire questa operazione. I passaggi successivi potrebbero causare l'interruzione della query.

    Insert Step verification

  6. Impostare il valore dei dati su 51.

    editor di Power Query sostituisce i dati per Arizona. Quando si crea un nuovo passaggio applicato,editor di Power Query il nome in base all'azione. in questo caso, Sostituito valore. Se nella query sono presenti più passaggi con lo stesso nome, editor di Power Query aggiunge un numero (in sequenza) a ogni passaggio applicato successivo per distinguerli.

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

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

  8. Infine, vien ora modificato il nome della tabella in uno più descrittivo. Nel riquadro Impostazioni query, in Proprietà immettere il nuovo nome della tabella e quindi selezionare Immetti. Assegnare a questa tabella il nome RetirementStats.

    Rename table in Query Settings

    Quando si inizia a creare i report, è utile usare nomi di tabella descrittivi, soprattutto nel caso di una connessione a più origini dati, elencate nel riquadro Campi della vista Report.

    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. Esiste tuttavia un problema: la maggior parte dei dati usa un'abbreviazione di due lettere per i codici relativi allo stato, non il nome completo dello stato. È necessario trovare un modo per associare i nomi degli stati e le rispettive abbreviazioni.

Fortunatamente, è disponibile un'altra origine dati pubblica che esegue proprio questa operazione, ma è necessaria una quantità elevata di data shaping prima che sia possibile connettersi alla tabella relativa al pensionamento. Per modificare la forma dei dati, seguire questa procedura:

  1. Dalla 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 https://en.wikipedia.org/wiki/List_of_U.S._state_abbreviations.

    Lo strumento di navigazione visualizza il contenuto del sito Web.

    Navigator page

  3. Selezionare Codes and abbreviations (Codici e abbreviazioni).

    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. È comunque sufficiente apprendere le procedure seguenti per utilizzare le tabelle. Le relazioni possono tuttavia aiutare a usare rapidamente i dati provenienti 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 Rimuovi prime righe.

    Select Remove Top Rows

    Viene visualizzata la finestra Rimuovi prime righe, in cui è possibile specificare il numero di righe da rimuovere.

    Nota

    Se Power BI Importa accidentalmente le intestazioni di tabella come una riga nella tabella dati, è possibile selezionare Usa la prima riga come intestazione dalla scheda Home o dalla scheda Trasforma sulla barra multifunzione per correggere la tabella.

  2. Rimuovere le ultime 26 righe. Queste righe sono territori statunitensi, che non è necessario includere. Nella barra multifunzione Home selezionare Rimuovi righe Rimuovi righe in basso.

    Select Remove Bottom Rows

  3. Poiché la tabella RetirementStats non contiene informazioni per Washington DC, è necessario escluderla dall'elenco con un filtro. Selezionare l'elenco a discesa Region Status, quindi deselezionare la casella di controllo accanto a Federal district.

    Clear Federal district check box

  4. Rimuovere alcune colonne non necessarie. Poiché è necessario solo il mapping di ogni stato alla relativa abbreviazione ufficiale di due lettere, è possibile rimuovere diverse colonne. Selezionare prima una colonna, quindi tenere premuto CTRL e selezionare ognuna delle altre colonne da rimuovere. Nella scheda Home della barra multifunzione selezionare Rimuovi colonne Rimuovi colonne.

    Remove column

    Nota

    Questo è un buon momento per ricordare che la sequenza di passaggi applicati nel editor di Power Query è importante e può influire sulla forma dei dati. È anche importante valutare l'eventuale impatto di un passaggio su un passaggio successivo. Se si rimuove un passaggio in Passaggi applicati, i passaggi successivi potrebbero non dare gli stessi risultati previsti inizialmente, a causa dell'impatto della sequenza di passaggi della query.

    Nota

    Quando si ridimensiona la finestra editor di Power Query per ridimensionare la larghezza, alcuni elementi della barra multifunzione vengono condensati per usare al meglio lo spazio visibile. Quando si aumenta la larghezza della finestra editor di Power Query, gli elementi della barra multifunzione si espandono per usare 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 dalla scheda Trasforma sulla barra multifunzione oppure fare clic con il pulsante destro del mouse e scegliere Rinomina. L'immagine seguente contiene delle frecce che puntano a entrambe le opzioni; è sufficiente sceglierne solo una.

    Rename column in Power Query Editor

  6. Rinominare le colonne in State Name e State Code. Per rinominare la tabella, immettere il nome nel riquadro Impostazioni query. Assegnare a questa tabella il nome StateCodes.

Combinare le query

Ora che è stata data la forma desiderata alla tabella StateCodes, 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.

  • Quando sono presenti una o più colonne da aggiungere a un'altra query, è consigliabile eseguire il merge delle query.
  • Quando sono presenti righe aggiuntive di dati da aggiungere a una query esistente, è consigliabile accodare la query.

In questo caso si usa l'unione delle query. A questo scopo, attenersi alla procedura seguente:

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

  2. Selezionare Merge Queries Merge Queries (Unisci query) nella scheda Home della barra multifunzione.

    Select Merge Queries

    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.

  3. Selezionare State dalla tabella RetirementStats, quindi selezionare la query StateCodes.

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

    Merge window

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

    NewColumn column

  5. Per espandere la tabella sottoposta a merge e selezionare le colonne da includere, fare clic sull'icona di espansione (Expand icon).

    Verrà visualizzata la finestra Espandi.

    NewColumn in query

  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 Usa il nome della colonna originale come prefisso fosse stata lasciata selezionata, il nome della colonna sottoposta a merge sarebbe NewColumn.State Code.

    Nota

    È possibile scegliere diversi modi per inserire la tabella NewColumn. È possibile fare alcune prove e, se i risultati non sono quelli desiderati, sarà sufficiente eliminare il passaggio dall'elenco Passaggi applicati nel riquadro Impostazioni query. La query tornerà allo stato precedente all'applicazione del 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 modellata in modo da soddisfare le esigenze specifiche. Questa query può essere usata come base per diverse connessioni dati aggiuntive e interessanti, ad esempio le statistiche relative ai costi delle abitazioni, i dati demografici o le opportunità di lavoro in ogni 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.

    Select Close & Apply

Passaggi successivi

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