Formattare i dati in LuckyTemplates: risolvere i formati di dati irregolari

Nel post del blog di oggi, parleremo del problema della settimana n. 6 . Ti mostrerò come formattare i dati in LuckyTemplates che prevedono l'uso di power query . Lo screenshot qui sotto è la soluzione che dovresti avere dopo aver eseguito tutta la trasformazione dei dati. Il saldo effettivo era di $ 685.616,33 . Puoi guardare il video completo di questo tutorial in fondo a questo blog.

Abbiamo avuto molta partecipazione per la Challenge #6. Apprezzo gli sforzi di tutti nel cercare di ottenere questo risultato. Una cosa da notare è che abbiamo visto un sacco di voci che hanno ridotto leggermente il saldo. Ti mostrerò mentre passo attraverso la mia voce per questa sfida perché quell'importo in dollari era fuori.

Si noti che la mia soluzione su come formattare i dati in prevedeva più passaggi e che c'erano molte voci che condensavano quei passaggi. Terrò conto di queste scorciatoie per gli sforzi futuri che ho con questo tipo di dati. Quindi non solo i partecipanti hanno aiutato a risolvere il problema della settimana, ma mi hanno anche aiutato a lungo termine quando si trattava di sviluppare rapporti.

Aprirò la query di alimentazione e analizzerò passo dopo passo come ho trovato la mia soluzione. Ecco come appare il prodotto finito.

Ma se faccio clic sulla fonte, vedrai che questo è ciò che è apparso originariamente quando ci siamo collegati al percorso del file in cui abbiamo archiviato il file CSV.

Sommario

• Vantaggi dei commenti
• Ragioni per l'importo errato
• Filtraggio delle colonne
• Creazione della colonna dell'età
• Conclusione

Vantaggi dei commenti

Controlla i passaggi sul lato destro. Puoi vedere la piccola "i" che è l'abbreviazione di informazioni. Questo indica che ci sono commenti fatti nell'editor avanzato.

Apriamo l'editor avanzato molto velocemente qui. Puoi vedere che i testi in verde sono commenti che mi aiutano a ricordare a me stesso a cosa stavo pensando in quel momento.

Lascia una traccia di ciò che ho fatto in passato, quindi se qualcuno di nuovo entra a guardare il file, può almeno avere un'idea del percorso che ho intrapreso.

Possiamo vedere i commenti a lato qui. Il primo commento su Changed Type1 riguarda fondamentalmente la modifica dei tipi per le diverse colonne. Come puoi vedere, il commento che ho fatto è "Assicurati che la colonna corrente sia impostata come decimale, altrimenti è qui che le cose inizieranno ad andare male!"

Questo mi porta al punto del motivo per cui le persone escogitano quell'importo del saldo sbagliato che ti ho mostrato prima.

Ragioni per l'importo errato

Lascia che ti mostri un altro file PBIX che ho copiato da qualcuno che aveva una di quelle voci. $ 685.615,28 è l'importo sbagliato e c'erano numerose voci che avevano questo importo risultante.

Scopriamo come e perché è successo. Se andiamo nella scheda Trasforma e facciamo clic su Detect Data Type , che rileva automaticamente tutte le righe, possiamo vedere che la colonna corrente si presenta come un numero intero 64, che non è quello che vogliamo vedere.

Ecco perché gli importi sono sbagliati. C'è anche un altro modo in cui questo errore può essere visualizzato nelle tue impostazioni. Andiamo su File>Opzioni e impostazioni>Opzioni.

Sotto l'area GLOBAL e la sezione Data Load, possiamo vedere Type Detection . Ho scelto Rileva tipi di colonne e intestazioni per fonti non strutturate in base alle impostazioni di ciascun file .

Se selezioni la prima opzione, ovvero Rileva sempre i tipi di colonna e le intestazioni per le fonti non strutturate , otterrai l'errore anche nell'importo del saldo.

Uno dei nostri che è stato di grande aiuto quando si tratta di codice M o linguaggio di query di potenza è Melissa. Mi ha mostrato un documento Microsoft che ci spiegherà perché si è verificato questo errore.

Come puoi vedere, questo si chiamava Project Options . L'opzione Rileva automaticamente i tipi di colonna e le intestazioni per le fonti non strutturate è stata selezionata.

Il documento dice anche che si basa solo sulle prime 200 righe della tua tabella.

Nella mia tabella, la quantità originale di righe era di circa 4.000 righe. Quindi questo è il motivo per cui non ha raccolto gli importi giusti.

Torniamo alla query avanzata e iniziamo a formattare i dati in LuckyTemplates. Come puoi vedere nel passaggio Tipo modificato , è un numero intero 64 con corrente.

Quindi quello che farò qui molto velocemente è filtrare in base a uno di questi numeri di fattura per mostrarti come appare prima e dopo.

Dopo aver selezionato un particolare numero di fattura, possiamo vedere che l'importo della corrente è 4.741,01.

Dopo aver fatto clic su questo tipo di modifica, vedrai che il valore è cambiato ed è stato arrotondato a 4.741.

Quindi questo è un modo rapido e semplice per spiegare cosa è andato storto con le altre voci per il Problema della settimana.

Torniamo alla mia soluzione in Power query M. Il passaggio successivo è dove ho filtrato le righe per mostrare solo le righe vuote.

Il passaggio successivo è dove ho rimosso le colonne. Ho rimosso tutte le colonne tranne quelle che vedi negli screenshot. Inoltre, tieni presente che il numero di record originariamente indicato è in realtà il numero della fattura.

Il passaggio successivo consiste nel rimuovere le righe vuote che erano nulle per ciascun campo.

E poi ho rinominato la prima colonna che prima era vuota e l'ho cambiata in Cliente... perché aggiungeremo un altro campo che sarà il mio vero campo Cliente.

Il passaggio successivo consisteva nel sostituire tutti i valori di uno spazio vuoto con null nella colonna Cliente. Il motivo per cui dobbiamo farlo è a causa del passaggio successivo.

Per riempire tutte queste righe, nessuna di queste può essere vuota e devono essere nulle.

Per eseguire il riempimento, fai clic sulla colonna, vai su Riempi , quindi Giù .

Ecco come puoi vedere tutti i nomi scendere in questa colonna.

Filtraggio delle colonne

Ho anche filtrato altre righe qui e filtrato per numero di fattura per rimuovere quelle vuote.

Ho aggiunto un'altra colonna Cliente in cui ho copiato la colonna nello screenshot sopra e mi sono sbarazzato dei numeri prima del nome.

Ho anche riordinato le colonne. Ho spostato la colonna dei nuovi clienti dalla fine all'inizio. E poi ho rimosso la colonna Cliente che aveva il punto o il punto alla fine. Quindi ora ho solo una colonna Cliente chiara senza i numeri a sinistra.

Quindi il passaggio successivo è Inserted Sum, dove ho preso tutti i valori da Current, 1 – 30, 31 – 60, 61 – 90 e 91 a 120. Ho aggiunto tutte queste colonne insieme per ottenere la colonna personalizzata chiamata Addition .

Un altro modo per farlo è evidenziare tutte le colonne, andare alla scheda Aggiungi colonna , fare clic su Standard, quindi fare clic su Aggiungi .

Questo ti darebbe il campo Addition senza dover digitare nulla in power query.

E poi ho rimosso tutte quelle colonne originali: Corrente, 1 – 30, 31 – 60, 61 – 90 e 91 – 120. Successivamente, l'ho appena rinominato Balance .

Il passaggio successivo consiste nell'aggiungere una colonna personalizzata. Per questa sfida, era il 21 gennaio quando questi dati originali sono stati prelevati dal cliente.

La data è stata visualizzata automaticamente come funzione di testo, quindi l'ho modificata in una funzione di data con questo passaggio.

Creazione della colonna dell'età

Per creare una colonna dell'età, ho evidenziato la data odierna e la data di scadenza. Quindi sono andato alla scheda Aggiungi colonna e ho fatto clic su Sottrai giorni .

Il passaggio successivo consiste nel trasformarlo in un numero intero.

E poi ho rinominato la colonna da Age a Days Aged . Quindi ho aggiunto altre due colonne condizionali. Uno è per la colonna dell'invecchiamento per ricategorizzare questi elementi.

La seconda colonna condizionale aiuterebbe a ordinare la colonna di invecchiamento in modo che vengano visualizzate in ordine sequenziale.

Conclusione

Quindi è così che ho trovato la mia soluzione per questo problema della settimana. Dopo aver premuto Chiudi e applicato, questi sono i risultati.

Spero ti sia piaciuto affrontare la soluzione con me. Se ti è piaciuto questo tutorial su come formattare i dati in LuckyTemplates, iscriviti al canale TV per contenuti simili.

Jarrett