Résolution des taux de change manquants avec Power Query dans LuckyTemplates

Dans ce didacticiel, vous apprendrez à utiliser Power Query pour les conversions de devises dans LuckyTemplates. Vous pouvez regarder la vidéo complète de ce tutoriel au bas de ce blog.

La méthode décrite dans ce blog vous aidera à résoudre les problèmes de votre tableau des taux de change en utilisant le dernier taux disponible.

Vous comprendrez comment régler les problèmes lorsque le tableau des taux de change n'a pas de taux de change chaque jour.

Table des matières

• Identification des données manquantes
• Séparer différentes devises
• Remplir les taux manquants
• Fusionner toutes les devises
• Création de relations dans le modèle de données
• Comparaison des méthodes DAX et Power Query
• Conclusion

Identification des données manquantes

Ce problème peut se produire si la table Date ne contient pas de données pour les jours fériés ou les week-ends.

Si vous n'avez pas de taux un jour et que vous n'en tenez pas compte, les mesures telles que les ventes totales ne seront pas converties.

Si vous jetez un œil à ce tableau des taux de change, il a des taux en euros pour janvier 2016.

Mais notez que les 27e et 28e jours manquent et n'ont pas de taux. Donc, si vous allez à la matrice, vous pouvez voir qu'il n'y a pas de taux pour 27 et 28.

La mesure Taux de change avec données manquantes est simplement une somme du tableau Fact Currency Rates.

Le total de 26,6983 dans le tableau n'a pas de sens. C'est juste la somme de tous les taux de change au-dessus que vous n'avez pas besoin d'additionner car ils sont ce qu'ils sont aujourd'hui.

Maintenant, cela peut être résolu dans Power Query car il s'agit davantage d'un problème de modélisation de données qui implique des requêtes et une agrégation. Cela peut également être fait avec DAX, mais c'est plus facile et plus rapide avec Power Query dans LuckyTemplates.

Cliquez donc sur Transformer les données , puis ouvrez la fenêtre Power Query.

Voici le tableau avec des données manquantes. Vous pouvez également voir dans ce tableau des taux de change qu'il contient des euros, des livres et des dollars.

Et puis c'est la table finale qui a des valeurs les 27 et 28 janvier. C'est la table ou la sortie que vous devriez avoir.

Importez vos données à partir de n'importe quelle source d'où vous les obtenez et changez simplement le type de données.

Séparer différentes devises

Ensuite, si vous avez plus d'une devise, il est important de les séparer et de les regrouper par leur ticker.

Lorsque vous les regroupez, assurez-vous de les regrouper dans Toutes les lignes afin que si vous cliquez sur Euros par exemple, seuls les taux de change en euros s'afficheront.

Vous pouvez également voir qu'il y a des jours manquants ici ainsi que la devise de la livre.

Il y a cette chose appelée Invoke Custom Function dans LuckyTemplates. Il s'agit d'une fonction avec FillMissingRates.

Si vous cliquez sur Euro, vous verrez un tableau complet contenant les données manquantes que vous avez vues dans le tableau développé. Il est trié des tarifs les plus anciens aux plus récents.

Découvrez ce que font les fonctions personnalisées. Si vous cliquez sur l'icône d'engrenage près de la fonction personnalisée Invoke, vous pouvez voir le nom de la colonne et la source extraite dans la colonne All.

Pour ce faire, commencez par un tableau de base. Effectuez les transformations, obtenez le résultat souhaité, puis utilisez-le pour créer la fonction. Cela a plus de sens que d'essayer de le faire dans le tableau où vous avez tous les autres sous-tableaux.

Par exemple, si vous souhaitez vous concentrer sur l'euro, filtrez-le jusqu'au ticker.

Pour déterminer les jours manquants, fusionnez-les avec votre table de dates.

Voici une table de devises de base filtrée et une table DimDate :

Pour le type Join, vous pouvez utiliser Full Outer pour intégrer toutes les lignes des deux tables. Cela vous montre ce qui manque et les tarifs qui correspondent.

Lorsque vous le fusionnerez, il vous donnera un tableau complet en tant que sous-tableau.

Après cela, triez les lignes, puis développez DimDate pour n'inclure que la colonne Date.

Maintenant, vous pouvez voir que tout est assorti. Et lorsque vous triez les lignes du plus ancien au plus récent, vous pouvez également voir les deux jours manquants avec des valeurs nulles.

Remplir les taux manquants

Il est important qu'ils soient dans cet ordre car vous allez remplir toutes les valeurs nulles dans toutes les colonnes sauf Date.1.

Après l'avoir rempli, vous pouvez voir que les valeurs nulles ont été remplacées par l'euro et le dernier taux disponible.

Ensuite, supprimez les colonnes inutiles comme la colonne Date du tableau.

La colonne Date.1 contient déjà les dates, la colonne Date n'est donc pas nécessaire. Ensuite, réglez le Crossrate sur Decimal Number.

Enfin, réorganisez et renommez les colonnes et assurez-vous que vos types de données sont définis.

Vous avez maintenant un tableau complet des taux de change.

Cependant, cela ne fonctionne que pour cette table. Vous devez trouver un moyen d'appliquer la même logique à un tableau où les devises sont regroupées.

Fusionner toutes les devises

Si vous avez des devises différentes, il est difficile de maintenir des tables de taux différentes. Vous devez donc les fusionner pour créer une table de devises.

Pour commencer, prenez votre table existante et créez une fonction en cliquant sur Table et en sélectionnant Créer une fonction . Ensuite, saisissez un nom de fonction.

Après cela, allez dans Advanced Editor . Supprimez les calculs dont vous n'aurez pas besoin. Dans ce cas, supprimez la source, filtrez et modifiez la syntaxe.

Ensuite, nommez votre paramètre Source puis saisissez Let. Étant donné que les lignes filtrées ont été supprimées, remplacez la valeur dans la syntaxe Merged Queries par Source. Après cela, supprimez la Source à la fin de la syntaxe, puis cliquez sur Terminé.

Maintenant, allez dans Ajouter une colonne, cliquez sur Appeler une fonction personnalisée. Entrez un nom de colonne comme Toutes les données. Pour la requête de fonction, sélectionnez la fonction qui a été créée précédemment. Choisissez Tout comme source, puis cliquez sur OK.

La prochaine chose à faire est de supprimer toutes les colonnes sauf Toutes les données. Ensuite, développez cette colonne et décochez le paramètre "Utiliser le nom de la colonne d'origine comme préfixe". Après cela, cliquez sur OK et modifiez les types de colonnes.

Définissez le type des colonnes Date, Ticker et Crossrate sur Date, Texte et Décimal respectivement. Une fois terminé, cliquez sur Fermer et appliquer.

Vous obtiendrez maintenant un tableau des taux de change combinés sans dates ni taux manquants.

Création de relations dans le modèle de données

Dans votre modèle de données, vous pouvez voir la table FactCurrencyRates . Maintenant, créez une relation, Ticker to Ticker et Date to Date, avec cette table et les tables DimCurrencyRates et DimDate.

Dans votre matrice, tirez le taux de change non manquant pour voir qu'il a maintenant les taux pour le 27 et le 28, tous effectués dans Power Query.

La colonne Last Reported Currency Rate affiche également les taux des jours manquants. Il affiche les mêmes taux et valeurs. Mais la différence entre eux est que cette colonne est créée dans DAX.

Il s'agit de la mesure et de la syntaxe de cette colonne. Ce n'est pas aussi simple que d'utiliser uniquement la fonction dans Power Query.

En regardant le tableau, vous verrez la colonne Converted Sales No Missing. Il affiche les ventes converties à l'aide de la colonne Taux de change non manquant. L'autre colonne avec les ventes converties utilise la colonne créée dans DAX.

La fonction utilisée pour obtenir les ventes converties est . Si vous regardez la mesure de cette colonne, vous pouvez voir SUMX passer par-dessus la table DimDate.

Comparaison des méthodes DAX et Power Query

Vous pouvez réellement comparer les colonnes de ventes converties qui ont utilisé DAX et Power Query dans LuckyTemplates.

Pour ce faire, supprimez toutes les colonnes à l'exception des colonnes de ventes converties. Ensuite, lancez l'analyseur de performances et cliquez sur Démarrer l'enregistrement.

Ensuite, ouvrez DAX Studio. C'est une application qui vous montre comment et pourquoi les choses fonctionnent dans votre modèle. Après cela, copiez la requête de votre matrice et collez-la dans le studio.

Pour la comparaison, transformez d'abord l'une d'entre elles en commentaire afin qu'elle n'interfère pas avec l'autre colonne en cours de test.

Et puis, chargez les minutages du serveur et le plan de requête. Puisque vous comparez les performances, videz d'abord le cache avant d'exécuter la comparaison.

Après avoir exécuté le test, vous pouvez voir que la colonne qui a utilisé DAX a reçu de nombreuses analyses et a une durée totale de 71 millisecondes.

Maintenant, exécutez l'autre colonne qui a utilisé Power query. Mais d'abord, transformez l'autre colonne de la syntaxe en commentaire.

Vous pouvez voir que la colonne n'a fonctionné que pendant 25 millisecondes et n'a que 7 analyses.

Vous pouvez clairement voir ce qui fonctionne le plus rapidement et le mieux entre les méthodes DAX et Power Query.

Conclusion

L'un des avantages de l'utilisation de la méthode Power Query dans LuckyTemplates est que les données sont déjà stockées. Ainsi, la mesure peut trouver le taux et le faire ressortir à l'aide de fonctions simples.

Une autre chose à propos de cette méthode est qu'elle ne ralentit pas si le calcul devient complexe.

Cependant, cela ne fonctionne que si les données que vous demandez ne doivent pas être converties à la volée. Si c'est le cas, vous devez utiliser DAX.

Tous mes vœux,