Depuis quelques temps déjà, je cherchais une manière de convertir les types de données dans mes requêtes Power Query, de façon automatique, afin d’avoir un code extensible et paramétrable facilement, si jamais mes données changeaient (par exemple ajout/suppression de colonnes, changement de noms de colonnes, etc.), avec le minimum de maintenance à faire.

C’est aujourd’hui chose faite, et je vous partage ici ma solution, que vous pourrez utiliser à votre tour dans vos applications Excel ou Power BI ! Cette solution m’a été inspiré par l’excellent article de Imke Feldmann sur la transformation automatique de tous les champs dans un type spécifié.

Lorsque vous importez des données à l’aide de PowerQuery, bien souvent l’outil ajoute automatiquement une étape de transformation du type des données, la détection étant basée sur les premières lignes. La formule ressemble à ça :

C’est parfait si les données ne changent plus par la suite, le format étant figé en dur dans la formule.

Maintenant, si vous ne souhaitez pas devoir retourner dans le code des requêtes dès que le format de données change, une solution est de spécifier le type de données à appliquer aux données dans une table de paramètres saisie dans Excel.

Importer les données à retraiter

Supposons que vous ayez une table de données avec deux champs : Ventes et Devise.

[code]
let
Source = #table({“Sales”,”Currency”},{{“1000″,”EUR”},{“100″,”EUR”}})
in
Source

[/code]

Changer le type manuellement

Pour définir le type manuellement, il faudrait ajouter cette étape après :

Table.TransformColumnTypes(Source,{{"Sales", type number}, {"Currency", type text}})

Mais ici je n’ai pas défini le type des données, tout comme lorsque les données sont importées de Excel, elles n’ont pas de type défini.

Ceci risque de poser un problème pour un certain nombre d’opérations dans vos requêtes ou modèle de données. D’où l’importance de toujours typer vos données avant de les charger dans Excel ou un modèle de données.

Utiliser une table de conversion de type

Le type de données à appliquer pour les ventes est Nombre et Texte pour les devises.

Pour spécifier le type, nous créons tout d’abord une table de paramètres “HeadersType” dans Excel, puis on l’importe dans Power Query. Cette table contient une colonne avec le nom des champs de données et une colonne avec le type de chaque champs. Cette table ressemble à ça :

Ensuite on ajoute le préfixe “type ” dans la colonne ColumnType (on peut alternativement renseigner directement ce préfixe dans la table) :

Prefixe = Table.TransformColumns(Source, {{"ColumnType", each "type " & _, type text}})

Puis, il faut convertir le type au format texte en format Type, voici l’étape magique qui rend cela possible :

TexteType = Table.TransformColumns(Prefixe,{{"ColumnType", Expression.Evaluate}})

Appliquer le changement de type aux données

Enfin, on applique le changement de type à la table de données à l’aide de cette étape :

Table.TransformColumnTypes(TableSansType, List.Zip({HeadersType[ColumnName], HeadersType[ColumnType]}))

L’instruction List.Zip va créer la combinaison {{“Nom de colonne1”, “Type de la colonne1”}, {“Nom de colonne2”, “Type de la colonne2”}}, qui peut être ensuite utiliser dans l’instruction de conversion de type Table.TransformColumnTypes.

📥TELECHARGER LE FICHIER D’EXEMPLE

Téléchargez le classeur Excel pour ce tutoriel. Seule votre adresse e-mail vous sera demandée dans la prochaine étape. Vous recevrez immédiatement le lien de téléchargement par e-mail.

Transformer le type de données de manière dynamique et en masse dans Excel Power Query, langage M, Power BI

Cette astuce permet de convertir les types de données de manière dynamique et en masse à l'aide d'une table de paramètres !

Télécharger

Envoyer le lien de téléchargement à :

Oui, je consens à recevoir la Newsletter. Je confirme avoir lu et accepté la Politique de confidentialité.

En savoir plus

Enfin, si vous souhaitez apprendre une méthode rapide et efficace pour collecter et analyser vos données dans Excel, et créer des rapports dynamiques calculant des variations mensuelles / trimestrielles / annuelles,  des Year-to-Date, des cumuls glissants, etc. AUTOMATIQUEMENT, alors voici un programme 100% en ligne spécialement conçu pour découvrir et maîtriser les Power Tools de Microsoft facilement :