Come importare un file JSON in Excel con Power Query

In questa guida vediamo come importare un file JSON in Excel utilizzando Power Query.

Per fare un test utilizzeremo un dataset un po' diverso dal solito: tutte le 64 partite dei Mondiali di calcio del 2006, con squadre, risultati, fase del torneo e informazioni sugli stadi. Diciamo che, se siete appassionati, la scelta dell'edizione non è casuale, ma i dati sportivi sono un buon esempio per lavorare su JSON ed espandere i concetti.

L'obiettivo è partire da un formato pressochè incomprensibile come JSON, almeno per i non addetti ai lavori, per arrivare a una normale tabella Excel, con una riga per ogni partita e le informazioni che ci interessano già separate in colonne.

NOTA BENE: i dati utilizzati sono reali e provengono dal Fjelstul World Cup Database. Li ho selezionati e riorganizzati in JSON proprio per rendere il file più utile per questo esempio, fonte e licenza sono indicate anche all'interno del file.

Premessa - non sono uno sviluppatore, la spiegazione del formato del file può essere migliorata ma può dare, a chi non l'abbia mai incrociato, un'idea di alto livello del perchè JSON sia così rilevante nel mondo dei dati.

Come è strutturato un file JSON

Se lavori abitualmente con CSV o file Excel, un JSON può risultare un po' strano la prima volta che lo apri. In JSON abbiamo tutte le informazioni sulle partite, ma non sono già organizzate nella classica struttura a righe e colonne.

Diciamo che, rispetto a quello che facciamo in Excel, JSON richiede più astrazione. Ad esempio, per una singola partita troviamo informazioni sulla squadra di casa, sulla squadra in trasferta, sul risultato e sullo stadio. Fin qui, è come Excel. Ognuno di questi elementi, però, può contenere a sua volta altri dati, come se lavorassimo con una specie di modello dei dati.

Prendiamo il caso di Italia - Ghana: prima partita del girone per l'Italia nel girone E, finita con la vittoria dell'Italia per 2-0 e giocata ad Hannover (in inglese Hanover) il 12 Giugno del 2006.

In Excel, una versione semplificata potrebbe essere fatta così:

SQUADRA_CASA	SQUADRA_TRASFERTA	DATA	STADIO	GIRONE	GOL_CASA	GOL_TRASFERTA	SQUADRA_VINCITRICE
ITALIA	GHANA	12/06/2006	HANOVER	E	2	0	ITALIA

Chiaramente, questa è una visione semplificata della partita. Ad esempio, potremmo voler sapere:

• Se ci siano stati dei rigori
• Goal segnati nel primo tempo
• Goal segnati nel secondo tempo
• Come si chiama lo stadio
• Ammoniti o espulsi
• Nome dell'arbitro e relativa nazionalità

Potremmo andare avanti all'infinito e in Excel questo file diventerebbe veramente complicato da gestire. Avremmo un numero indeterminato di colonne poco manovrabili.

JSON ha il vantaggio di poter contenere una grande mole di dati, pur rimanendo in un formato compatto. Ad esempio, potresti vedere:

{
  "id_partita": 11,
  "data": "2006-06-12",
  "ora": "21:00",
  "fase": "Fase a gironi",
  "gruppo": "E",
  "stadio": {
    "nome": "Niedersachsenstadion",
    "citta": "Hanover",
    "paese": "Germania"
  },
  "casa": {
    "squadra": "Italy",
    "codice": "ITA"
  },
  "trasferta": {
    "squadra": "Ghana",
    "codice": "GHA"
  },
  "risultato": {
    "gol_casa": 2,
    "gol_trasferta": 0,
    "tempi_supplementari": false,
    "rigori": false,
    "rigori_casa": null,
    "rigori_trasferta": null
  },
  "vincitore": "Italy"
}

Certo, il file è quasi comprensibile, ma bisogna andare per gradi, dato che nel JSON abbiamo diversi livelli di informazioni. Capiamo che tutti i dati sono collegati con una sola partita, infatti il primo livello contiene i dati principali della partita:

• id_partita
• data
• ora
• fase
• gruppo
• stadio
• casa
• trasferta
• risultato
• vincitore

Alcuni di questi campi contengono direttamente un valore. Fin qui, il ragionamento è uguale a una tabella in Excel o a un qualunque dataset. Ad esempio:

"data": "2006-06-12",
"fase": "Fase a gironi",
"gruppo": "E"

Era il 12 di Giugno del 2006, fase a gironi del gruppo E. Ci siamo. In questi casi la struttura è piuttosto semplice: abbiamo il nome del campo (intestazione) e il relativo valore. Questi valori sono immediatamente comprensibili.

Altri campi contengono invece ulteriori informazioni. Prendiamo casa:

"casa": {
  "squadra": "Italy",
  "codice": "ITA"
}

casa non contiene un singolo valore, ma un altro oggetto JSON composto da due campi: squadra e codice. Quindi casa ha due attributi: è sia squadra = Italy sia codice = ITA e possiamo richiamare entrambe le informazioni, se richiesto.

Lo stesso succede con trasferta:

"trasferta": {
  "squadra": "Ghana",
  "codice": "GHA"
}

Diciamo che le parentesi graffe { e } servono per incapsulare nuove informazioni, in modo da aumentare le ramificazioni e gli attributi delle singole informazioni che troviamo nel dataset.

Anche stadio contiene un secondo livello:

"stadio": {
  "nome": "Niedersachsenstadion",
  "citta": "Hanover",
  "paese": "Germania"
}

In questo caso abbiamo tre informazioni che appartengono allo stesso elemento. La partita è stata in Germania, lo stadio si chiama Niedersachsenstadion e la città è Hanover.

Il record più articolato è quello dedicato al risultato:

"risultato": {
  "gol_casa": 2,
  "gol_trasferta": 0,
  "tempi_supplementari": false,
  "rigori": false,
  "rigori_casa": null,
  "rigori_trasferta": null
}

La partita è finita 2 a zero, non ci sono stati supplementari, non ci sono stati rigori di nessun tipo. Certo, potresti obiettare che non potevano esserci i supplementari perchè eravamo nella fase a gironi, ma il record esiste lo stesso. In questo modo, la stessa struttura può essere usata anche per la fase a eliminazione diretta.

Vediamo però che qui troviamo anche tipi di valore diversi:

• gol_casa e gol_trasferta sono numeri
• tempi_supplementari e rigori contengono invece valori booleani, quindi true oppure false.

Infine abbiamo null, che indica l'assenza di un valore. In questa partita non sono stati disputati i rigori, quindi rigori_casa e rigori_trasferta non hanno un risultato da riportare.

Questa struttura ci aiuta anche a capire quello che vedremo tra poco in Power Query. I campi semplici potranno essere utilizzati direttamente, mentre gli oggetti come casa, trasferta, stadio e risultato dovranno essere espansi per recuperare le singole informazioni contenute al loro interno. Chiaramente non vogliamo lavorare direttamente su questa struttura. Power Query ci permette di leggerla e di scegliere quali informazioni trasformare in colonne.

Vediamo come fare.

Come importare un file JSON in Excel

Per prima cosa dobbiamo recuperare il nostro file JSON: vai nella scheda Dati, seleziona Recupera dati > Da file > Da JSON e scegli il file da analizzare.

In questo caso, come detto, useremo un file chiamato MONDIALI_2006.JSON. Il file contiene tutte le partite disputate e altre informazioni interessanti.

Power Query leggerà il contenuto del file e ci mostrerà la struttura disponibile. Già da qui, iniziamo a vedere qualcosa di diverso rispetto alla classica importazione di un CSV.

Vediamo infatti:

• attribution: Record
• torneo: Record
• fonte: Record
• partite: List

Cambio inoltre il nome della query in TBL_PARTITE_MONDIALI_CALCIO_2006.

Notare il codice generato che è =Json.Document(File.Contents(...)). Power Query definisce che quello che stiamo per importare è un documento Json e, se volessimo, potremmo anche definire un encoding (di default UTF8).

Che cosa sono Record e List in Power Query

Una volta importato il JSON, come detto, Power Query ci mostra alcuni elementi chiamati Record e List. Li incontrerai molto spesso quando lavori con questo tipo di file ed è un concetto chiave per Power Query, anche se poco considerato.

Nel nostro caso, la List che ci interessa contiene tutte le partite dei Mondiali. Abbiamo 64 partite e quindi 64 elementi, quindi possiamo già aspettarci 64 righe una volta trasformati i dati. Le liste in Power Query sono sfruttate per ottimizzare i calcoli e ti capiterà spesso di trovarle, soprattutto quando lavori con dataset grandi.

I Record contengono invece più informazioni relative allo stesso elemento e riprendono il concetto che citavamo prima - ad esempio, per la squadra considerata di casa non abbiamo solamente il nome della nazionale, ma anche altre informazioni associate.

Ad esempio:

• Nome: Italia
• Codice: ITA

Power Query raggruppa queste informazioni in un Record. Lo stesso succede con il risultato, lo stadio e altri elementi presenti nel nostro file. A livello operativo ci interessa sapere soprattutto questo:

• una List contiene più elementi;
• un Record contiene più informazioni relative a un elemento.

Facciamo un esempio pratico. Se clicchi su Record in torneo, vedrai che ti appariranno una serie di informazioni. Ci sarà anche un'ulteriore record, capocannoniere. Questo indica che capocannoniere avrà più valori all'interno, mentre tutti gli altri dati sono più semplici avendo un solo valore.

Non ci stupisce vedere che, infatti, ci siano giocatore e gol: rispettivamente, Miroslav Klose e 5.

Cliccando poi sul Record, potremmo espandere i due campi. Il passaggio in questo caso è anche facile da leggere, visto che sarebbe =torneo[capocannoniere] - una volta incamerato il file JSON, possiamo muoverci con facilità per ottenere i dati inseriti all'interno.

Andando sull'editor avanzato di Power Query da Home > Editor avanzato, vedrai come i passaggi siano proprio questi:

• Prima selezioniamo il file che vogliamo riportare in Power Query
• A quel punto iniziamo a navigare e selezioniamo il torneo
• Cliccando sul Record, troviamo anche il capocannoniere e le relative informazioni

Vediamo come trasformarli.

Come trasformare una List in tabella

A questo punto vediamo come passare da un file JSON a un file utilizzabile, utilizzando Power Query. In questo caso ci interessano le partite, quindi le selezioniamo.

Cliccando su List in partite, vedremo ben 64 elementi che sono le 64 partite del Mondiale 2006.

Cliccando sul numero di riga, vedresti tutti i dati collegati con quello specifico id_partita: in questo caso la partita inaugurale, che vede la Germania come vincitrice e che è parte del gruppo A. Tuttavia, vari campi non sono visualizzabili - ad esempio, stadio, casa, trasferta o risultato.

A questo punto, dobbiamo cliccare su Strumenti per gli elenchi > Con la tabella. L'elenco sarà convertito in tabella e sarà quindi utilizzabile.

Power Query ci permetterà di scegliere il delimitatore e come gestire le colonne aggiuntive. Mettiamo nessuno e di mostrare come errori.

Ora ci siamo quasi - come noterai possiamo espandere la colonna per ottenere ulteriori informazioni. Il JSON non è più così incomprensibile.

Cliccando sulle due frecce, vedrai tutti i dati e potrai quindi espanderli a piacimento. Noi prenderemo tutte le colonne, ma potresti scegliere di usarne solo alcune.

A questo punto avremo i nostri dati utilizzabili!

Come espandere i Record in Power Query

Dopo la prima espansione iniziamo finalmente a vedere i singoli campi disponibili per ogni partita. Alcuni sono già valori utilizzabili, mentre altri sono ancora Record. Perchè?

Succede ad esempio con:

• squadra di casa;
• squadra in trasferta;
• risultato;
• stadio.

Prendiamo lo stadio.

Se selezioniamo la riga 11, che è la partita Italia - Ghana, vediamo come lo stadio sia composto da diversi campi:

• Nome
• Città
• Paese

Se clicchiamo sul Record, possiamo vedere i tre valori che lo compongono. Tuttavia, questi valori non sarebbero utilizzabili se caricassimo il nostro JSON immediatamente, dato che vedremmo esclusivamente [Record]. Quindi possiamo espandere e verificare le ramificazioni direttamente in Power Query, ma la stessa flessibilità non viene replicata poi in Excel.

Chiaramente possiamo quindi espandere le singole sezioni, come in questo caso le informazioni aggiuntive per lo stadio.

Espansa questa parte, avremo anche sul nostro Excel i dati ascrivibili alla singola partita per lo stadio - altrimenti, come anticipato, non sarebbero consultabili.

In generale, l'aspetto comodo è che non siamo obbligati a recuperare tutto. Se nel JSON ci sono informazioni che non ci servono, possiamo semplicemente lasciarle fuori dalla tabella finale e, nel caso, recuperarle successivamente.

Come verificare i dati da JSON a Excel

La finale dei Mondiali 2006 (scelta non casuale, visti anche i disastri recenti) ci permette di vedere bene perché all'interno di un JSON possiamo avere più informazioni annidate. La partita tra Italia e Francia terminò 1-1 dopo i tempi supplementari, ma l'Italia vinse 5-3 ai rigori.

Se guardassimo solamente le colonne dedicate ai gol, avremmo quindi:

• Italia: 1
• Francia: 1

Il file contiene però anche le informazioni necessarie per rappresentare il risultato ai rigori. Espansi tutti i valori, vediamo come ci siano squadra e squadra.1 che rispettivamente sono casa e trasferta. Abbiamo anche i tempi supplementari (con verifica TRUE o FALSE, per verificare se ci siano stati o meno) e rigori (stessa condizione). Abbiamo poi un dato numerico invece per i rigori finali e l'indicazione della squadra vincitrice.

Possiamo decidere se mantenere queste colonne oppure escluderle, a seconda dell'analisi che vogliamo realizzare. Arrivati a questo punto, darei sempre un'occhiata anche ai tipi di dati visto che Power Query prova a riconoscerli in automatico, ma conviene controllare che il risultato sia coerente.

Nel nostro esempio avremo, tra gli altri:

• Data come Data;
• Squadra casa come Testo;
• Squadra trasferta come Testo;
• Gol casa come Numero intero;
• Gol trasferta come Numero intero.

È un controllo semplice, soprattutto prima di utilizzare le colonne per calcoli, filtri, Tabelle Pivot o grafici.

Come caricare i dati in Excel

Finite le trasformazioni, possiamo selezionare Home > Chiudi e carica. Power Query creerà una normale tabella Excel con una riga per ognuna delle 64 partite. Da qui possiamo utilizzare i dati come qualsiasi altra tabella.

Potremmo, ad esempio:

• creare una Tabella Pivot con i gol segnati dalle diverse nazionali;
• contare quante partite sono state disputate in ogni fase;
• analizzare le partite terminate ai rigori

Anche in questo caso vedrai i risultati per la finale e saranno riportati esattamente come li vedevamo in Power Query - per casi booleani (VERO vs FALSO), potresti anche voler inserire una checkbox per migliorare la visualizzazione.

Un punto di attenzione sui Record, dato che possiamo chiaramente scegliere anche di non espandere i Record. Qui, per esempio, ho evitato di espandere i Record per casa ma il risultato non è particolarmente utile perchè avremo solo il valore [Record]. Bisogna quindi scegliere accuratamente quali informazioni restituire, proprio per evitare colonne incomprensibili come questa.

Credo sia una specifica utile perchè potresti pensare che, avendo più valori, Excel / Power Query restituiscano solo il primo ma invece, come abbiamo visto, non ne viene restituito nessuno.

Conclusione

Abbiamo visto come recuperare un file JSON in Excel e sistemarne la struttura con Power Query. Il nostro file di partenza conteneva diversi livelli di informazioni, tra List e Record, all'apparenza inutilizzabili - mentre alla fine abbiamo ottenuto una normale tabella con tutte le 64 partite dei Mondiali 2006.

Se vuoi fare qualche test, prova a partire dallo stesso file e a mantenere colonne diverse rispetto a quelle utilizzate nella guida!