Power Query può essere di grande aiuto quando si desidera combinare più cartelle di lavoro in un'unica cartella di lavoro.
Si supponga, ad esempio, di disporre dei dati sulle vendite per diverse regioni (Est, Ovest, Nord e Sud). Puoi combinare questi dati da diverse cartelle di lavoro in un singolo foglio di lavoro usando Power Query.
Se disponi di queste cartelle di lavoro in posizioni/cartelle diverse, è una buona idea spostarle tutte in un'unica cartella (o creare una copia e inserire quella copia della cartella di lavoro nella stessa cartella).
Quindi, per cominciare, ho quattro cartelle di lavoro in una cartella (come mostrato di seguito).
Ora, in questo tutorial, tratterò tre scenari in cui è possibile combinare i dati di diverse cartelle di lavoro utilizzando Power Query:
- Ogni cartella di lavoro ha i dati in una tabella Excel e tutti i nomi delle tabelle sono gli stessi.
- Ogni cartella di lavoro ha i dati con lo stesso nome del foglio di lavoro. Questo può essere il caso in cui è presente un foglio denominato "riepilogo" o "dati" in tutte le cartelle di lavoro e si desidera combinare tutti questi elementi.
- Ogni cartella di lavoro ha molti fogli e tabelle e si desidera combinare tabelle/fogli specifici. Questo metodo può essere utile anche quando si desidera combinare tabelle/fogli che non hanno un nome coerente.
Vediamo come combinare i dati di queste cartelle di lavoro in ogni caso.
Ogni cartella di lavoro ha i dati in una tabella Excel con la stessa struttura
La tecnica seguente funzionerebbe quando le tue tabelle Excel sono state strutturate allo stesso modo (stessi nomi di colonna).
Il numero di righe in ogni tabella può variare.
Non preoccuparti se alcune tabelle di Excel hanno colonne aggiuntive. Puoi scegliere una delle tabelle come modello (o come "chiave" come la chiama Power Query) e Power Query la userebbe per combinare tutte le altre tabelle di Excel con essa.
Nel caso in cui siano presenti colonne aggiuntive in altre tabelle, queste verranno ignorate e verranno combinate solo quelle specificate nel modello/chiave. Ad esempio, se la tabella modello/chiave selezionata ha 5 colonne e una delle tabelle in un'altra cartella di lavoro ha 2 colonne aggiuntive, quelle colonne aggiuntive verranno ignorate.
Ora ho quattro cartelle di lavoro in una cartella che voglio combinare.
Di seguito è riportato un'istantanea della tabella che ho in una delle cartelle di lavoro.
Ecco i passaggi per combinare i dati di queste cartelle di lavoro in un'unica cartella di lavoro (come una singola tabella).
- Vai alla scheda Dati.
- Nel gruppo Ottieni e trasforma, fai clic sul menu a discesa Nuova query.
- Passa il cursore su "Da file" e fai clic su "Da cartella".
- Nella finestra di dialogo Cartella, inserisci il percorso del file della cartella che contiene i file oppure fai clic su Sfoglia e individua la cartella.
- Fare clic su OK.
- Nella finestra di dialogo che si apre, fai clic sul pulsante Combina.
- Fare clic su "Combina e carica".
- Nella finestra di dialogo "Combina file" che si apre, seleziona la tabella nel riquadro di sinistra. Nota che Power Query ti mostra la tabella dal primo file. Questo file fungerebbe da modello (o chiave) per combinare altri file. Power Query ora cercherà "Tabella 1" in altre cartelle di lavoro e la combinerà con questa.
- Fare clic su OK.
Questo caricherà il risultato finale (dati combinati) nel tuo foglio di lavoro attivo.
Si noti che insieme ai dati, Power Query aggiunge automaticamente il nome della cartella di lavoro come prima colonna dei dati combinati. Questo aiuta a tenere traccia di quali dati provengono da quale cartella di lavoro.
Nel caso in cui desideri modificare prima i dati prima di caricarli in Excel, al passaggio 6, seleziona "Combina e modifica". Verrà aperto il risultato finale nell'editor di Power Query in cui è possibile modificare i dati.
Alcune cose da sapere:
- Se si seleziona una tabella di Excel come modello (nel passaggio 7), Power Query utilizzerà i nomi delle colonne in questa tabella per combinare i dati di altre tabelle. Se altre tabelle hanno colonne aggiuntive, queste verranno ignorate. Nel caso in cui quelle altre tabelle non dispongano di una colonna, che è presente nella tabella modello, Power Query metterebbe semplicemente "null" per essa.
- Non è necessario che le colonne siano nello stesso ordine in cui Power Query usa le intestazioni di colonna per mappare le colonne.
- Poiché hai selezionato Table1 come chiave, Power Query cercherà Table1 in tutte le cartelle di lavoro e le combinerà. Nel caso in cui non trovi una tabella Excel con lo stesso nome (Table1 in questo esempio), Power Query ti darà un errore.
Aggiunta di nuovi file alla cartella
Ora prendiamoci un minuto e capiamo cosa abbiamo fatto con i passaggi precedenti (che ci hanno impiegato solo pochi secondi).
Abbiamo combinato i dati di quattro diverse cartelle di lavoro in un'unica tabella in pochi secondi senza nemmeno aprire nessuna cartella di lavoro.
Ma non è tutto.
Il vero POTERE di Power Query è che ora quando aggiungi più file alla cartella, non è necessario ripetere nessuno di questi passaggi.
Tutto ciò che devi fare è spostare la nuova cartella di lavoro nella cartella, aggiornare la query e combinerà automaticamente i dati di tutte le cartelle di lavoro in quella cartella.
Ad esempio, nell'esempio sopra, se aggiungo una nuova cartella di lavoro - "Mid-West.xlsx" nella cartella e aggiorna la query, mi darà immediatamente il nuovo set di dati combinato.
Ecco come si aggiorna una query:
- Fare clic con il pulsante destro del mouse sulla tabella Excel caricata nel foglio di lavoro e fare clic su Aggiorna.
- Fare clic con il pulsante destro del mouse sulla query nel riquadro "Query cartella di lavoro" e fare clic su Aggiorna
- Vai alla scheda Dati e fai clic su Aggiorna.
Ogni cartella di lavoro ha i dati con lo stesso nome del foglio di lavoro
Nel caso in cui non si disponga dei dati in una tabella Excel, ma tutti i nomi dei fogli (da cui si desidera combinare i dati) siano gli stessi, è possibile utilizzare il metodo mostrato in questa sezione.
Ci sono alcune cose su cui devi essere cauto quando si tratta solo di dati tabulari e non di una tabella Excel.
- I nomi dei fogli di lavoro dovrebbero essere gli stessi. Ciò consentirà a Power Query di esaminare le cartelle di lavoro e combinare i dati dei fogli di lavoro con lo stesso nome in ogni cartella di lavoro.
- Power Query fa distinzione tra maiuscole e minuscole. Ciò significa che un foglio di lavoro denominato "dati" e "Dati" è considerato diverso. Allo stesso modo, una colonna con l'intestazione "Negozio" e una con "negozio" sono considerate diverse.
- Sebbene sia importante avere le stesse intestazioni di colonna, non è importante avere lo stesso ordine. Se la colonna 2 in "East.xlsx" è la colonna 4 in "West.xlsx", Power Query la corrisponderà correttamente mappando le intestazioni.
Ora vediamo come combinare rapidamente i dati di diverse cartelle di lavoro in cui il nome del foglio di lavoro è lo stesso.
In questo esempio, ho una cartella con quattro file.
In ogni cartella di lavoro, ho un foglio di lavoro con il nome "Dati" che contiene i dati nel seguente formato (nota che questa non è una tabella Excel).
Ecco i passaggi per combinare i dati di più cartelle di lavoro in un unico foglio di lavoro:
- Vai alla scheda Dati.
- Nel gruppo Ottieni e trasforma, fai clic sul menu a discesa Nuova query.
- Passa il cursore su "Da file" e fai clic su "Da cartella".
- Nella finestra di dialogo Cartella, inserisci il percorso del file della cartella che contiene i file oppure fai clic su Sfoglia e individua la cartella.
- Fare clic su OK.
- Nella finestra di dialogo che si apre, fai clic sul pulsante Combina.
- Fare clic su "Combina e carica".
- Nella finestra di dialogo "Combina file" che si apre, seleziona "Dati" nel riquadro di sinistra. Nota che Power Query mostra il nome del foglio di lavoro dal primo file. Questo file fungerebbe da chiave/modello per combinare altri file. Power Query esaminerà ogni cartella di lavoro, troverà il foglio denominato "Dati e li combinerà".
- Fare clic su OK. Ora Power Query esaminerà ogni cartella di lavoro, cercherà il foglio di lavoro denominato "Dati" al suo interno e quindi combinerà tutti questi set di dati.
Questo caricherà il risultato finale (dati combinati) nel tuo foglio di lavoro attivo.
Nel caso in cui desideri modificare prima i dati prima di caricarli in Excel, al passaggio 6, seleziona "Combina e modifica". Verrà aperto il risultato finale nell'editor di Power Query in cui è possibile modificare i dati.
Ogni cartella di lavoro ha i dati con nomi di tabelle o nomi di fogli diversi
A volte, potresti non ottenere dati strutturati e coerenti (come tabelle con lo stesso nome o foglio di lavoro con lo stesso nome).
Ad esempio, supponi di ottenere i dati da qualcuno che ha creato questi set di dati ma ha chiamato i fogli di lavoro come Dati est, Dati ovest, Dati nord e Dati sud.
Oppure, la persona potrebbe aver creato tabelle Excel, ma con nomi diversi.
In questi casi, puoi comunque usare Power Query, ma devi farlo con un paio di passaggi aggiuntivi.
- Vai alla scheda Dati.
- Nel gruppo Ottieni e trasforma, fai clic sul menu a discesa Nuova query.
- Passa il cursore su "Da file" e fai clic su "Da cartella".
- Nella finestra di dialogo Cartella, inserisci il percorso del file della cartella che contiene i file oppure fai clic su Sfoglia e individua la cartella.
- Fare clic su OK.
- Nella finestra di dialogo che si apre, fai clic sul pulsante Modifica. Questo aprirà l'editor di Power Query dove vedrai i dettagli di tutti i file nella cartella.
- Tieni premuto il tasto Ctrl e seleziona le colonne "Contenuto" e "Nome", fai clic con il pulsante destro del mouse e seleziona "Rimuovi altre colonne". Questo rimuoverà tutte le altre colonne tranne le colonne selezionate.
- Nella barra multifunzione dell'editor di query, fai clic su "Aggiungi colonna", quindi fai clic su "Colonna personalizzata".
- Nella finestra di dialogo Aggiungi colonna personalizzata, denominare la nuova colonna come "Importazione dati" e utilizzare la seguente formula =Excel.Workbook([CONTENT]). Nota che questa formula fa distinzione tra maiuscole e minuscole e devi inserirla esattamente come ho mostrato qui.
- Ora vedrai una nuova colonna in cui è scritto Table. Ora lascia che ti spieghi cosa è successo qui. Hai fornito a Power Query i nomi delle cartelle di lavoro e Power Query ha recuperato gli oggetti come fogli di lavoro, tabelle e intervalli denominati da ogni cartella di lavoro (che al momento risiede nella cella Tabella). Puoi fare clic sullo spazio bianco accanto al testo Tabella e vedresti le informazioni in basso. In questo caso, poiché abbiamo solo una tabella e un foglio di lavoro in ogni cartella di lavoro, puoi vedere solo due righe.
- Fare clic sull'icona della doppia freccia nella parte superiore della colonna "Importazione dati".
- Nella casella dei dati della colonna che si apre, deseleziona "Usa colonna originale come prefisso", quindi fai clic su OK.
- Ora vedrai una tabella espansa in cui vedi una riga per ogni oggetto nella tabella. In questo caso, per ogni cartella di lavoro, l'oggetto di lavoro e l'oggetto tabella sono elencati separatamente.
- Nella colonna Tipo, filtra l'elenco per mostrare solo la tabella.
- Tieni premuto il tasto Ctrl e seleziona la colonna Nome e dati. Ora, fai clic con il pulsante destro del mouse e rimuovi tutte le altre colonne.
- Nella colonna Dati, fai clic sull'icona della doppia freccia in alto a destra nell'intestazione dei dati.
- Nella casella dei dati della colonna che si apre, fare clic su OK. Ciò combinerà i dati in tutte le tabelle e verranno visualizzati in Power Query.
- Ora puoi effettuare qualsiasi trasformazione di cui hai bisogno, quindi vai alla scheda Home e fai clic su Chiudi e carica.
Ora vorrei provare a spiegare rapidamente cosa abbiamo fatto qui. Poiché non c'era coerenza nei nomi dei fogli o nei nomi delle tabelle, abbiamo usato la formula =Excel.Workbook per recuperare tutti gli oggetti delle cartelle di lavoro in Power Query. Questi oggetti possono includere fogli, tabelle e intervalli denominati. Una volta ottenuti tutti gli oggetti da tutti i file, li abbiamo filtrati per considerare solo le tabelle Excel. Quindi abbiamo ampliato i dati nelle tabelle e combinato tutti questi.
In questo esempio, abbiamo filtrato i dati per utilizzare solo le tabelle di Excel (nel passaggio 13). Nel caso in cui desideri combinare fogli e non tabelle, puoi filtrare i fogli.
Nota: questa tecnica ti darà i dati combinati anche quando c'è una mancata corrispondenza nei nomi delle colonne. Ad esempio, se in East.xlsx hai una colonna con errori di ortografia, ti ritroverai con 5 colonne. Power Query inserirà i dati nelle colonne se lo trova e, se non riesce a trovare una colonna, segnalerà il valore come "null".
Allo stesso modo, se hai alcune colonne aggiuntive in uno qualsiasi dei fogli di lavoro delle tabelle, queste verranno incluse nel risultato finale.
Ora se ottieni più cartelle di lavoro da cui devi combinare i dati, è sufficiente copiarli e incollarli nella cartella e aggiornare la Power Query
