Préparer les données dans Power Query

Opérer des transformations courantes dans l’éditeur Power Query

L’accès aux données et leur préparation sont considérés à juste titre comme l’étape clé de la création d’un bon rapport, en particulier lors de la connexion à des fichiers personnels ou des sites web. Ce type de sources est appelé « non-structuré » et s’oppose aux données structurées comme c’est le cas avec les bases de données.

L’importance du travail que demande cette étape (en termes d’efforts et de temps) ne doit en aucun cas être sous-estimée.

Cette phase du cycle de travail peut dans certains cas être prise en charge par un profil purement technique (service informatique, spécialiste des systèmes d’information, administrateur de base de données), puisqu’elle suppose une bonne connaissance du modèle de données, des relations entre entités, ou encore du typage des données.

À ce titre, elle peut faire l’objet d’une mission à part entière, dont l’objectif est de livrer un jeu de données prêt à l’emploi, permettant par la suite aux experts métiers de prendre le relais et d’effectuer leurs analyses.

La préparation des données consiste, par exemple, à :

typer la donnée...

Nettoyer les données

Voyons maintenant dans le détail les différentes opérations courantes de nettoyage des données. Selon le type de la source et sa qualité, vous serez amené à effectuer une ou plusieurs de ces opérations.

Trois opérations sont très courantes :

Vérifier que les noms des colonnes (les en-têtes) ont bien été identifiés
Vérifier la présence d’accents ou de caractères spéciaux dans les noms donnés aux colonnes et aux tables
Vérifier le type des données

Utiliser la ligne d’en-têtes

Dans la plupart des cas, Power BI est en mesure d’identifier correctement la ligne d’en-têtes lorsqu’il y en a une.

Lors de requêtes sur des fichiers Excel notamment, et en l’absence d’identifiant de ligne, il peut être nécessaire d’« aider » le logiciel : c’est en particulier le cas lorsque la table ne contient que des données texte.

Sélectionnez la table. Rétablissez la situation en cliquant sur le bouton Utiliser la première ligne pour les en-têtes présent dans les onglets Accueil et Transformer. Répétez l’opération pour l’ensemble des tables concernées.

Voici à titre d’exemple une illustration...

Filtrer

Filtrer est un terme générique désignant le fait d’écarter certaines colonnes qui ne seront pas utiles au rapport, ou de réduire le nombre de lignes d’une table. Ces deux opérations ont pour effet d’accélérer le chargement du fichier et le fonctionnement de Power BI.

Supprimer les colonnes

Lorsque vous supprimez une colonne, vous ne travaillez que sur la copie des données stockées dans le fichier Power BI. En aucun cas vous ne supprimez des données dans la source.

Vous trouverez l’outil de gestion des colonnes dans le groupe Gérer les colonnes de l’onglet Accueil :

Cet outil permet de sélectionner ou désélectionner rapidement les colonnes à conserver, par le biais de cases à cocher.

Vous pouvez aussi procéder colonne par colonne, en faisant un clic droit sur l’en-tête de celles-ci.

La taille maximale du fichier (1 Go pour la version gratuite, 10 Go pour la version Premium), ainsi que le volume de données manipulées (qui détermine en grande partie les performances de l’application) incitent fortement à ne conserver que les colonnes et lignes réellement utiles au rapport.

À chaque action que vous effectuez dans Power Query, une nouvelle étape est créée (par exemple, Colonnes supprimées) dans le volet Paramètre...

Autres opérations courantes

Remplacer les valeurs

Une fois la colonne sélectionnée, cet outil est utilisé à l’identique des autres logiciels de la suite Office, en indiquant la valeur recherchée et la valeur de remplacement.

Faites un clic droit sur l’en-tête de la colonne et sélectionnez Remplacer les valeurs ou cliquez sur le bouton Remplacer les valeurs du groupe Transformer, dans l’onglet Accueil.

Par exemple, une fois la colonne Pays de la table Commandes sélectionnée, vous pourriez remplacer Mexico par Mexique (ceci n’est qu’un exemple de transformation) :

Si vous faites la manipulation, pensez ensuite à supprimer l’étape en cliquant sur la croix rouge à gauche du nom de l’étape pour revenir à l’état initial.

Utiliser l’outil Format pour nettoyer du texte

L’outil Format (onglet Transformer - groupe Colonne Texte ou onglet Ajouter une colonne - groupe À partir d’un fichier texte) propose des transformations courantes (mise en minuscules, en majuscules, etc.) ainsi que l’ajout d’un préfixe ou d’un suffixe à votre donnée.

Ajouter des colonnes

La colonne ajoutée, qu’elle soit calculée par la source de données ou qu’elle soit calculée par le langage M de Power BI, est physiquement stockée dans le modèle tabulaire : autrement dit, elle occupe de l’espace et augmente la taille du fichier. Elle est recalculée pour chaque ligne de la table lors de l’actualisation des données. Dans Power BI, cette notion porte un nom bien précis, le contexte de ligne, dont nous allons voir l’importance lorsque nous parlerons des formules en langage DAX.

Une mesure calculée - créée à l’aide d’une formule DAX -, n’est pas stockée physiquement, et n’est évaluée (calculée) que si elle est utilisée. Elle n’utilise donc pas d’espace ou de RAM.

Créer une colonne à partir d’exemples

L’outil Colonne à partir d’exemples de l’onglet Ajouter une colonne est l’un de ces outils « magiques » de Power BI pour ajouter une colonne : il consiste, une fois la ou les colonnes originales sélectionnées, à indiquer dans la nouvelle colonne ce que vous souhaitez voir. Power BI va alors « deviner » le type de transformation que vous voulez effectuer, et en déduire la formule.

Cet outil demande parfois de fournir plusieurs exemples de la transformation demandée, suffisamment pour permettre à Power BI de déduire une règle sans ambiguïté....

Deux autres exemples de transformation

Outre les transformations du contenu de la colonne, dont nous avons vu un certain nombre dans la section précédente, deux opérations courantes permettent de travailler sur la structure des données.

La première consiste à utiliser comme source un tableau croisé et à rétablir la structure en colonnes dont a besoin Power BI (il s’agit donc de « décroiser » ou dépivoter le tableau). La seconde consiste à ajouter les lignes d’une ou plusieurs tables à celle d’une autre table ayant la même structure. Par exemple si une feuille contient des données pour le mois de janvier, la suivante les données du mois de février, et ainsi de suite, vous pouvez souhaiter regrouper ces données dans une seule table, notamment pour être en mesure de les comparer.

Dépivoter un tableau croisé

La structure sous forme de table est nécessaire à Power BI : par table, il faut comprendre une structure reposant sur des colonnes identifiant des champs distincts, dont chaque ligne constitue une valeur.

Pour cette raison, l’exploitation des données d’un tableau croisé (ou encore TCD, ou pivot) n’est pas directement possible pour Power BI : une opération initiale est nécessaire, qui va permettre de restructurer les données croisées dans un tableau en colonnes, sans perdre la moindre information.

Le fichier Tableau croisé.xlsx en est un bon exemple. Un tableau croisé dynamique y a été créé, qui indique les montants des ventes par trimestre (lignes) et année (colonnes) :

Afin d’utiliser ces données dans Power BI, il faut d’abord restructurer ce tableau en colonnes (Année, Trimestre, Ventes) et générer par là même un tableau de 16 lignes (4 années...

Les outils de gestion de la requête

L’essentiel des opérations décrites dans cette section se déroule dans le volet Requêtes, sur la gauche de l’écran de l’éditeur Power Query. Il s’agit de travailler sur les caractéristiques de la requête, ou de la source, elle-même.

Ajouter une nouvelle source

Dans un fichier, il est toujours possible d’ajouter de nouvelles données. Elles peuvent provenir de la base ou du fichier déjà utilisés, ou de toute autre source.

La question importante ici est de savoir s’il est nécessaire et possible ou non de créer une relation entre les deux sources. Tant que les données sont utilisées séparément (c’est-à-dire dans des visuels distincts), la relation (ou jointure) n’est pas nécessaire. Dans l’autre cas, elle le devient.

Pour ajouter une nouvelle source, utilisez le bouton Sources récentes depuis l’onglet Accueil - groupe Données de Power BI ou de Power Query et vous retrouvez le processus habituel. La mise en place de la relation, si elle est requise, se fera dans un autre écran, la vue Modèle, dans Power BI.

Renommer, dupliquer, supprimer une requête

Les opérations de gestion courantes des requêtes sont disponibles par clic droit sur la requête dans le volet Requêtes...

Ajouter la date d’actualisation

Générer la date d’actualisation, c’est être en mesure d’afficher, sur le rapport la date et l’heure exacte où les données ont été mises à jour (dans le rapport). C’est donc une notion qui peut se révéler capitale.

Il n’existe curieusement pas de « bouton » prêt à être utilisé pour insérer cette valeur.

Mais c’est l’occasion de voir un premier exemple de code M.

Vous trouverez avec les fichiers source, un fichier Actualisation.txt : ouvrez ce fichier et copiez tout le code qu’il contient :

let 
   ParisTime = DateTimeZone.SwitchZone(DateTimeZone.UtcNow(), 2), 
// +2 heures pour UTC+2, Paris 
   ColumnNames = {"Date d'Actualisation"}, 
   ColumnValues = {{ParisTime}}, 
   ConvertToTable = Table.FromColumns(ColumnValues, ColumnNames), 
   #"Type modifié" = Table.TransformColumnTypes(ConvertToTable, 
{{"Date d'Actualisation", type datetime}}) 
in 
   #"Type modifié"

Nous allons créer une requête à partir d’une page blanche, et coller le code.

Pour cela :

Ouvrez le fichier Ventes T1.pbix, cliquez sur Transformer...

Utiliser l’IA générative pour préparer les données

Très en vogue depuis un an, l’IA générative (ChatGPT, pour ne pas la citer), peut être un allié utile pour effectuer certaines opérations de transformation de données.

Le code de la section précédente (la date d’actualisation), a notamment été généré entièrement par ChatGPT.

Sur des données existantes, la démarche consiste à fournir à ChatGPT un échantillon de la table (typiquement, une vingtaine de lignes), et de lui demander de générer du code M pour effectuer telle ou telle opération.

Comme toujours avec l’IA générative, il est bon de pouvoir comprendre le code ainsi généré, afin de corriger des erreurs.

Une fois le code confirmé, vous pourrez l’insérer dans Power Query, en ouvrant l’éditeur avancé comme nous l’avons vu dans la section précédente. Cette opération n’est néanmoins pas triviale, et demande de connaître Power Query et le langage M. Je ne fais donc que le mentionner ici. Je vous invite à vous reporter au second livre que j’ai consacré à Power BI, Renforcer, approfondir, explorer, où la structure du code M y est vue en détail. ...