- Power Query consente di consolidare più file da una cartella locale o cloud, purché condividano lo stesso schema di dati.
- L'utilizzo di un file di esempio semplifica l'applicazione di trasformazioni in blocco, come la rimozione delle righe superiori o la modifica delle intestazioni.
- Si verificano delle limitazioni quando le colonne non corrispondono esattamente, il che richiede una pulizia manuale o l'utilizzo di script VBA per i casi più complessi.
Immaginate di avere una serie di report mensili o settimanali sparsi in diverse cartelle e, invece di sprecare la mattinata a copiare e incollare freneticamente i dati, di poterli unificare in un unico foglio di calcolo Excel con pochi clic. È proprio qui che entra in gioco Power Query, uno strumento che si rivela un valido alleato per chiunque lavori con i dati, consentendo di consolidare senza sforzo file con la stessa struttura.
L'aspetto più interessante di questo sistema è che non ti limita a una cartella locale sul tuo PC; puoi estrarre dati da siti SharePoint, Azure Data Lake o blob di Azure . La chiave per un funzionamento impeccabile è che i file abbiano lo stesso schema, ovvero che le colonne corrispondano, anche se Power Query è abbastanza intelligente da ripulire i dati se contengono informazioni superflue o righe non necessarie.
Il processo di combinazione passo passo
Quando ci si connette a un'origine dati, la prima cosa che si vede è la visualizzazione del file system. Qui, la colonna più importante è Contenuto, che contiene la rappresentazione binaria di ciascun documento. Se si fa clic su uno di questi valori binari, il programma tenterà di determinare se si tratta di un file CSV, JSON o Excel per poterlo aprire. Per unire l'intero batch, si hanno due opzioni: fare clic direttamente sul pulsante Unisci oppure, se si desidera un'operazione più precisa, selezionare Trasforma dati per filtrare l'elenco dei file (ad esempio, per ignorare cartelle specifiche) prima di procedere con l'unione.
Una volta aperta la finestra di dialogo di unione, Power Query utilizza un file di esempio per analizzare la struttura . Per impostazione predefinita, seleziona il primo file nell'elenco, ma è possibile modificarlo se ne esiste un altro che rappresenti meglio il set di dati. Qui è possibile selezionare l'opzione per ignorare i file con errori , una funzione fondamentale quando un report è danneggiato o vuoto, evitando così l'interruzione dell'intero processo.
Gestione dell'output e pulizia dei dati
Dopo aver accettato l'unione, il sistema esegue alcune operazioni tecniche in background: crea una query di esempio e una funzione parametrizzata. Ciò significa che qualsiasi modifica apportata al file di esempio verrà automaticamente replicata in tutti gli altri file della cartella. Se, ad esempio, i file CSV contengono quattro righe di intestazioni non necessarie prima dei dati effettivi, è possibile accedere alla query di trasformazione e utilizzare l'opzione per rimuovere le prime righe ed eliminare questo elemento superfluo.
Dopo aver pulito le righe, è fondamentale applicare la funzione " Usa la prima riga come intestazione" . Tieni presente che, in questo caso, Power Query solitamente aggiunge un passaggio di conversione automatica del tipo di dati. Se, tornando alla query principale, visualizzi un errore che indica che la colonna 'Colonna1' non è stata trovata, non preoccuparti: è normale perché nell'esempio hai rinominato le colonne e il passaggio successivo non riconosce più i vecchi nomi. Elimina semplicemente il passaggio errato dal riquadro Passaggi applicati e tutto tornerà alla normalità.
Sfide comuni e alternative avanzate
A volte la vita non è così semplice e ci imbattiamo in file non identici. Ad esempio, se si hanno report di ServiceNow in cui un'intestazione si chiama "resolved_on" e un'altra "closed_on", Power Query potrebbe dare problemi perché richiede una struttura coerente . Se il file di esempio non corrisponde esattamente agli altri, lo strumento potrebbe omettere le informazioni dai file non corrispondenti o generare errori di importazione.
Nei casi in cui la struttura è completamente caotica o il volume dei dati è enorme (superiore a un milione di righe Excel), esistono altre opzioni. Alcuni utenti optano per script VBA che utilizzano interfacce CSV , le quali consentono di unire i file senza ripetere le intestazioni, indipendentemente dal fatto che il delimitatore di campo o di record vari da un file all'altro. Sebbene la shell di Windows sia estremamente veloce, spesso non riesce a gestire le interruzioni di riga incorporate, mentre una solida soluzione VBA non sovraccarica la RAM e può gestire milioni di record con facilità.
Per completare il processo, una volta combinati i dati in Power Query, ricordati di assegnare il tipo di dati corretto a ciascuna colonna (Data per le date, Valuta per i ricavi, ecc.) prima di caricare la tabella finale. Puoi verificare che tutto sia corretto filtrando la colonna Nome origine per assicurarti che tutti i file nella cartella siano stati elaborati correttamente.
La possibilità di automatizzare l'unione di più file CSV consente di passare da un noioso lavoro manuale a un flusso di lavoro professionale, sfruttando la flessibilità di Power Query per la pulizia dei dati o ricorrendo al VBA quando le strutture sono incompatibili o i volumi superano i limiti dei fogli di calcolo.