Comment utiliser Power Query ?

Power Query est un éditeur de requêtes qui s’utilise dans Excel, dans Power BI Desktop et dans Power BI Service (à travers les Dataflows). Il s’agit d’une interface conviviale, qui permet à un usager d’Excel ou de Power BI, de se connecter ou d’importer des données de diverses sources et de les transformer. L’objectif de cette transformation est de structurer les données pour en simplifier l’analyse. Par la suite, l’usager peut simplement cliquer sur un bouton d’actualisation, pour mettre à jour les résultats de ses requêtes. Voici plus précisément comment utiliser Power Query.

 

Importer ou se connecter à des données

La première étape est d’importer des données. Pour cela, il s’agit simplement d’aller dans le menu Accueil, d’ouvrir le menu déroulant de l’option Obtenir les données et de choisir le type de données. Les types de données les plus courants seront d’abord affichés. Mais vous pouvez aussi cliquer sur Plus… pour accéder à un plus grand choix.

Obtenir des données via Power Query

 

Quelle différence entre une importation et une connexion ?

Pour comprendre les différences entre une importation de données et une connexion directe ou active à une source de données, merci de consulter cet article : Power BI : Importation de données vs connexion directe.

 

Fichiers

Vous pouvez utiliser Power Query pour importer des fichiers de toutes sortes, même des fichiers PDF. Il est aussi possible d’importer des fichiers qui seraient enregistrés dans un dossier. Par exemple, si vous faites l’extraction menuelle des données d’un système sous format .csv, vous pouvez ensuite vous connecter au dossier pour créer votre requête. Ce faisant, à l’actualisation, Power Query importera tous les fichiers du dossier, incluant les plus récents.

Importation de fichiers via Power Query

 

Bases de données

Vous pouvez utiliser Power Query pour importer ou vous connecter aux bases de données les plus courantes. S’il n’existe pas de connecteur pour vous connecter à votre base de données, vous pourrez vous connecter aux extractions de cette base de données ou bien via ODBC. En effet, s’il est possible d’installer un pilote ODBC sur votre base de données, Power Query pourra s’y connecter.

Bases de données Power Query

 

Power Platform

Vous pourrez utiliser Power Query pour vous connecter à des jeux de données publiés dans le nuage, via des solutions Micorosft. Par exemple, les Dataflows, sont des requêtes Power Query publiées dans le nuage. Pour ne pas avoir à refaire constamment la même requête pour la même source de données, on pourrait en créer une officielle et la publier dans le nuage. Il s’agirait ensuite de la récupérer via la connexion ci-dessous.

 

Azure

Vous pouvez utiliser Power Query pour vous connecter à un ensemble de services du nuage Azure de Microsoft. Par exemple, vous pouvez vous connecter à une base de données SQL Azure.

Importer données d'Azure dans Power Query

 

Services en ligne

Vous pouvez utiliser Power Query pour vous connecter à une multitude de services en ligne comme Sharepoint, SalesForce ou Google Analytics par exemple. Vous devez évidemment pour ça, avoir des identifiants de connexion.

 

Autres sources de données

Finalement, vous pouvez utiliser Power Query pour vous connecter à un ensemble d’autres données comme des données provenant de sites web, des données de Microsoft Exchange ou encore des scripts R ou des scripts Python.

 

Transformer les données

Une fois les données importées dans l’éditeur de requêtes Power Query, vous devrez vous assurer qu’elles sont présentées dans un format idéal pour l’analyse. Plus précisément, il faudra les préparer en vue de la création d’un modèle de données. Pour cela, il faudra notamment, le cas échéant, normaliser le plus possible les données. L’objectif final étant d’obtenir des tables de faits et des tables de dimensions.

Éditeur de requêtes Power Query

 

Panneau de requêtes (1)

Du moment que vous importez une source de données dans l’éditeur, vous la retrouverez dans le panneau de requêtes. À noter que vous pouvez modifier le nom, regrouper des requêtes et créer des requêtes de toute pièce en utilisant du code M dans la fenêtre de l’éditeur avancé.

 

Paramètres d’une requête

Nom (2)

Votre requête prend le nom de votre source par défaut. Vous pouvez le modifier en tout temps.

 

Aperçu (3)

La fenêtre du centre vous montre toujours un aperçu non seulement de la requête active mais aussi de l’étape (voir point 4) active.

 

Étapes appliquées (4)

Les étapes appliquées constituent le coeur de chaque requête. Une étape équivaut à une ligne de code, mais vous n’avez pas à rédiger le code vous-mêmes. Vous verrez dans les prochaines étapes qu’en cliquant plutôt sur différents menus et boutons, vous vous trouvez à générer des étapes et donc du code.

 

En cliquant sur une étape, on peut voir le code dans la barre de formule :

Exploration du code M

 

En allant dans l’éditeur avancé…

Éditeur avancé Power Query

 

… on peut voir et modifier tout le code des étapes appliquées :

Code M Power Query

 

Transformation des données

Menus du haut (5)

Vous pouvez transformer vos données de multiples façons à l’aide des différents menus du haut de votre écran. Voici quelques exemples dans chaque menu :

 

  • Accueil
    • Regrouper des données (consolidation à plus haut niveau)
    • Fusionner des tables de données côte à côte
    • Fusionner des tables de données bout à bout

 

  • Transformer
    • Extraire les x premiers caractères ou les x derniers caractères
    • Extraire tout le texte avant le premier délimiteur espace ou point ou autre
    • Fractionner une colonne par délimiteur, nombre de caractères, etc.

 

  • Ajouter une colonne
    • Insérer un calcul personnalisé
    • Ajouter une colonne de jours, mois, année, nombre de jours dans le mois, dernier jour du mois, etc.
    • Intégrer une colonne conditionnelle (si telle colonne répond à un test logique, retourne ceci sinon cela)

 

Coin supérieur gauche

Depuis le menu déroulant de l’icône de table, dans le coin supérieur gauche de l’aperçu d’une requête, il est possible d’ajouter des étapes de transformation comme le fait de prendre la première ligne de la table et de l’utiliser comme entêtes de colonnes ou encore le fait de supprimer des lignes ou de choisir les colonnes à conserver.

Options de transformation Power Query

 

Menus déroulants

Chaque colonne possède un menu déroulant. Selon la nature de la colonne (date, texte, nombre), les options de filtres seront différentes. Voici un exemple de filtres possibles avec une colonne de texte.

Modifier une colonne dans Power Query

 

Bouton droit

En cliquant sur une colonne avec le bouton droit de la souris, vous avez accès à des options supplémentaires. À noter qu’une option peut être accessible de différentes façons. Ici, vous pourrez notamment supprimer des doublons, remplir une colonne de valeurs vers le bas ou vers le haut ou encore dépivoter des colonnes.

Transformations dans Power Query

 

Code M

Pour les utilisateurs plus avancés, il est aussi possible de rédiger directement un script M pour transformer les données ou encore de créer des fonctions qui seront réutilisées dans certaines requêtes.

 

Modéliser les données

Une fois que les données sont bien préparées avec Power Query, il faut ensuite construire un modèle de données. Le modèle de données est créé directement dans la section Modèle de Power BI Desktop ou alors avec Power Pivot, si vous travaillez dans Excel. Il est recommandé de viser la construction d’un modèle en étoile. C’est seulement après, que vous pourrez commencer à créer des visualisations de données, des tableaux croisés dynamiques (si vous travaillez dans Excel) des rapports et des tableaux de bord.

 

Power Query pour Excel

 

Excel 2010 et 2013

Si vous utilisez Power Query dans Excel, vous aurez besoin de télécharger un complément pour les version Excel 2010 et 2013, depuis le site de Microsoft. Une fois téléchargé, vous devez activer le complément dans vos compléments COM. Vous verrez alors une menu Power Query apparaître dans votre barre de menus.

 

Excel 2016

Pour la version Excel 2016, Power Query est intégré mais le menu depuis Excel est “imbriqué” dans le menu Données. Il faut ensuite aller dans le menu “Nouvelle requête”.

 

Excel 2019+ et Excel via O365

Pour les versions via O365 et 2019+, Power Query est “imbriqué” dans le menu “Données”. Vous y trouverez l’option “Obtenir des données”, comme dans Power BI.

 

Power Query et la programmation VBA

Pour tout ce qui touche l’automatisation de l’importation et de la transformation de données, il est donc possible d’utiliser Power Query, en remplacement du langage VBA, beaucoup plus difficile à apprendre. Cela dit, le langage VBA permet d’autres tâches, non supportées par Power Query, comme la création de formulaires d’entrée de données par exemple ou l’envoi automatisé de courriels. Qui plus est, utilisé en combinaison, Power Query et VBA permettent aux Power Users de construire des solutions très robustes dans Excel.

 

Collaboration entre Excel et Power BI

Pour savoir comment utiliser Excel en collaboration avec Power BI, merci de consulter cet article : 5 interactions possibles entre Excel et Power BI. Vous y apprendrez notamment qu’il est possible d’utiliser Power Query et Power Pivot dans Excel, pour ensuite publier le modèle de données créé dans Power BI.

 

Commencer du bon pied avec Power Query

Le CFO masqué vous offre plusieurs formations pour apprendre à utiliser Power Query :

 

Retour en haut