Power Query permet l’extraction, la transformation et le chargement de données depuis une multitude de sources vers les deux outils principaux que sont Power BI et Excel. C’est surtout l’étape de transformation qui va nous intéresser dans cet ouvrage : les plus simples consistent à filtrer les lignes, choisir les champs ou encore vérifier le type de la donnée. Mais il est souvent nécessaire de nettoyer les données (harmoniser les champs de type texte, supprimer les valeurs nulles ou une ligne de total…), de créer de nouveaux champs, de calculer de nouveaux indicateurs ou de scinder un champ complexe en plusieurs valeurs distinctes.

M est le langage de programmation disponible dans Power Query qui permet de travailler plus vite et d’aller plus loin.

Ce support s’adresse à toute personne devant traiter des données avec Power Query. Les utilisateurs de Power Query pouvant s’appuyer sur l’interface graphique exclusivement, ou saisir intégralement le code M, nous avons adopté dans ce livre une approche mixte : laisser l’interface graphique générer le code et y apporter des améliorations en code M lorsque cela est utile, tant en termes de rapidité de conception qu’en termes de possibilités de transformation.

Dans le premier chapitre, nous nous intéressons aux connecteurs. Nous évoquons un certain nombre de cas courants (fichier Excel, fichier Excel avec plusieurs feuilles, plusieurs fichiers Excel, tableaux croisés), puis nous voyons l’import de fichiers plats (TXT, JSON), l’import à partir de bases de données et l’import de données issues du Web. Nous évoquons aussi les jeux de données (datasets).

Le deuxième chapitre est consacré au nettoyage et à la préparation des données. Nous y voyons comment transformer des colonnes, dépivoter un tableau croisé, transformer successivement du texte, des numériques puis des dates, comment ajouter des colonnes, combiner (associer) des tables, comment gérer les erreurs de chargement. Nous voyons aussi comment accéder au code M, comment le lire et comment, après avoir utilisé l’interface graphique, reproduire ou améliorer l’opération à l’aide du code M.

Le troisième chapitre vous propose d’aller plus loin avec Power Query et M en travaillant sur les étapes de transformation, avec l'organisation des requêtes, les paramètres, les filtres dynamiques, l’agrégation des tables. Nous évoquons également dans ce chapitre le modèle « en étoile » qui est la structure de données recommandée.

Le quatrième chapitre se concentre sur les erreurs au chargement des données : comment les déceler, comment générer un rapport d’erreurs pour les corriger.

Dans le chapitre suivant, nous plongeons dans le code M pour mieux comprendre sa structure, ses entités, créer des fonctions personnalisées, utiliser l'actualisation incrémentielle, découvrir des cas complexes de transformation ou d’extraction de données à partir du Web.

Le support se termine par une annexe dans laquelle nous évoquons l'installation et l’utilisation de R ou de Python dans Power Query.

Téléchargements

• Des fichiers complémentaires (4 251 Ko)

Introduction

1. A. Power Query, M et les produits Microsoft
2. B. À quoi sert Power Query ?
3. C. À qui s’adresse ce livre et comment est-il structuré ?
4. D. Aperçu général de l’interface de Power Query
   1. 1. Les rubans
   2. 2. Les requêtes
   3. 3. La zone centrale
   4. 4. La barre de formule
   5. 5. Les étapes
5. E. Power Query et le modèle de données
6. F. Le modèle en étoile
7. G. Power Query et le modèle composite
8. H. Les ressources
9. I. Note importante à propos des versions du logiciel Power BI Desktop

Acquérir les données

1. A. Introduction
   1. 1. Organisation des fichiers exemples et des sources
   2. 2. Les principes généraux
   3. 3. L’accès à la collection de connecteurs
2. B. Les sources de type fichier
   1. 1. Fichier Excel
      1. a. Une feuille dans un fichier
      2. b. Plusieurs feuilles dans un fichier
      3. c. Plusieurs feuilles de structure identique dans un seul fichier
      4. d. Plusieurs fichiers avec des feuilles de structure identique
      5. e. Plusieurs fichiers avec des feuilles de noms différents
      6. f. Obtenir une liste de répertoires ou une liste de fichiers
      7. g. Cas particulier du tableau croisé
   2. 2. Fichiers plats
      1. a. Fichier TXT ou CSV
      2. b. Le cas des données empilées dans un fichier TXT ou CSV
      3. c. Fichier XML
      4. d. Fichier JSON
      5. e. Fichier PDF
3. C. Les base de données
   1. 1. Base Access
   2. 2. SQL Server
      1. a. Accéder aux tables
      2. b. Effectuer une recherche sur la base
      3. c. Le mode Import et le mode DirectConnect (ou LiveConnect)
      4. d. Utiliser les options avancées
4. D. Les sources web
   1. 1. Extraire les fichiers d’un dossier SharePoint
   2. 2. Extraire un tableau d’une page web
   3. 3. Extraire une liste structurée implicite d’une page web
   4. 4. Autre sources web
      1. a. API
      2. b. Flux Odata
      3. c. Microsoft 365
      4. d. Google Analytics
5. E. Les sources de données de la Power Platform
   1. 1. Les jeux de données
   2. 2. Les flux de données (Dataflows)
   3. 3. Le Common Data Service ou Dataverse
      1. a. Connexion par le CDS
      2. b. Connexion par le connecteur Dataverse
6. F. Changer la source de données d'une requête
7. G. Exporter et partager une source de données
8. H. Épingler une source de données

Nettoyer et transformer les données

1. A. Introduction
2. B. Fonctionnement général
3. C. Lire et comprendre le code M
   1. 1. Aperçu de la structure d’un code M
   2. 2. Afficher et éditer le code M
4. D. Transformer les colonnes
   1. 1. Conserver ou supprimer les colonnes
      1. a. Conserver ou supprimer les colonnes à l’aide de l’interface
      2. b. Supprimer ou conserver les colonnes ?
      3. c. Maîtriser la suppression de colonnes à l’aide du code M
   2. 2. Ajouter les en-têtes
   3. 3. Renommer les colonnes
      1. a. Avec l’interface
      2. b. Avec M
   4. 4. Changer le type de la colonne
      1. a. Changer le type d’une colonne
      2. b. Changer le type de plusieurs colonnes d’un coup
   5. 5. Conserver ou supprimer les lignes
   6. 6. Développer les colonnes
5. E. Transformer le texte
   1. 1. Remplacer des portions de texte
   2. 2. Fractionner une colonne, extraire, recombiner
   3. 3. Ajouter un préfixe ou un suffixe
   4. 4. Nettoyer le texte à l’aide du code M
6. F. Transformer les numériques
   1. 1. Remplacer le séparateur de décimales
   2. 2. La fonction Modulo et List.Split
7. G. Transformer les dates
   1. 1. Utiliser l’interface pour générer des champs liés à la date
   2. 2. Utiliser l’interface pour créer une table du temps
   3. 3. Générer une table du temps avec le code M
   4. 4. Quelques éléments de M
      1. a. Obtenir la date d’aujourd’hui
      2. b. Convertir du texte en date
   5. 5. Travailler avec les durées
      1. a. Utiliser les fonctionnalités de calcul de durée de Power Query
      2. b. Générer un identifiant pour travailler avec une table des heures
8. H. Transformer les requêtes
   1. 1. Ajouter des colonnes
      1. a. Ajouter des colonnes à l’aide d’une formule (Colonne personnalisée)
      2. b. Ajouter des colonnes à partir d’exemples
      3. c. Ajouter des colonnes conditionnelles
      4. d. Ajouter des colonnes à l’aide d’une fonction personnalisée
      5. e. Ajouter une colonne d’index
   2. 2. Dépivoter un tableau croisé
      1. a. En utilisant l’interface
      2. b. En utilisant le code M
      3. c. Cas particulier d’un tableau avec des sous-totaux
   3. 3. Combiner des requêtes
      1. a. Empiler les lignes (ajouter)
      2. b. Remarques sur la combinaison de fichiers avec le connecteur Dossier
      3. c. Assembler les colonnes (fusionner)
   4. 4. Importer plusieurs fichiers avec des feuilles de noms différents

Exploiter la puissance de Power Query

1. A. Introduction
2. B. Organiser les requêtes et les étapes
   1. 1. Gérer les requêtes
      1. a. Créer des répertoires pour regrouper les requêtes
      2. b. Dupliquer ou référencer une requête
   2. 2. Travailler avec les étapes
      1. a. Renommer une étape
      2. b. Gérer et modifier une étape
3. C. Récupérer le contexte de la requête
4. D. Dynamiser la requête et travailler avec les paramètres
   1. 1. Travailler sur des périodes glissantes
   2. 2. Travailler avec les paramètres de requête
      1. a. Créer et utiliser un paramètre pour filtrer la requête
      2. b. Créer un paramètre de type texte avec une liste de valeurs dynamiques
      3. c. Créer et utiliser un paramètre pour changer la source de la requête
      4. d. Paramètres et modèle de document
5. E. Entrer des données
   1. 1. Entrer des données par copier-coller
      1. a. Où trouver la fonctionnalité ?
      2. b. Copier-coller à partir d’Excel
      3. c. Copier-coller à partir d’un fichier texte
      4. d. Copier-coller à partir d’un tableau PDF
      5. e. Copier-coller à partir d’un tableau Word
   2. 2. Ajouter une table par saisie manuelle
   3. 3. Ajouter une table avec du code M
      1. a. Ajouter une table du temps
      2. b. Ajouter une table et son contenu manuellement
6. F. Analyser le texte avec un produit cartésien
   1. 1. Avec un nombre de termes de recherche limité
   2. 2. Avec un produit cartésien
7. G. Agréger les tables
   1. 1. Comprendre l’agrégation
      1. a. Définition standard de l’agrégation
      2. b. Définition étendue de l’agrégation
      3. c. Pourquoi et quand recourir à un agrégat dans Power BI
      4. d. Comment s’utilise un agrégat
   2. 2. Mettre en place les données agrégées
      1. a. Agréger un indicateur dans une table dimension
      2. b. Créer une table agrégée
      3. c. Ramener un modèle en flocon à un modèle en étoile
      4. d. Utiliser la fonction d’agrégation Toutes les lignes

Gérer les erreurs au chargement

1. A. Introduction
2. B. Comment repérer les erreurs
   1. 1. Dans Power Query
   2. 2. Au moment du chargement complet des données
3. C. Supprimer les erreurs sur une colonne
4. D. Construire un rapport d’erreur lors du chargement
5. E. Erreurs liées aux paramètres régionaux
6. F. Utiliser la fonction TRY localement
7. G. Les messages d’erreurs courants
   1. 1. Expression.Error : La clé ne correspondait à aucune ligne dans la table
   2. 2. Expression.Error : Désolé... Nous n'avons pas trouvé la colonne « nom » de la table
   3. 3. DataSource.error : Le fichier <chemin/nom du fichier> est introuvable
   4. 4. Expression.Error : Le nom « Table.promoteHeaders » n'a pas été reconnu
   5. 5. DataFormat.Error : Désolé... Nous ne pouvons pas procéder à la conversion en un nombre.

Guide pratique du code M

1. A. Introduction
2. B. Présentation générale du code M
   1. 1. DAX ou M ?
   2. 2. Afficher la librairie des fonctions
   3. 3. Les étapes
   4. 4. Les types de valeurs primitives
   5. 5. Les types complexes (ou abstraits)
3. C. Réutiliser le code M entre plusieurs requêtes ou documents
4. D. Créer et utiliser une fonction personnalisée
   1. 1. Créer la fonction personnalisée
   2. 2. Utiliser la fonction personnalisée
   3. 3. Créer une fonction complexe
5. E. Travailler avec les tables
   1. 1. Manipulations courantes avec les tables
   2. 2. Fonctions de table courantes
   3. 3. Exemples de manipulations de tables
      1. a. Extraire des colonnes dynamiquement
      2. b. Ajouter un suffixe aux noms des colonnes
      3. c. Nettoyer le nom des colonnes
6. F. Travailler avec les listes
   1. 1. Manipulations courantes avec les listes
   2. 2. Fonctions de liste courantes
7. G. Travailler avec les enregistrements (records)
   1. 1. Manipulations courantes avec les enregistrements
   2. 2. Fonctions d’enregistrement courantes
8. H. Les fonctions Texte, Date et Nombre
9. I. L’expression each
10. J. Comprendre l’actualisation incrémentielle
    1. 1. Comment la mettre en place ?
    2. 2. Limitations et réserves
11. K. Extraire les données sur plusieurs pages d’un site avec le code M
    1. 1. Créer la requête sur la première page
    2. 2. Transformer cette requête en fonction personnalisée
    3. 3. Créer une table à une colonne contenant les numéros de page
    4. 4. Créer une colonne personnalisée appelant la fonction
    5. 5. Opérer des transformations
12. L. Où trouver des ressources sur M ?

R et Python dans Power Query : premier pas

1. A. Utiliser R dans Power Query
   1. 1. Installation (R et R Studio)
   2. 2. R pour importer les données
   3. 3. R pour transformer les données
2. B. Utiliser Python dans Power Query
   1. 1. Installation
   2. 2. Python pour importer les données
   3. 3. Python pour transformer les données
   10. Index