Microsoft Excel - Power Query - Contabilità, Estratto conto - Applicazione delle Join
Introduzione
Abbiamo due file
- contabilità.csv - contiene la situazione delle fatture con i campi
- DataFattura,
- NumeroFattura,
- Importo,
- Cliente
- estratto-contro.csv - Contiene la situazione delle fatture saldate in Banca con i campi
- DataPagamento,
- NumeroFattura,
- Importo,
- Cliente
Obiettivo
Vogliamo trovare
- Le fatture non saldate
- Le fatture saldate in banca ma che non risultano in contabilità
- Le fatture saldate evidenziando
- i giorni trascorsi tra la DataFattura e la DataPagamento
- Le differenze di importo tra la fattura e il saldo
Iniziamo
Estraiamo il file zip e mettiamo i file CSV in una cartella Contabilità
All'interno della cartella creiamo un file Excel gestione-contabilità.xlsx
Iniziamo a importare i file CSV
Importiamo il file contabilità.csv
Dal menù Dati scegliamo Da testo/CSV
e scegliamo il file contabilità.csv
Facciamo click su Trasforma dati
Viene creata la Power Query contabilità.
Il passaggio Modificato tipo però ha letto gli importi in maniera errata. Infatti nel file CSV l'importo relativo alla fattura F001 era scritto "1000.00" in stile paesi anglosassoni, con il punto come separatore dei decimali.
La power Query ha interpretato questo dato eliminando il "." e scrivendo 100000.
Eliminiamo il passaggio Modificato tipo per risolvere il problema facendo click sulla X.
Ora tipizziamo le colonne a mano. Click sul pulsante 
e scegliamo il tipo Data
Otteniamo
Click sul pulsante della colonna Importo
Invece di scegliere Valuta andiamo su Uso delle impostazioni locali
Nella pagina che appare scegliamo
- Valuta
- Inglese (Stati Uniti)
Confermiamo con OK
Ora l'importo è stato interpretato correttamente
Modifichiamo l'ordine delle colonne mettendo
- NumeroFattura
- Cliente
- DataFattura
- Importo
Terminiamo l'importazione andando nel menù Home > Chiudi e carica
Chiudi e carica in
Crea solo connessione
La connessione ora è stata creata
Importiamo il file estratto-contro.csv
I passaggi sono identici per il file contabilità.csv visto sopra.
Possiamo terminare con Chiudi e carica > Chiudi e carica > Crea solo connessione
salviamo il file Excel.
Creazione dei Report
Fatture insolute
Vogliamo vedere quali sono le fatture che non sono state pagate. Per questo creeremo una tabella con le fatture presenti in contabilità ma non in estratto-conto.
Click con il tasto destro sulla query contabilità e scegliamo Riferimento
Viene creata la nuova query contabilità (2)
Possiamo rinominarla in fatture insolute
Nel menù Home scegliamo Merge di query > Merge di query
Nella finestra che appare scegliamo
- nell'elenco a discesa estratto-conto
- in fatture insolute selezioniamo la colonna NumeroFattura
- in estratto-conto selezioniamo la colonna NumeroFattura
Nella finestra popup che appare attiviamo "ignora i controlli ..." e facciamo click su Salva
Come tipo di Join scegliamo Left Anti. in questo modo rimarranno solo le fatture in fatture insolute che non sono presenti in estratto-conto. Diamo OK
Possiamo espandere la colonna estratto-conto
inserendo tutte le colonne
Ma in realtà questa azione non serve perché le righe sono vuote
Quindi eliminiamo questo passaggio
Eliminiamo la colonna estratto-conto facendo click con il tasto destro e scegliamo Rimuovi
Possiamo concludere facendo click sul pulsante Chiudi e carica
Otteniamo la tabella fatture_insolute
Impostiamo la colonna Importo di tipo Valuta e confermiamo con OK
Possiamo aggiungere la Riga del Totale nel menù Struttura Tabella
Ora abbiamo l'elenco delle fatture insolute
Fatture anomale
Verifichiamo se ci sono fatture presenti solo nell'estratto conto.
Ripetiamo i passaggi visti in precedenza. Click con il tasto destro sulla query estratto-conto e scegliamo Riferimento
Rinominiamo la query anomalie e scegliamo Merge di query
Selezioniamo
- Nella finestra che appare scegliamo
- nell'elenco a discesa contabilità
- in anomalie selezioniamo la colonna NumeroFattura
- in contabilità selezioniamo la colonna NumeroFattura
- in Tipo di Join scegliamo Left Anti
Terminiamo con OK. Rimuoviamo la colonna contabilità che non contiene informazioni utili
Possiamo concludere facendo click sul pulsante Chiudi e carica
Otteniamo la tabella anomalie.
Fatture saldate
È il momento di creare la tabella delle fatture saldate.
Click con il tasto destro sulla query contabilità e scegliamo Riferimento
Viene creata la nuova query contabilità (2) che rinominiamo in fatture saldate.
Nella finestra che appare scegliamo
- nell'elenco a discesa estratto-conto
- in fatture insolute selezioniamo la colonna NumeroFattura
- in estratto-conto selezioniamo la colonna NumeroFattura
- Tipo di join - Inner
Ora la colonna estratto-conto contiene informazioni sulle fatture saldate in banca. Espandiamo la colonna estratto-conto.
Teniamo le colonne
- DataPagamento
- Importo
Per evitare nomi duplicati manteniamo Usa il nome della colonna origine ....
Diamo OK
rinominiamo le nuove colonne in DataPagamento e Saldo
Ordiniamo le colonne
Calcolo dei giorni di ritardo
Vogliamo calcolare quanti giorni sono passati dalla DataFattura alla DataPagamento.
Nel menù Aggiungi colonna scegliamo Colonna personalizzata.
Diamo il nome alla colonna GGPagamento e inseriamo la formula
= Duration.Days([DataPagamento] - [DataFattura])
Otteniamo la nuova colonna GGPagamento.
Impostamo questa colonna come numero intero
Spostiamo la nuova colonna dopo la DataPagamento.
NOTA
Il linguaggio M non contiene una funzione che permette di calcolare solo i giorni lavorativi come GIORNI.LAVORATIVI.TOT di Excel
Calcolo della differenza di importi
Vogliamo creare una nuova colonna che calcola la differenza tra Saldo e importo. Inseriamo una nuova Colonna personalizzata Differenza. la formula è
= [Saldo]-[Importo]
Otteniamo la nuova colonna Differenza che formattiamo in formato valuta
In negativo abbiamo gli importi mancanti al Saldo.
Terminiamo con Chiudi e Carica.
Formattiamo le colonne Importo, Saldo e Differenza in formato Valuta evidenziando i numeri negativi
Inseriamo la riga dei totali
