Tutorial [Power Query] Estrarre e trasformare i dati con Power Query

Andrea90

Power BI Expert
Expert
25 Giugno 2017
878
95
Riccione
Excel 2016
120
INDICE

Parte 1 - Introduzione caricamento, pulizia e raggruppamento dei dati

- Step 1: Caricamento dei dati​
- Step 2: Pulizia dei dati​
- Step 3: Raggruppamento dei dati​
- Step 4: Caricamento della query​
Parte 2 - Merge di Query
- Step 1: Caricamento dei dati​
- Step 2: Eseguire il merge​
- Step 3: Espansione tabella​
- Step 4: Merge con più chiavi di ricerca​
Parte 3 - Caricare più file da una cartella

________________________________________________
Buongiorno a tutti,

Sperando di fare cosa gradita volevo inserire ogni tanto dei tutorials in merito all'utilizzo dello strumento PowerQuery/PowerPivot.

Ho visto che altri utenti prima di me hanno aggiunto validi tutorial in merito a che cosa sia Power Query e a quali siano le sue potenzialità. Quello che volevo pormi come obiettivo era quello di offrire un contributo pratico, con tutorials brevi e mirati su di uno specifico comando o funzionalità di questo strumento.

Non avendone mai pubblicato uno spero di non andare contro ad eventuali regole previste dal forum, nel caso chiedo perdono in anticipo e rimedierò MartelloTesta.

Con la premessa che sia cosa gradita il fatto di inserire nuovi articoli di questo genere, un'altra cosa che mi piacerebbe sviluppare è la possibilità di poter ricevere consigli su quali argomenti trattare (non so se possibile rispondere nei commenti o in forma di messaggio privato), in modo tale da aggiustare di volta in volta il tiro su funzionalità che potrebbero risultare veramente utili agli utenti (sperando poi di avere il bagaglio di conoscenze necessario per soddisfare queste richieste :studia::arrossisco:).

Scendendo nel pratico avevo intenzione di lavorare per quanto possibile con dati pubblici, e ben strutturati. Ne ho trovato uno sul sito della Microsoft e che vi lascerò come allegato per provare a sviluppare "a casa" quanto indicato nel tutorial.

Vi lascio anche il link: https://docs.microsoft.com/it-it/power-bi/sample-financial-download

Spero di utilizzare il più possibile questa base dati così da poter offrire una sorta di familiarità tra un tutorial ed un altro.

Ultima cosa prima di cominciare: perdonate in anticipo qualsiasi strafalcione grammaticale che sicuramente farò Muoio_muoio

Step1_Caricamento Dati

Partendo dalla base direi di caricare i dati che troverete all'interno del file. Il foglio in questione si chiama "Dati" (fantasia portami via). Ho volontariamente ridotto il file che troverete sul sito, non in termini di righe, ma in termini di colonne, questo per due motivi:

1) Con le colonne che ho inserito possiamo limitarci a lavorare solo sull'argomento del tutorial di oggi
2) Alcune colonne presenti nel dataset vorrei aggiungerle in futuro a mano a mano che si rendono necessarie per mostrare nuove funzionalità.

In termini operativi il caricamento dei dati non dovrebbe essere cosa complicata, sempre rimanendo nel semplice. Questo anche perché altri utenti prima di me hanno mostrato alcune delle modalità principali di caricamento.
Visto quanto da loro realizzato direi che possiamo utilizzare il loro lavoro come una valida base di appoggio, al quale poi aggiungere o ripetere temi che di volta in volta si renderanno necessari.

Se apriamo il file troveremo 5 campi (le colonne di un qualsiasi dataset le chiamerò così):

1) Segment
2) Country
3) Product
4) Discount Band
5) Units Sold

La base dati non è in formato tabellare, anche se la formattazione che gli hanno dato porterebbe a presupporlo. Per ora la lasciamo così come è, semplicemente la selezioniamo e seguiamo il seguente percorso per giungere al comando che ci interessa:

Dati --> Da tabella/intervallo



Si aprirà una finestra che ci indicherà il range dell'intervallo da "prelevare" e se quest'ultimo ha delle intestazioni. Diamo conferma in questo caso, e verremo così rimandati all'interfaccia di PowerQuery.

Questa interfaccia contiene nella schermata centrale una videata con quelli che sono i dati estrapolati.

Step2_Pulizia Dati

La prima cosa che suggerisco di fare è quella di mettere in ordine il "materiale di lavoro", questo per un semplice motivo: con tutti i dati disposti e formattati correttamente le analisi dati risultano migliori.

Ecco alcune buone prassi da mantenere:

1) Rinominare la Query --> per me è una delle prime cose da fare perché anche se al momento non serve, fidatevi che quando avrete una decina di query attive nella stessa cartella di lavoro, vedere nomi come Dati(1), Dati(1.1), Foglio(4), non aiuta.

Per rinominare la query non bisogna far altro che selezionare nella sezione "Proprietà" visibile sulla destra, il campo "Nome" e modificarlo in modo tale da renderlo quanto più riconoscibile all'utilizzatore. Io la chiamerò "RaggruppamentoDati".

2) Rivedere il formato dei vari campi --> guardando la tabella nella schermata centrale vediamo che i nomi dei campi sono quelli forniti nella base dati che abbiamo estrapolato. Sulla sinistra di ciascuna etichetta c'è un piccolo riquadro con un simbolo che identifica il tipo di formato che PowerQuery ha assegnato. In generale il motore di PQ (PowerQuery) è abbastanza bravo da assegnare il giusto formato, ma consiglio sempre di rivedere ciascun campo per accertarsi della bontà del modello dati che andremo a caricare.



Questo qui sopra è un esempio di formato dati assegnato al campo "Units Sold" (Unità Vendute). Per scopi puramente pratici andremo a cambiare il formato, perché lo vogliamo visualizzare non in formato decimale ma intero. Selezioniamo dunque il riquadro che contiene il simbolo del formato, e dalla lista che apparirà selezioniamo numero intero.

Il sistema ti chiederà di confermare quanto da te richiesto e questo perché come accennavo prima PQ aveva già pensato ad un formato dati per quel campo, e tu gli stai dicendo che quello pensato da lui non è corretto e dunque lo vuoi cambiare. Lui ti chiede conferma di questa cosa, dunque premete sul comando per "Sostituire il formato corrente" ed avrete il nuovo formato impostato secondo quanto da voi richiesto.

!!! Importante: Un altro passaggio fondamentale, e che consiglio, è quello di rivedere i campi caricati a sistema, perché magari per convenienza carichiamo tutta la tabella, ma poi vogliamo solo che vengano utilizzate determinate colonne, dunque alcune devono essere rimosse. Le modalità per rimuovere le colonne presentano delle funzionalità interessanti a cui è bene dedicare un altro piccolo tutorial più avanti.

Step3_Raggruppamento Dati

Supponiamo che quanto mostrato precedentemente sia a noi sufficiente per aver reso il nostro dataset ordinato e pronto all'uso. Ora arriviamo all'obiettivo del presente tutorial:

Raggruppare i dati

Nel nostro dataset ci sono delle righe che riportano le stesse informazioni per i primi 4 campi, con l'unica differenza data dalla quantità venduta (Units Sold).

Non vogliamo mantenere questo livello di dettaglio, vogliamo invece accorpare righe uguali e far si che nella cella relativa alla quantità ci sia la somma delle quantità riportate nelle righe oggetto del raggruppamento. Per farla semplice supponiamo di avere due righe identiche ma che differiscono solo per il terzo campo:

Agente | Prodotto | Quantità

A | ProdottoA | 100
A | ProdottoA | 200

Quello che vogliamo ottenere è:

Agente | Prodotto | Quantità

A | ProdottoA | 300

Per farlo non bisogna far altro che selezionare tutte e 4 le colonne iniziali. Selezioniamo la prima colonna sulla sinistra poi spostiamo il cursore del mouse sulla destra e premendo Shift (tasto per il maiuscolo) selezioniamo l'ultima colonna prima del campo Quantità e avremo così tutte le colonne utili al raggruppamento selezionate.

Ora ricerchiamo il menu: "Trasforma"

e clicchiamo sulla prima voce che compare sulla sinistra: "Raggruppa per"



Comparirà un'interfaccia come quella che appare nell'immagine che segue e che riporta le seguenti opzioni:

- Ci saranno dei menù a tendina che riporteranno il nome dei campi che vogliamo andare a raggruppare (in estrema sintesi è come se Power Query crei delle combinazioni univoche con i valori che ciascun campo riporta, e sulla base di queste combinazioni creerà il raggruppamento andando ad eseguire una formula di aggregazione (somma, media, ecc). I campi sono comunque corretti dunque scendiamo alla sezione seguente.

- Ci saranno tre campi disposti in riga:

1) Nome nuova colonna --> è il campo che utilizziamo per inserire il nome che vogliamo dare alla colonna che conterrà le Quantità Vendute raggruppate (la quale sostituirà quella originale presente nel dataset: Units Sold). Io la chiamerò: Qtà Tot

2) Operazione --> è il campo che indica che tipologia di operazione vogliamo andare a svolgere con i dati presenti all'interno del campo da aggregare, in questo caso selezioniamo somma.

3) Colonna --> bisogna selezionare il campo da voler aggregare, in questo caso Units Sold



Diamo conferma ed in poco tempo avremo la nostra nuova colonna con il raggruppamento.

Ora se avete fatto caso, una volta inseriti caricati i dati nel modello in basso a sinistra compare una stringa di testo che indica quante sono le righe caricate (se superano una certa soglia riportano un messaggio del tipo: caricate più di 999 righe). In questo caso le righe iniziali, poste a sistema erano 700. A seguito dell'operazione sono diventate 372. Questo a dimostrazione del fatto che alcune righe erano effettivamente dei doppioni se non per la Quantità Venduta.

Step4_Caricamento query

Ora che la nostra Query ha lavorato in modo corretto è necessario caricare i dati che essa contiene a sistema. Per le modalità di caricamento ci sono i Tutorials sviluppati da altri utenti nel forum. Per non dilungarmi troppo caricherò i dati in formato connessione, e per farlo non bisogna far altro che selezionare dalla voce "Home" l'opzione "Chiudi e carica" con l'accortezza di premere sulla freccia che punta in basso e che apre un menù con l'opzione "Chiudi e carica in.."

Premuto quello si seleziona la voce "solo connessione" e si preme conferma.

Sulle modalità di caricamento vorrei entrare nel merito in tutorials futuri, dunque per il momento lasciamo le cose come stanno per poi ritornarci in futuro.

Due ulteriori appunti:

Non vorrei tediarvi troppo in questa domenica mattina dunque non approfondirò in questa sede due aspetti fondamentali da tenere sempre a mente quando si lavora con una mole di dati considerevole (possiamo considerarli come voci aggiuntive di controllo dello Step2):

1) Bisogna sempre rinominare i vari passaggi che costituiscono la Query
2) Bisogna sempre accertarsi dell'integrità del dataset, in parole povere: chi mi dice che le quantità caricate a sistema, e poi raggruppate siano effettivamente le stesse?

Questi due elementi potrebbero essere oggetto di futuri tutorials nel caso in cui il lavoro svolto, o meglio l'idea, sia gradita al forum.

Ringrazio chi ha voluto leggere fino a qui e auguro a tutti voi una buona domenica.

A presto,

Andrea
 

Allegati

Ultima modifica di un moderatore:

Marius44

VBA Expert
Moderatore
9 Settembre 2015
7.559
145
77
Catania
Excel2010
364
Ciao
Innanzi tutto GRAZIE.
Avevo visto e scaricato l'addin (ho la vers.2010) ma mi era sembrato di primo acchito troppo pesante (per la mia età) da studiare.
Devo darti atto che mi sono cimentato e ... sono riuscito ad ottenere quello che dicevi.

Veramente semplice e accurato. Bravissimo.
Aspetto (non far passare troppo tempo, non si sa mai di questi tempi ...) il seguito.
Ciao,
Mario
 

Andrea90

Power BI Expert
Expert
25 Giugno 2017
878
95
Riccione
Excel 2016
120
Ciao Marius44 @Marius44 ,

Sono io a ringraziarti per il fatto di aver provato a cimentarti in questo strumento. Detto poi da uno con il tuo immenso background su grafici e VBA fa ancora più piacere. :applausi:

Vedrò di inserire qualche nuovo tutorial durante la settimana che verrà. Tieni d'occhio la pagina Saluto_saluto

Ciao,
Andrea
 
  • Like
Reactions: gennaro.64

klingklang

Excel/Power BI Expert
Staff
20 Ottobre 2017
6.305
345
44
San Giovanni in Persiceto (BO)
www.mondobi.it
2016, 365
500
Non avevo ancora risposto per non "inquinare" il thread, ma a questo punto voglio cogliere l'occasione per dire quanto sia contento che un giovane preparatissimo come Andrea90 @Andrea90 sia arrivato sul forum e stia valorizzando l'utilizzo di strumenti come Power Query e Power Pivot, che siamo ancora in pochi a promuovere.
Buona parte del futuro di excel passa sicuramente di qui, e ampliare il gruppo di utenti esperti non può che giovare alla crescita di tutti :batticinque:
 

Andrea90

Power BI Expert
Expert
25 Giugno 2017
878
95
Riccione
Excel 2016
120
Buongiorno a tutti,

Nello scorso tutorial (qui) avevo accennato a come non ho voluto caricare l'intero dataset perché volevo sfruttare alcune delle colonne in esso presenti per mostrare una funzionalità molto utile di PowerQuery (PQ): Il Merge di Query.

Chi di voi ha provato a scaricare il dataset completo dalla pagina ufficiale di Microsoft ha potuto notare che oltre alle 5 colonne viste nel precedente tutorial ce ne erano altre, tra le quali due in particolare:

1) Manufacturing Price
2) Sale Price



Come è possibile notare alcuni valori si ripetono a seconda che sia il Prodotto (Manufacturing Price), oppure la combinazione di Segmento e Prodotto (Sale Price).

Mi sono dunque permesso di creare all'interno del file che vi allego, due ulteriori fogli, chiamati:

1) CostiProduzione
2) PrezziVendita

Lo scopo è quello di ricreare la porzione di tabella che vi ho mostrato precedentemente, attingendo le informazioni da queste due tabelle.

Nota:
Ora questa prassi non è congeniale per chi vuole operare in termini di "Modello Dati", questo perché tenere il tutto in un'unica tabella porta ad appesantire l'intero applicativo.

Per chi non fosse interessato a lavorare con il modello dati, ma necessita comunque di creare un legame tra due tabelle, riportando i dati di una, all'interno dell'altra, può dunque seguire questo breve tutorial, poiché l'operazione "Merge di Query" è quella necessaria per raggiungere questo scopo.

Scendendo nel pratico, apriamo il file allegato ed all'interno troverete il foglio con i dati utilizzati per effettuare il raggruppamento visto al tutorial precedente, ed inoltre avremo altri due fogli con dati salvati all'interno di due tabelle, nominate con il nome dei fogli che le contengono.

Step1_Caricamento Dati

La prima cosa da fare è quella di inserire all'interno della nostra interfaccia di PowerQuery i dati delle due nuove tabelle.

Clicchiamo su quella relativa ai costi di produzione, accediamo al tab "Dati" e nella sezione "Recupera e trasforma dati" selezioniamo la voce "da tabella/intervallo". (stessi passaggi visti nel precedente tutorial).

Verremo rimandati all'interfaccia di PowerQuery, la quale avrà già caricato i dati della tabella dentro una nuova Query, la quale sarà nominata come la tabella presente sul foglio.

Per rinfrescare alcuni passaggi visti precedentemente possiamo cambiare il formato del campo Manufacturing Price, selezionando il formato "Valuta", diamo l'ok al comando "Sostituisci corrente" ed avremo completato la prima importazione. (per un eventuale remind di come si eseguono queste operazioni controllare il tutorial precedente).

Ora possiamo premere la freccia che punta in basso del pulsante "Chiudi e carica" e selezionare la voce "Chiudi e carica in", salviamo il tutto nella sola modalità connessione.

Ripetiamo i passaggi visti fino a qui anche per l'altra tabella presente sul foglio "PrezziVendita", cambiando anche in questo caso il formato del dato numerico in valuta e caricando il tutto in modalità "connessione".

Ora se tutti i passaggi sono stati eseguiti correttamente dovreste ritrovarvi dentro al vostro file excel con la seguente lista di Query:




Step2_EseguireMerge_1 ChiaveRicerca

Dalla lista delle Query selezioniamo quella nominata RaggruppamentoDati e clicchiamo il tasto sinistro del mouse due volte.
Avremo così accesso all'interfaccia di PowerQuery con i dati del raggruppamento mostrati in formato tabellare.

Ora il nostro scopo è quello di legare a ciascun prodotto presente sulle righe il relativo costo di produzione che è presente nella query caricata precedentemente.

In estrema sintesi quello che vogliamo fare è simile ad un operazione di CERCA.VERT()

La chiave di ricerca in questo caso è il nome del prodotto, poiché esso è presente in entrambe le tabelle.

Questi sono i passaggi da compiere:

1) Accediamo al tab "Home" e cerchiamo la sezione "Combina"

2) Cerchiamo il comando "Merge di Query" e premiamo la freccia che punta verso il basso in modo tale da vedere la lista di opzioni.

3) Il comando che a noi interessa è "Merge di Query"

Accederemo alla seguente schermata:



Come possiamo notare, avendo attivato il Merge di Query all'interno della Query "RaggruppamentoDati", la tabella che ci viene mostrata è proprio quella che stavamo analizzando.

Selezioniamo ora la seconda tabella, quella che contiene i dati del costo di produzione dal menu a tendina sottostante.

Ora quello che dobbiamo fare è semplicemente selezionare la colonna da utilizzare come chiave di ricerca all'interno delle due tabelle. In questo caso selezioniamo la colonna "Product".



Ora prima di premere il comando di invio è necessario soffermare l'attenzione su due aspetti:

PowerQuery mette a disposizione 6 tipologie differenti di Join, e cioè di "modalità" con le quali poter generare l'output finale dato dal Merge.

Trattare tutte queste tipologie, e studiarne gli effetti di ciascuna esula dal tema di questo Tutorial, perché prima è necessario capire quali sono i passaggi da eseguire in generale, per poter realizzare un'operazione di Merge.

Nulla vieta però, di poter creare un tutorial dedicato, se gradito, per entrare nel merito di ciascuna tipologia.

Per il momento ci basta sapere che l'operazione CERCA.VERT() è associata alla tipologia LeftOuter (che è poi la tipologia di default).

Un altro aspetto su cui prestare attenzione è il messaggio che compare in basso a sinistra nella finestra delle query. Questo messaggio ci informa che delle 372 righe presenti nella Query RaggruppamentoDati, tutte sono state in grado di associare una riga della Query CostiProduzione. In poche parole, tutti i prodotti presenti nel primo foglio, sono presenti anche nel foglio dei costi di produzione.

Diamo dunque conferma dell'operazione, per notare una nuova colonna posta all'estrema destra della tabella relativa al RaggruppamentoDati.




Step3_EspansioneTabella

Dobbiamo sapere che PowerQuery è in grado di fornire come output anche delle "Tabelle", da far comparire all'interno di singole celle.

Proviamo con il mouse a cliccare in un qualsiasi punto alla destra della scritta "Table" che compare in ogni riga della nuova colonna. Vedremo che in basso comparirà una finestra che espande la selezione fatta e ci mostra i risultati che quella singola "table" contiene:



Ora noi vogliamo però estrarre quell'informazione da questa tabella, in maniera tale da poterci poi lavorare in modo classico, come siamo abituati a fare da sempre.

Per farlo dobbiamo espandere la colonna che contiene queste tabelle disposte per riga.

Premiamo dunque quel pulsante dalla doppia freccia che compare alla destra del nome della colonna "CostiProduzione". Una volta premuto ci comparirà una finestra simile a quelle che si aprono quando premiamo sul pulsante filtro di una colonna.

Il nostro compito è di selezionare solamente i campi che vogliamo importare dentro la nostra query, in questo caso deselezioniamo il campo Prodotto, perché è già presente nella tabella di origine, e teniamo selezionata solo il campo Manufacturing Price.

Questa è l'immagine di come dovrebbe comparirvi la schermata:



Lasciate il flag sull'opzione in basso cosi da evitare possibili problematiche legate all'importazione di campi con un nome identico a quelli già presenti nella tabella di origine (tanto poi andremo a modificare i nomi successivamente).

Premiamo OK ed avremo la nostra nuova Query con tutti i dati originali, più il nuovo valore del Costo di Produzione per ciascuna riga.

Cambiamo il nome del campo togliendo il riferimento alla colonna di origine, per farlo facciamo doppio click con il tasto sinistro sul nome ed una volta modificato premiamo invio.

Step4_EseguireMerge_2 ChiaviRicerca

Ora una domanda che potrebbe venire fuori è la seguente:

Ok, ho capito come si crea un Merge di Query, ma come faccio a creare la stessa procedura se le mie due tabelle sono legate tra loro da una chiave di ricerca composta da due campi e non da uno solo.

Magari in maniera "semplice" si risolveva questa cosa con un CERCA.VERT andando però ad effettuare la ricerca su di un campo chiave composto dalla concatenazione di più campi. Questa chiave di ricerca veniva però inserita in una nuova colonna da porre nella tabella che importa i nuovi dati.

In PowerQuery tutto ciò non è necessario.

Ripetiamo tutti i passaggi visti allo Step2 alla schermata per la selezione dei campi di ricerca:



Ora la chiave che vogliamo creare si basa sulla combinazione di due campi: Segmento e Prodotto.

Per farla non basta far altro che selezionare dalla prima tabella, il campo Segmento e poi il campo Prodotto tenendo premuto il pulsante Ctrl.
Se fatto correttamente vedremo selezionati entrambi i campi, con un piccolo numero progressivo sulla destra di ciascuno di essi.

Abbiamo appena creato la nostra chiave di ricerca combinata.

Ripetiamo la stessa cosa sulla seconda tabella (Quella relativa ai prezzi di vendita) e premiamo invio.

Da qui in poi i passaggi sono i medesimi di quelli visti precedentemente, dunque non resta altro che ripeterli nell'ordine mostrato ed avremo la nostra nuova Query "RaggruppamentoDati" nel seguente formato:



Ora possiamo chiudere e caricare, il pulsante chiudi e carica in.. non sarà visibile perché sono già state impostate tutte e tre le Query come sola connessione.

Nel caso in cui i risultati non fossero in linea con quelli mostrati, o se alcuni passaggi non fossero sufficientemente chiari basta comunicarlo che poi provvedo a chiarire ogni eventuale dubbio a riguardo.

Per il momento direi di concludere qui questa prima introduzione al Merge di Query così da potervi dar modo di testare quanto visto fino ad ora.

Nel proseguo dei vari tutorials potrà capitare di mostrare ulteriori funzionalità legate a questo strumento.

Ci tengo a ricordare che la Query così creata gode di tutte le proprietà di una normale Query, e cioè se un domani vado ad aggiungere nuove righe alla tabella di partenza, queste ultime rientrerebbero nel calcolo della Query e dunque avrebbero anche loro il dato relativo al costo di produzione o al prezzo di vendita (a patto ovviamente che queste informazioni siano disponibili nelle due nuove tabelle appena aggiunte).

Colgo l'occasione per ringraziare chi ha avuto la pazienza di leggere e di mettere in pratica quanto visto fino a questo punto.

A presto,

Andrea
 

Allegati

Andrea90

Power BI Expert
Expert
25 Giugno 2017
878
95
Riccione
Excel 2016
120
Buongiorno a tutti,

Leggendo le varie discussioni aperte sul forum ho notato come molto spesso vengano richieste soluzioni che possono essere implementate tramite l'utilizzo del caricamento dati da cartella di PowerQuery (PQ).

Questo brevissimo tutorial spiega quali sono i passi principali da seguire per questo tipo di caricamento.

Partiamo subito con un esempio:

Ho una cartella che contiene 4 file excel con i dati delle vendite delle 4 aree geografiche che voglio analizzare. Ogni file viene gestito da un responsabile, in questo caso sono 4, i quali mi inviano settimanalmente lo stesso identico format, con dati ovviamente diversi che poi dovrò riepilogare all'interno di un'unica cartella di lavoro.

Per lavorare con questa modalità di caricamento dati è bene impostare una serie di regole per la raccolta delle informazioni, in maniera tale che questi 4,5,6,20 files che dovrò accorpare siano identici in termini di struttura (con ovviamente differenze in termini di valori raccolti).

I file che allegherò mantengono sempre la stessa impostazione:

1) hanno un unico foglio chiamato "Dati"
2) i dati sono raccolti all'interno di una tabella, la quale ha il nome dell'area alla quale i valori si riferiscono
3) ogni file viene salvato con il nome dell'area

Il fatto di impostare queste precisa serie di regole non è una cosa obbligatoria, potete tranquillamente utilizzare le regole che volete, l'importante è che queste vengano rispettate da tutti coloro che lavoreranno direttamente sui file che poi voi dovrete riepilogare.

La prima cosa da fare è aprire il nostro file excel di riepilogo (che NON dovrà risiedere dentro alla cartella dalla quale andremo a pescare i dati) e selezionare la voce per caricare i dati da cartella:



Una volta premuto dovremo inserire il giusto percorso (io inserirò uno creato ad hoc per questo tutorial, voi ovviamente selezionerete il vostro tanto i passaggi che poi andremo ad eseguire rimarranno gli stessi).

Nel mio caso il percorso è il seguente (la cartella che utilizzo per contenere i quattro files Excel l'ho chiamata Vendite):

C:\Users\andre\Desktop\Vendite

Ora comparirà una prima preview di quello che è il contenuto della cartella. Noi non dovremo fare altro che selezionare il menu a tendina della voce "Combina" posto in fondo alla schermata, e selezionare la voce: "Combina e trasforma dati"



In questa schermata vedremo che PowerQuery seleziona per noi il primo file disponibile all'interno della cartella, e di questo file mostra il suo contenuto, in questo caso una tabella che prende il nome "Est", ed il foglio di lavoro che esso contiene, e cioè "Dati".

Ora se il foglio di lavoro è pulito, e cioè se i dati sono disposti in modo ordinato, e non ci sono celle valorizzate in giro che non siano quelle che vogliamo analizzare, possiamo anche cliccare sul nome del foglio (Dati). Questo perché ogni file conterrà lo stesso foglio dal quale poi andare ad estrarre le informazioni.

Oppure, potremmo chiamare ogni tabella nello stesso modo, cosi che invece di selezionare il foglio generico Dati, potremmo selezionare direttamente la tabella generica, così da essere sicuri che l'informazione che andremo ad estrapolare sia quella richiesta.

Oppure possiamo selezionare l'intera cartella di lavoro, e dunque sia tabelle che foglio, premendo il simbolo della cartella "Parametro 1 [2]"

Facciamo così e premiamo il pulsante di conferma. Questa sarà la schermata che apparirà:



Dal campo Kind selezioniamo tramite l'apposito pulsante per il filtro la sola tipologia "Table" così da richiamare sempre i dati presenti all'interno delle tabelle, anche se queste tabelle sono chiamate con nomi diversi.

A questo punto selezioniamo solo la colonna "Name" e "Data" in modo tale da premere poi il pulsante destro del mouse e selezionare la voce "Rimuovi altre colonne". Così da rimanere solo con queste due.



A questo punto clicchiamo con il tasto sinistro del mouse sul pulsante per espandere le tabelle presenti all'interno della colonna Table (comando già incontrato nei precedenti tutorial) e deselezioniamo la voce per utilizzare il nome della colonna originale come prefisso.



Diamo conferma e quello che otterremo sarà l'output finale che contiene tutti i dati provenienti dai 4 file inseriti all'interno della cartella scelta come origine.

Ora questi dati possono essere sistemati e caricati all'interno di una tabella, di una connessione, di una tabella pivot, ecc. Per farlo basta seguire quanto visto nei precedenti tutorial e dunque selezionare la freccia che punta in basso vicino al comando "Chiudi e carica", poi dal menu a tendina selezionare la voce "Chiudi e carica in".

Questo breve tutorial voleva mostrare quelli che sono i passaggi principali da seguire per il caricamento dei dati da cartella. Ulteriori proprietà e funzioni potranno comparire in eventuali tutorial futuri o a seguito di domande specifiche poste dagli utenti se i passaggi in questione non fossero sufficientemente chiari.

A presto,

Andrea
 

Allegati

gennaro.64

Nuovo utente
22 Gennaio 2021
10
3
365
0
Complimenti per la precisione e la chiarezza espositiva.
Sono neofita di power query e sono alla ricerca di approfondimenti.
Mi piace il tuo approccio (sei partito dalla tabella dal sito di microsoft (https://docs.microsoft.com/it-it/power-bi/sample-financial-download) che contiene tutte le colonne per fare quegli approfondimenti che io ricerco e che vorrei proporti come nuovi argomenti:

a) colonne aggiuntive risultato di operazioni su colonne esistenti;
b) riepiloghi per chiavi di ricerca come la Regione, il segmento, etc.
c) incidenza percentuale dei totali e/o delta sulle singole regioni;
d) viaualizzare i risultati in una pivot, magari con filtro di una sequenza temporale;
e) collegare un grafico automatico.

Tutte cose che sono possibili e che molti di noi conoscono nel foglio di calcolo, ma che hanno uno sviluppo diverso nelle power query.

Grazie per quanto fatto/farai.
 

Andrea90

Power BI Expert
Expert
25 Giugno 2017
878
95
Riccione
Excel 2016
120
Buongiorno G @gennaro.64 ,

Grazie per aver concesso del tempo alla lettura di quanto ho pubblicato in questa pagina. In particolar modo per i tuoi suggerimenti (sei il primo che mi ha fornito spunti per nuovi tutorials quindi ancora un sincero grazie).

Mi permetto di aggiungere qualche commento, così da darti un mio parere:

a) Si questa è una cosa utile in molti contesti, il più delle volte molto semplice da realizzare poiché l’interfaccia di PowerQuery permette di fare tanto, senza per forza avere nozioni di linguaggio M.
b - c) Metto insieme questi 2 punti perché per me non conviene svilupparli in PowerQuery. Il consiglio che mi sento di dare è di studiare si PowerQuery, ma senza perderci troppo tempo, o cercare di andare troppo a fondo. Molto meglio è invece spenderlo per approfondire il linguaggio DAX, il quale rappresenta la base per lavorare con le PowerPivot. Tutto quello che hai indicato si può fare anche con una normale Pivot, ma per unire la comodità di una Pivot (ad esempio aggregare dati per certe dimensioni) a formule avanzate (esempio calcoli YTD o YOY, media mobile, ecc ) allora è necessario ricorrere alle PowerPivot.

Inoltre i due strumenti viaggiano assieme, nel senso che PowerQuery prepara il dato, lo pulisce e lo rende disponibile all’interno di un modello dati, ma chi poi svolge il lavoro di analisi è il DAX, che in excel si traduce in PowerPivot e in formule CUBO.

A presto,

Andrea
 

gennaro.64

Nuovo utente
22 Gennaio 2021
10
3
365
0
Sono io che ringrazio te per le risposte.
Ho capito le tue indicazioni.
Ricapitolando:
- per il punto a) ci aspettiamo un supplemento di tutorial .... :) ;
- per il punto b-c il tutorial si estenderà a power pivot .... :)

di nuovo grazie.
A presto
 

Andrea90

Power BI Expert
Expert
25 Giugno 2017
878
95
Riccione
Excel 2016
120
Ciao G @gennaro.64 ,

Se hai qualche indicazione specifica su alcune operazioni che ti interesserebbe vedere in PQ dimmi pure che aggiungo una sezione.

Per PowerPivot invece ti dico già che non lo tratto qui ma sul sito di MondoBi, proprio perché è un linguaggio specifico per PowerPivot, ma anche, e soprattutto, per PowerBI.

Alcuni tutorial gli ho già realizzati in merito ai concetti base. E conto di proseguire tempo permettendo.

A presto,

Andrea
 

gennaro.64

Nuovo utente
22 Gennaio 2021
10
3
365
0
Ciao G @gennaro.64 ,

Se hai qualche indicazione specifica su alcune operazioni che ti interesserebbe vedere in PQ dimmi pure che aggiungo una sezione.

Per PowerPivot invece ti dico già che non lo tratto qui ma sul sito di MondoBi, proprio perché è un linguaggio specifico per PowerPivot, ma anche, e soprattutto, per PowerBI.

Alcuni tutorial gli ho già realizzati in merito ai concetti base. E conto di proseguire tempo permettendo.

A presto,

Andrea
Partiamo dalla tabella che hai individuato sul sito della microsoft.
Abbiamo intercettato il prezzo di acquisto dei prodotti, il prezzo di vendita ed abbiamo la quantità di prodotti venduti.
Possiamo agevolmente calcolarci il costo totale dei prodotti acquistati e l'ammontare totale dei ricavi da vendita.
Possiamo calcolarci il margine (inteso come differenza tra ammontare dei costi ed ammontare dei ricavi) declinato pert le varie aggregazioni (Regioni, segmenti, prodotti) ed in percentuale (qual'è la mia percentuale di margine?).
Io vorrei tutto rappresentato in un report (immagino una pivot) con un grafico collegato.

Questo è l'obiettivo.
Poi vediamo che fino ad un certo punto ci può essere d'aiuto power query, poi dobbiamo fermarci perché dobbiamo continuare su power pivot e ci darai indicazioni come continuare, a quali link trovare risposte.
Va bene così?
 

Andrea90

Power BI Expert
Expert
25 Giugno 2017
878
95
Riccione
Excel 2016
120
Ciao G @gennaro.64 ,

Per me questi calcoli sono già tutti da passare in PowerPivot, poiché preferisco calcolare queste misure "al bisogno", ovvero quando creo le reportistiche in PowerPivot, evitando il proliferare di colonne calcolate all'interno del mio modello.

Pertanto carico la query "Raggruppamento Dati" direttamente come connessione ed anche all'interno del mio modello dati.

Successivamente creo una PPivot e comincio a calcolare le misure che mi servono in linguaggio DAX:

Es° TotSales

TotSales:
=SUMX( RaggruppamentoDati; [Qtà Tot] * RaggruppamentoDati[Sale Price] )
E così via con tutte le altre misure. Una volta completato questo passaggio sono libero di utilizzarle all'interno di una PowerPivot creandomi reportistiche come quella che troverai allegata. Cambiando le dimensioni poste sulle righe/colonne potrai poi creare tutti i report/grafici che vorrai.

Per lo studio di quelle formule però è necessario studiare il linguaggio DAX. Online trovi davvero di tutto.
Da parte mia posso inserire il link a MondoBi, che comunque trovi nella mia firma ed anche qui: Link x MondoBi

Troverai una sezione dedicata alle PowerPivot (in excel), ed una dedicata a PowerBi, ma tanto il linguaggio è unico ovvero DAX.

A presto,

Andrea
 

Allegati

  • Like
Reactions: klingklang

gennaro.64

Nuovo utente
22 Gennaio 2021
10
3
365
0
Buon giorno Andrea90,

Innanzitutto desidero ringraziarti per le preziose indicazioni fornite.
E' evidente la mia ignoranza su Power Pivot. E' un mondo per me ancora alquanto sconosciuto.
Avendo sperimentato un po' Power Query, inconsciamente ed inconsapevolmente, avrei preferito soluzioni in ambito Power Query che, a dire il vero, per me che non ho mai masticato di linguaggi di programmazione, trovo molto "user friendly", molto intuitivo fare operazioni in ambito Power Query.

Non conosco Power Pivot (se non per la connessione delle chiavi comuni tra le tabelle) e quindi, ripeto, per me che non conosco linguaggi di programmazione, così, su due piedi, mi spaventa un po'. Spero che anche Power Pivot offra la possibilità di operazioni intuitive.

Seguirò comunque le tue indicazioni: guarderò i tutorial su Power Pivot e magari chiederò il tuo aiuto su quel fronte .... :)

PS: Ho visto il file ultimo allegato con lo sheet "power pivot". E' eccezionale. E' esattamente quello che io intendo realizzare. Quindi vada per Power Pivot .... :) :)

PS2: Ho visto le formule che hai inserito in Power Pivot per avere quei risultati aggregati. Ho risolto (rectius, hai risolto) le mie esigenze ..... :) :)

Di nuovo grazie di tutto.
gc
 
Ultima modifica:
  • Like
Reactions: klingklang

gennaro.64

Nuovo utente
22 Gennaio 2021
10
3
365
0
Ciao Andrea90,
Nella tabella allegata ho fatto da solo, cercando di replicare quanto hai fatto tu.
Diciamo che ci sono riuscito (quasi), a meno di alcune differenze:

Questi sono i totali del tuo report










Country Segment Product #TotSales #TotCost #TotMargin #Margin%








Totale complessivo
141.732.297​
43.381.091​
98.351.206​
69,39%​
Questi i miei










Country Segment #TotSales #TotCost #TotMagin #Margin%




Totale complessivo
141.731.862​
106.662.513​
35.069.349​
24,74%​
1) Perché il mio #TotCost (e conseguente Margin e Margin%) è diverso?
Eppure ho controllato il numero delle righe della tua query è uguale a quello della mia.

2) Come hai fatto ad inserire nella tua PPivot la colonna Product senza che siano presenti i valori?

3) Come mai nella tua query "RaggruppamentoDati" alla fine della tabella hai una riga 373 con il totale dei prodotti?

ciao e grazie
g
 

Allegati

Andrea90

Power BI Expert
Expert
25 Giugno 2017
878
95
Riccione
Excel 2016
120
Ciao G @gennaro.64 ,

Se hai usato la copia del file che ho inviato nell'ultimo messaggio allora i valori devono per forza tornare.

Ho aperto il tuo file e i dati della tabella Costi di Produzione non sono come i miei, pertanto devi aver utilizzato la vecchia versione.

Per quanto riguarda la colonna dei prodotti che vedi nella Ppivot è semplicemente un raggruppamento, che poi andrai ad espandere cliccando sul simbolo "+" che precede il nome del segmento.

Sulla riga 373 non so a cosa tu faccia riferimento. La query da te indicata mi mostra 372 riga nel modello dati.

P.s. Per simili domande conviene però creare nuove discussioni all'interno del forum così da non "inquinare" troppo il Tutorial anche a beneficio degli altri utenti.

Grazie mille e a presto.

Andrea
 

Sostieni ForumExcel

Aiutaci a sostenere le spese e a mantenere online la community attraverso una libera donazione!