Nettoyer et transformer les données

Introduction

Avec ce nouveau chapitre, nous entrons de plain-pied dans Power Query et ses capacités à nettoyer et à transformer les données issues de la source.

Nous allons voir le fonctionnement général de Power Query, et l’articulation avec les lignes de code M.

Nous aborderons le travail au niveau des requêtes (ou tables, les termes sont synonymes), au niveau des colonnes (modifier, supprimer, ou ajouter des colonnes), la transformation des champs de type texte, numérique ou date et le traitement des tableaux croisés.

La plupart du temps, nous verrons que les opérations peuvent être réalisées à l’aide de l’interface graphique, mais aussi à l’aide du code M, et pourquoi comprendre ce code et en connaître certaines syntaxes simples permet d’accélérer le travail, et souvent de dépasser les limites de l’interface.

Les opérations que nous illustrons dans ce chapitre sont courantes, des opérations que tout utilisateur de Power Query doit connaître.

Le chapitre Exploiter la puissance de Power Query vous permettra de pousser plus loin le travail à l’aide de l’interface, avec entre autres la mise en place de tables d’agrégation, ou encore de paramètres.

Le chapitre Guide pratique du code M propose, lui, une approche plus globale de ce code, avec...

Fonctionnement général

L’accès à Power Query est possible à tout moment du travail dans Power BI : soit à partir du navigateur que nous avons vu au chapitre précédent (options Transformer les données), en d’autres termes immédiatement après la phase de connexion aux données, soit à partir de Power BI (bouton Transformer les données dans le ruban Accueil), en cours d’élaboration du rapport.

Il faut ici distinguer les sources locales (ou personnelles) et les sources d’entreprise (base de données notamment, mais aussi toute source partagée) : les premières nécessitent le plus souvent un travail de nettoyage et de préparation, ce qui est moins vrai des secondes. Mais il n’existe aucune règle définitive en la matière.

Il est utile de rappeler que le travail dans Power Query est effectué sur un échantillon de données (1000 lignes) : sur la base de cet échantillon, vous allez pouvoir définir un ensemble d’opérations (dont le nom précis est « étapes »). C’est seulement lorsque vous serez satisfait par cet ensemble d’étapes que vous allez envoyer la requête à la source, qui ramènera alors l’intégralité des lignes de la source....

Lire et comprendre le code M

let 
   Source =  
Excel.Workbook(File.Contents(ChoixSource), null, true), 
 ...

Transformer les colonnes

Transformer et nettoyer les colonnes sont les premières actions que vous réaliserez dans Power Query : cela inclut notamment supprimer les colonnes ou les lignes inutiles et vérifier ou changer le type de la colonne.

Conserver ou supprimer les colonnes

Dans cette section, nous utilisons le fichier source02.pbix.

Il existe une règle générale dans Power BI : ne ramenez que les données dont vous avez besoin. La quantité de données a un impact sur la performance de l’application, en lien avec les capacités de votre PC. Plus le fichier est léger, mieux Power BI réagira.

Conserver uniquement les colonnes nécessaires est donc la première action que vous devez faire. Cela suppose bien sûr que vous ayez une bonne idée de l’objectif que vous fixez à votre rapport. Mais rien n’est non plus définitif : vous pouvez à tout moment rajouter des colonnes ou annuler la suppression d’une colonne.

Conserver ou supprimer les colonnes à l’aide de l’interface

 Ouvrez le fichier source02.pbix.

 Dans le ruban Accueil - groupe Requêtes cliquez sur Transformer les données pour lancer Power Query.

 Dans le volet Requêtes, cliquez sur Ventes pour faire apparaître la table.

Sur la droite de la table, des colonnes superflues (Column10 à Column14) ont été extraites : nous allons les supprimer.

 En maintenant la touche Ctrl enfoncée, cliquez un à un sur les en-têtes des cinq colonnes concernées.

 Puis effectuez un clic droit pour ouvrir le menu contextuel et cliquez sur Supprimer les colonnes.

Les colonnes disparaissent, et une nouvelle étape (Colonnes supprimées) a été créée dans le volet Paramètres d’une requête. Le nom de l’étape est précédé d’une croix, qui permet la suppression et le retour à l’état avant transformation.

Il existe...

Transformer le texte

Les transformations de champs de type texte sont parmi les plus courantes, et offrent de nombreuses possibilités, tant à travers l’interface qu’à l’aide de code M. Là encore, si le recours au code n’est pas toujours nécessaire, il permet souvent d’élargir les possibilités et d’accélérer et de reproduire le traitement d’un document à un autre, ou d’une requête à une autre.

Il s’agit le plus souvent de nettoyer des champs saisis de manière libre, et donc sujet à des aléas (pensez notamment à des commentaires entrés par les utilisateurs d’un site), de remplacer un terme par un autre, ou encore de normaliser des numéros de téléphone ou des codes postaux. Il peut également s’agir de repérer dans un champ la présence d’un mot-clé, ce qui repose sur le fait que le terme recherché soit conforme au mot-clé (un terme en majuscule n’est pas « égal » au même terme en minuscule).

Un autre cas courant implique de décomposer (scinder) un code pour en extraire les composants, de façon à pouvoir analyser les données à différents niveaux.

D’un point de vue général, les fonctionnalités liées au texte se trouvent le plus souvent dans le groupe Colonne texte du ruban Transformer. Nous allons en voir divers exemples, mais voici le panorama complet des possibilités.

Elles permettent notamment de fractionner une colonne (la répartir en plusieurs colonnes distinctes), en fonction d’un délimiteur (un signe comme un tiret ou un point-virgule), en fonction d’un nombre de caractères (« les 5 premiers »), ou encore par position (il s’agit dans ce cas de préciser les positions où débutent chacune des portions de texte). Les autres possibilités offertes relèvent des transitions entre minuscules, majuscules ou chiffres que présente votre texte - dans ce cas souvent un code (par exemple 01AB2021 serait réparti en trois colonnes).

Les actions de normalisation...

Transformer les numériques

Au contraire des transformations de champs de type texte, celles qui concernent les champs numériques sont peu courantes : il est en effet plus fréquent de travailler sur les nombres en s’appuyant sur le langage d’analyse post-requête (donc le DAX pour Power BI).

D’un point de vue général, les fonctionnalités liées aux nombres se trouvent dans le groupe Colonne Nombre du ruban Transformer. Il s’agit de fonctions agrégatives, statistiques et mathématiques.

Le premier groupe de fonctions, Statistiques, abrite notamment la somme, le décompte (Compter les valeurs) et la moyenne. L’intérêt de ces fonctions à ce stade du travail deviendra plus clair lorsque nous parlerons de la mise en place des agrégats (dans le chapitre Exploiter la puissance de Power Query).

Le groupe de fonctions Standard offre un ensemble de fonctions arithmétiques de base (addition, multiplication, etc.). Si ce type d’opération est le plus efficacement réalisé lors de la mise en place de mesures après la requête, il est en revanche envisageable, sur de très gros volumes de données, de les faire réaliser par le serveur abritant...

Transformer les dates

Avant d’entrer dans le détail des transformations appliquées aux dates, il s’agit de prendre un peu de recul : la question du temps est sans aucun doute la constante que vous retrouverez dans tous les modèles de données auxquels vous aurez à faire.

La résolution idéale, recommandée, est d’importer - ou de créer, une table du temps contenant tous les découpages requis pour l’analyse (par exemple, le jour, la semaine, le mois, le trimestre, l’année).

Si cette table existe au niveau de la source, alors il ne faut pas hésiter à l’importer.

Si elle n’existe pas, elle peut être créée, soit dans Power Query, à l’aide de l’interface ou en M, soit dans Power BI, en DAX. L’avantage des codes sur l’interface est toujours le même : un simple copier-coller d’un document à un autre permet de créer sans effort et instantanément la table du temps. L’interface prend un peu plus longtemps.

Cette table du temps, créée ou importée, doit ensuite, dans le modèle (c’est-à-dire après l’étape Power Query), être liée à une ou plusieurs tables contenant une colonne Date.

Du point de vue de Power Query donc, pour peu que la table source (par exemple la table des transactions) contienne un champ date, et que ce champ soit effectivement typé en Date, le tour est joué.

Mais le champ temporel se présente parfois sous forme d’un code, 20210531 par exemple, qu’il s’agit donc de transformer. Nous avons vu un exemple de ce type de transformation dans la section Fractionner une colonne, extraire, recombiner.

Une autre solution, qui peut convenir à des scénarios et des besoins d’analyse simples et courants, consiste à ajouter dans la table source, disposant d’une colonne Date, de nouvelles colonnes calculant par exemple l’année, le mois, la semaine...

Transformer les requêtes

Cette section sera sans doute la plus longue de ce chapitre, car nous y verrons l’ajout de colonnes selon différentes méthodes, le traitement des données issues d’un tableau croisé la combinaison ou la fusion de requêtes, ou encore les cas d’importation complexes de plusieurs fichiers.

Ajouter des colonnes

Il n’est sans doute pas inutile de rappeler la différence entre colonne et mesure.

Une colonne peut être créée dans Power Query (par l’interface ou par le code M) et dans Power BI (à l’aide du DAX). Dans les deux cas, elle est calculée au moment de sa création et stockée physiquement dans le modèle. Elle occupe donc de l’espace et alourdit le modèle. Les mesures, elles, sont définies mais calculées uniquement au moment de leur utilisation. Elles n’occupent pas d’espace mais puisent dans la RAM du PC.

C’est donc une démarche qui doit être bien réfléchie. Sans qu’il y ait de règle tranchée, tout ce qui concerne les numériques (et plus précisément les indicateurs), doit être réservé aux mesures. Les colonnes sont plutôt destinées aux données qui serviront à structurer les visuels, ou filtrer un rapport (identifiants, dimensions, etc.).

Pour les utilisateurs venant d’Excel, la tentation est grande de créer des colonnes pour tout : c’est un réflexe dont il faut toujours se méfier.

L’ensemble des fonctionnalités permettant d’ajouter des colonnes se trouve dans le ruban Ajouter une colonne (nous en avons déjà vu certaines) et pour la partie qui nous intéresse ici, dans le groupe Général :

Ajouter des colonnes à l’aide d’une formule (Colonne personnalisée)

Nous venons de voir, dans la section précédente, l’ajout d’une colonne à l’aide d’une formule (fonctionnalité Colonne personnalisée du ruban Ajouter une colonne).

Cette fonctionnalité peut effectuer des opérations très simples (accoler deux termes, multiplier un chiffre par un autre - c’est-à-dire une colonne par une autre), mais aussi des opérations plus complexes...