Power BI : Agréger, transformer et combiner des données

Première étape incontournable d’un projet BI, la connexion de toutes les données à traiter introduit de nombreux mécanismes et revêt de multiples aspects. Observez les informations propres à votre organisation que vous souhaitez analyser : elles proviennent souvent de multiples sources hétéroclites et vous devrez ainsi les transformer et les combiner en amont, avant d’envisager de les modéliser et de créer des relations.

Les 4 phases d’analyse de Microsoft Power BI

Un projet d’analyse à l’aide de Microsoft Power BI s’articule autour de quatre phases essentielles.

Collecte des données

Il y a tout d’abord la collecte de vos données business, à travers la connexion à de multiples sources. C’est un vrai processus d’extraction, au cours duquel vous serez fréquemment amené à transformer des données : Power Query entre alors en jeu et facilite la manipulation des champs et des enregistrements.

Modélisation de la data

Cette étape préliminaire est particulièrement minutieuse et de son succès dépend ensuite la possibilité de modéliser et d’architecturer correctement les données.

Visualisation et interprétation

Par la suite, vous serez enfin en mesure de visualiser, interpréter et partager les données.

Importation et transformation des données : la méthode expliquée

Pour expliquer l’importation et la transformation des données au sein de Power BI Desktop, envisageons un scénario simple :

Nous souhaitons intégrer un ensemble de factures sur plusieurs années, dans des formats hétéroclites, avec des colonnes ou des règles de formatage qui ne correspondent pas toujours. Après avoir lancé Power BI Desktop, cliquez sur : Obtenir les données et sélectionnez le dossier des factures 2011, par exemple.

On commence le projet Power BI Desktop par l'importation des données

On commence le projet Power BI Desktop par l’importation des données

Ce dossier comprend quatre fichiers CSV : Power BI Desktop vous les présente tous, déroulez le menu Combiner et choisissez Combiner et Modifier.

Vous visualisez ensuite la structure des données : ici, les quatre fichiers ont la même structure et le délimiteur a été deviné.

Cliquez enfin sur Modifier les requêtes afin de basculer vers Power Query. L’éditeur vous plonge au cœur des données.

  • Sur le volet droit, vous retrouvez les différentes étapes de transformation déjà appliquées. Comme nous le verrons par la suite, vous pourrez remonter à tout moment dans le temps et annuler des manipulations erronées.
  • Sur le volet gauche figurent les requêtes qui s’exécuteront notamment lorsque vous ajouterez un nouveau fichier au dossier. Enfin, la table “2011” correspond à la visualisation tabulaire de l’ensemble des factures.
L'éditeur Power Query, avec la visualisation des données et la liste des requêtes appliquées

L’éditeur Power Query, avec la visualisation des données et la liste des requêtes appliquées

Comment compléter et transformer vos données ?

Essayons à présent de charger les factures de 2012. Celles-ci se présentent sous la forme d’un fichier CSV unique. Cliquez sur Fermer et appliquer dans Power Query pour revenir à Power BI Desktop puis sur Obtenir les données. Sélectionnez Fichier > Texte/CSV puis choisissez le fichier du dossier 2012.

Les données s’ajoutent à celles déjà collectées : sélectionnez le nouveau jeu de factures importé puis cliquez sur Modifier les requêtes pour basculer vers Power Query. Pour mieux visualiser les requêtes brutes exécutées, reportez-vous à l’onglet Affichage et cochez la case Barre de formule.

Après avoir sélectionné le dernier jeu de requêtes importé, dans le volet gauche (“Factures Tri 1 2012”), vous constatez que trois étapes ont été appliquées. “Source” charge tout d’abord le fichier, en reconnaissant le délimiteur utilisé :

= Csv.Document(File.Contents("C:\Users\DataConnexion\Factures\2012\Factures Tri 1 2012.csv"),
[Delimiter=";", Columns=11, Encoding=1252, QuoteStyle=QuoteStyle.None])

L’étape “En-têtes promus” vient identifier la première ligne chargée comme l’en-tête des colonnes. Enfin, “Type modifié” va automatiquement associer le typage de toutes les données reconnues, en distinguant les dates des montants par exemple.

En cliquant sur chacune de ces étapes, vous visualisez les transformations successivement appliquées.

Intégrons à présent les deux jeux de facture ensemble.

Toujours dans l’éditeur Power Query, cliquez sur la table 2011 puis à l’onglet “Accueil”, cliquez sur Ajouter des requêtes, à la section Combiner.

Sélectionnez les factures 2012 comme table à ajouter. Validez : toutes les factures sont désormais combinées dans la table 2011.

Problème : les factures de 2012 ne présentent pas la colonne Source.Name. Déroulez le nom de cette colonne, puis cliquez sur Charger Plus : vous repérez en effet des champs vides (null).

  • Première solution, reportez-vous à l’onglet “Transformer” de Power Query et cliquez sur Remplacer les valeurs. Il vous suffit de rechercher null et de le remplacer par “Factures Tri 1 2012″par exemple. Mais vous aurez rarement le privilège de connaître à l’avance l’expression exacte à remplacer !
  • Dans ce cas, vous pouvez ajouter une nouvelle colonne personnalisée et la remplir manuellement. Sélectionnez la table “Factures Tri 1 2012” et cliquez sur Ajouter une colonne > Colonne personnalisée.
On filtre les données des colonnes, dans l'Éditeur Power Query

On filtre les données des colonnes, dans l’Éditeur Power Query

Indiquez “Source.Name” en guise de nom, puis saisissez directement le texte de remplissage, comme “Factures Tri 1 2012” par exemple. La nouvelle colonne est alors ajoutée.

Tester pour voir si l’intégration fonctionne

Pour tester son intégration au jeu de factures 2011, profitez de la souplesse de l’éditeur Power Query et revenez à l’étape précédente : cliquez sur la table 2011 et “remontez dans le temps”, en revenant vers l’étape “Type modifié” et en supprimant ainsi l’ajout des deux tables.

Cliquez sur Accueil > Combiner des fichiers et sélectionnez les factures de 2012. Power Query reconnaît immédiatement toutes les colonnes, même si elles ne sont pas dans le même ordre, et va correctement associer les données. Vous pouvez le vérifier en déroulant la colonne “Source.Name” : il n’y a plus d’enregistrements vides.

On remarque toutefois une coquille : toutes les factures de 2011 présentent l’extension “.csv” dans leur nom, et pas celles de 2012. Pour le corriger, vous allez profiter de la puissance de Power Query : sélectionnez la table “Factures Tri 1 2012” puis cliquez sur l’icône en forme d’engrenage, face à l’étape Colonne personnalisée, dans le volet droit.

Vous pouvez ici rectifier votre saisie, ce qui va immédiatement se répercuter sur la table 2011, dans laquelle vous avez combiné les données. N’oubliez pas de cliquer sur Fermer et appliquer afin de valider vos changements.

On modifie la formule d'une requête précédente et nos changements auront des répercussions immédiates

On modifie la formule d’une requête précédente et nos changements auront des répercussions immédiates

Maîtrisez les requêtes brutes

Vous l’aurez compris en manipulant les différentes étapes : l’ajout, la suppression ou la modification de l’ordre des requêtes joue un rôle fondamental sur la transformation finale. Vous devez prêter une grande attention à leur liste, sur le volet droit de Power Query, avant de valider vos changements.

Si vous avez filtré les données, par exemple, une étape peut s’ajouter automatiquement et vous empêcher de voir le jeu complet de données. Dans un autre ordre d’idée, vous aurez parfois intérêt à supprimer l’étape “Type modifié”, insérée après chaque importation, pour retyper plus convenablement chaque champ. Ce sujet fait l’objet d’un autre tutoriel, pour entrevoir l’opération plus en détail.

Pour visualiser toutes ces étapes dans leur ensemble :  cliquez sur Affichage > Éditeur avancé. Vous retrouvez, de manière brute, la requête intégrale, que vous pouvez librement copier-coller. Vous l’enregistrerez ainsi comme une base de travail, ré-importable en un clic ou à partager avec un collaborateur.

LÉditeur avancé nous renseigne sur la requête globale, que l'on peut librement copier-coller

L’Éditeur avancé nous renseigne sur la requête globale, que l’on peut librement copier-coller

À ce titre, vous pouvez rapidement dupliquer vos traitements, en copiant la requête de l’éditeur avancé. Reportez-vous à l’onglet “Accueil” de Power Query et cliquez sur Nouvelle source > Requête vide. Retournez dans l’éditeur avancé, puis collez la requête précédente : vous retrouvez la table complète.

Prenez l’habitude de vous reporter à cet éditeur avancé, pour mieux comprendre les mécanismes des étapes et pour affiner vos requêtes : vous veillerez ainsi à l’homogéniété des données connectées, une étape indispensable pour mener à bien tout projet Power BI, avant de passer à leur modélisation puis à leur visualisation.

Si vous souhaitez de l’aide pour apprendre les bases de l’outil de Microsoft, nous dispensons des cours Power BI pour vous former.