Risolvere i tassi di cambio mancanti con Power Query in LuckyTemplates

In questo tutorial imparerai come usare Power Query per le conversioni di valuta in LuckyTemplates. Puoi guardare il video completo di questo tutorial in fondo a questo blog.

Il metodo discusso in questo blog ti aiuterà a risolvere i problemi nella tabella dei tassi di cambio utilizzando l'ultimo tasso disponibile.

Capirai come risolvere le cose quando la tabella dei tassi di cambio non ha un tasso di cambio ogni singolo giorno.

Sommario

• Identificare i dati mancanti
• Separare diverse valute
• Riempire le tariffe mancanti
• Unire tutte le valute
• Creazione di relazioni nel modello di dati
• Confronto tra metodi DAX e Power Query
• Conclusione

Identificare i dati mancanti

Questo problema può verificarsi se la tabella Data non dispone di dati per le festività o i fine settimana.

Se non disponi di una tariffa in nessun giorno e non la tieni in considerazione, metriche come le vendite totali non verranno convertite.

Se dai un'occhiata a questa tabella dei tassi di cambio, ci sono i tassi in euro per gennaio 2016.

Ma nota che mancano i giorni 27 e 28 e non hanno una tariffa. Quindi, se vai alla matrice, puoi vedere che non ci sono tariffe per 27 e 28.

La misura Tasso valuta con misura mancante è semplicemente una somma della tabella Tassi valuta fatti.

Il totale di 26,6983 nella tabella non ha senso. È solo la somma di tutti i tassi di cambio al di sopra di esso che non è necessario sommare perché sono ciò che sono sotto il giorno corrente.

Ora, questo può essere risolto in Power Query perché si tratta più di un problema di modellazione dei dati che coinvolge query e aggregazione. Questa operazione può essere eseguita anche con DAX, ma è più semplice e veloce con Power Query in LuckyTemplates.

Quindi, fai clic su Trasforma dati e quindi apri la finestra di Power Query.

Questa è la tabella con i dati mancanti. Puoi anche vedere in questa tabella dei tassi di cambio che contiene Euro, Sterline e Dollari.

E poi questo è il tavolo finale che ha valori il 27 e 28 gennaio. Questa è la tabella o l'output che dovresti avere.

Importa i tuoi dati da qualsiasi fonte da cui li stai ricevendo e cambia semplicemente il tipo di dati.

Separare diverse valute

Successivamente, se hai più di una valuta, è importante separarle e raggrupparle in base al loro ticker.

Quando li raggruppi, assicurati di raggrupparli in Tutte le righe in modo che se fai clic su Euro, ad esempio, verranno visualizzati solo i tassi di cambio in euro.

Puoi anche vedere che qui mancano i giorni così come la valuta sterlina.

C'è questa cosa chiamata Invoke Custom Function in LuckyTemplates. Questa è una funzione con FillMissingRates.

Se fai clic su Euro, vedrai una tabella completa che contiene i dati mancanti che hai visto nella tabella espansa. È ordinato dalle tariffe più vecchie a quelle più recenti.

Scopri cosa stanno facendo le funzioni personalizzate. Se fai clic sull'icona a forma di ingranaggio vicino a Richiama funzione personalizzata, puoi vedere il nome della colonna e la fonte che sta estraendo nella colonna Tutto.

Per farlo, inizia con una tabella di base. Esegui le trasformazioni, ottieni il risultato desiderato e poi utilizzalo per costruire la funzione. Ha più senso che provare a farlo nella tabella in cui hai tutte le altre sottotabelle.

Ad esempio, se vuoi concentrarti sull'euro, filtralo fino al ticker.

Per capire quali giorni mancano, uniscilo alla tua tabella delle date.

Ecco una tabella delle valute filtrata di base e una tabella DimDate:

Per il tipo Join, puoi utilizzare Full Outer per inserire tutte le righe da entrambe le tabelle. Questo ti mostra cosa manca e le tariffe che corrispondono.

Quando lo unisci, ti darà una tabella completa come sottotabella.

Successivamente, ordina le righe e quindi espandi DimDate per includere solo la colonna Data.

Ora puoi vedere che tutto è abbinato. E quando ordini le righe dal più vecchio al più recente, puoi anche vedere i due giorni mancanti con valori nulli.

Riempire le tariffe mancanti

È importante che siano in questo ordine perché riempirai tutti i null in tutte le colonne tranne Date.1.

Dopo averlo compilato, puoi vedere che i valori nulli sono cambiati in Euro e l'ultima tariffa disponibile.

Successivamente, rimuovi le colonne non necessarie come la colonna Data dalla tabella.

La colonna Date.1 contiene già le date, quindi la colonna Date non è necessaria. Quindi, imposta il Crossrate su Decimal Number.

Infine, riordina e rinomina le colonne e assicurati che i tipi di dati siano impostati.

Ora hai una tabella completa dei tassi di cambio.

Tuttavia, questo funziona solo per questa tabella. Devi trovare un modo per applicare la stessa logica a una tabella in cui sono raggruppate le valute.

Unire tutte le valute

Se hai valute diverse, è difficile mantenere tabelle dei tassi diverse. Quindi, devi unirli per creare una tabella di valute.

Per iniziare, prendi la tabella esistente e crea una funzione facendo clic su Tabella e selezionando Crea funzione . Quindi, inserisci un nome per la funzione.

Successivamente, vai su Editor avanzato . Rimuovi i calcoli che non ti serviranno. In questo caso, eliminare l'origine, filtrare e modificare la sintassi.

Successivamente, dai un nome al tuo parametro Source e quindi inserisci Let. Poiché le righe filtrate sono state rimosse, modificare il valore all'interno della sintassi delle query unite in origine. Successivamente, elimina l'origine alla fine della sintassi, quindi fai clic su Fatto.

Ora vai su Aggiungi colonna, fai clic su Richiama funzione personalizzata. Immettere un nome di colonna come Tutti i dati. Per la query della funzione, selezionare la funzione creata in precedenza. Scegli Tutto come Origine, quindi fai clic su OK.

La prossima cosa da fare è rimuovere tutte le colonne tranne Tutti i dati. Quindi, espandi quella colonna e deseleziona l'impostazione "Usa il nome della colonna originale come prefisso". Successivamente, fai clic su OK e modifica i tipi di colonna.

Impostare il tipo di colonne Data, Ticker e Incrocio rispettivamente su Data, Testo e Decimale. Al termine, fai clic su Chiudi e applica.

Ora otterrai una tabella dei tassi di cambio combinati senza date e tassi mancanti.

Creazione di relazioni nel modello di dati

Nel tuo modello di dati, puoi vedere la tabella FactCurrencyRates . Ora, crea una relazione, Ticker to Ticker e Date to Date, con quella tabella e la tabella DimCurrencyRates e DimDate.

Nella tua matrice, inserisci il tasso di valuta non mancante per vedere che ora ha i tassi per il 27 e il 28 tutti fatti in Power Query.

La colonna Last Reported Currency Rate mostra anche i tassi dei giorni mancanti. Mostra le stesse tariffe e valori. Ma la differenza tra loro è che questa colonna viene creata in DAX.

Questa è la misura e la sintassi di quella colonna. Non è così semplice come usare solo la funzione in Power Query.

Osservando la tabella, vedrai la colonna Vendite convertite non mancanti. Mostra le vendite convertite utilizzando la colonna Tasso valuta non mancante. L'altra colonna con le vendite convertite utilizza la colonna creata in DAX.

La funzione utilizzata per ottenere le vendite convertite è . Se guardi la misura per quella colonna, puoi vedere SUMX che passa sopra la tabella DimDate.

Confronto tra metodi DAX e Power Query

È effettivamente possibile confrontare entrambe le colonne delle vendite convertite che utilizzavano DAX e Power Query in LuckyTemplates.

Per fare ciò, elimina tutte le colonne tranne le colonne di vendita convertite. Quindi, avvia l'analizzatore delle prestazioni e fai clic su Avvia registrazione.

Successivamente, apri DAX Studio. È un'applicazione che ti mostra come e perché le cose funzionano nel tuo modello. Successivamente, copia la query della tua matrice e incollala all'interno dello studio.

Per il confronto, trasforma prima uno di essi in un commento in modo che non interferisca con l'altra colonna che viene testata.

Quindi, carica i tempi del server e il piano delle query. Poiché stai confrontando le prestazioni, svuota la cache prima di eseguire il confronto.

Dopo aver eseguito il test, puoi vedere che la colonna che ha utilizzato DAX ha ricevuto molte scansioni e ha un tempo totale di 71 millisecondi.

Ora esegui l'altra colonna che utilizzava Power query. Ma prima, trasforma l'altra colonna nella sintassi in un commento.

Puoi vedere che la colonna ha funzionato solo per 25 millisecondi e ha solo 7 scansioni.

Puoi vedere chiaramente quale funziona più velocemente e meglio tra i metodi DAX e Power Query.

Conclusione

Un vantaggio dell'uso del metodo Power Query in LuckyTemplates è che i dati sono già archiviati. Quindi, la misura può trovare il tasso e quindi estrarlo utilizzando semplici funzioni.

Un'altra cosa di questo metodo è che non rallenta se il calcolo diventa complesso.

Tuttavia, questo funziona solo se i dati richiesti non devono essere convertiti al volo. In tal caso, devi utilizzare DAX.

Ti auguro il meglio,