Lab 01 : utiliser Power Query dans Excel

  • 30 minutes

Vous avez maintenant la possibilité d’essayer Power Query avec ce cas d’usage pas à pas guidé. Utilisez les exemples de fichiers de source de données fournis pour effectuer les exercices.

Lab 01 : analytique dans Excel : utilisez Power Query dans Excel

La durée estimée pour effectuer cet atelier est de 30 minutes.

Dans ce labo pratique d’apprentissage, vous effectuez les tâches suivantes :

  1. Utiliser Power Query pour se connecter à un fichier de données source .csv – Clients
  2. Utiliser les transformations Power Query pour joindre une colonne par délimiteur – Clients
  3. Utiliser Power Query pour se connecter à un fichier de données source Excel – Devis
  4. Utiliser les transformations Power Query pour dépivoter – Devis
  5. Utiliser les transformations Power Query pour nettoyer les données – Devis

Prérequis pour le labo

Les prérequis et la configuration suivants doivent être en place pour pouvoir effectuer les exercices :

  • Vous devez être connecté à Internet.

  • Microsoft Office doit être installé.

  • Inscrivez-vous à Microsoft Power BI.

  • Au minimum, vous devez disposer d’un ordinateur avec deux cœurs et de 4 Go de RAM, qui exécute une des versions suivantes de Windows : Windows 8, ou Windows Server 2008 R2 ou ultérieur.

  • Vous pouvez utiliser Microsoft Edge ou Google Chrome.

  • Vérifiez si vous avez un système d’exploitation 32 ou 64 bits pour savoir si vous devez installer les applications 32 ou 64 bits.

    Notes

    L’idéal est d’utiliser Power BI Desktop et Excel 64 bits.

    Important

    Téléchargez le contenu pour les participants : créez un dossier appelé ANALYST-LABS sur le lecteur C: de votre machine locale. Téléchargez et extrayez tout le contenu de https://aka.ms/modern-analytics-labs dans le dossier ANALYST-LABS que vous avez créé (C:\ANALYST-LABS).

  • Téléchargez et installez Power BI Desktop à l’aide de l’une des options suivantes :

    • Si vous avez Windows 10 ou ultérieur, utilisez Microsoft App Store pour télécharger et installer l’application Power BI Desktop.
    • Téléchargez et installez Microsoft Power BI Desktop.

  • Si Power BI Desktop est déjà installé, assurez-vous que vous disposez de la dernière version de Power BI.

Structure du document

Les données sources ou les fichiers de démarrage de chaque laboratoire se trouvent dans chaque dossier de labo.

  • Vous allez effectuer Lab 01 en utilisant Power Query dans l’application Excel.

  • Vous allez effectuer Lab 02A et Lab 02B en utilisant l’application Power BI Desktop.

  • Vous allez effectuer Lab 03A en utilisant les applications Power BI Desktop, Service Power BI et Excel.

  • Vous allez effectuer Lab 03B en utilisant les applications Excel et Service Power BI.

Chaque labo est fourni avec des instructions détaillées à suivre. Il contient des images d’écran pour les instructions. Les actions clés de chaque étape sont identifiées par le texte en gras. Prêtez attention aux notes, conseils et autres informations importantes. Chaque labo contient un fichier solution terminé que vous pouvez utiliser comme référence.

Vue d’ensemble

La durée estimée pour effectuer cet atelier est de 30 minutes. Dans ce labo, vous effectuez les tâches suivantes :

  1. Utiliser Power Query pour se connecter à un fichier de données source CSV – Clients

  2. Utiliser les transformations Power Query pour fractionner une colonne par délimiteur – Customers (Clients)

  3. Utiliser Power Query pour se connecter à un fichier de données source XLSX – Devis

  4. Utiliser les transformations Power Query pour supprimer le tableau croisé dynamique – Quotes (Devis)

  5. Utiliser les transformations Power Query pour nettoyer les données – Quotes (Devis)

    Notes

    Cet exercice a été créé sur la base des activités de vente d’une entreprise fictive du secteur du Wi-Fi nommée SureWi. Il est proposé par P3 Adaptive. Ces données sont la propriété de P3 Adaptive. Elles sont partagées dans le but d’illustrer les fonctionnalités d’Excel et de Power BI avec des exemples de données issus de l’industrie. Toute utilisation de ces données doit mentionner la propriété de P3 Adaptive.

Exercice 1 : utiliser Power Query pour la connexion au fichier séparé par des virgules – Clients.csv

Dans cet exercice, vous allez utiliser Excel pour vous connecter à un fichier de données sources CSV.

Tâche 1 : lancer Excel

Dans cette tâche, vous allez lancer une nouvelle feuille de calcul vide pour commencer.

  1. Lancez Excel.

    Capture d’écran du logo Excel.

  2. Créez un classeur vide.

    Capture d’écran de la sélection de l’écran Nouveau classeur vide dans Excel.

Tâche 2 : utiliser Power Query pour se connecter au fichier délimité par des virgules

Dans cette tâche, vous allez vous connecter au fichier de données sources Customers.CSV.

  1. Sélectionnez l’onglet Données dans le ruban principal Excel.

  2. Sélectionnez Obtenir les données > À partir d’un fichier > À partir d’un fichier texte/CSV.

    Capture d’écran de l’option de menu Données > À partir de l’option de menu texte/CSV.

  3. Accédez au fichier C:\ANALYST-LABS\Lab 01\MAIAD Lab 01 - Data Source - Customers.csv.

    La zone Aperçu affiche un exemple des données du client, des noms de colonnes et des valeurs.

    Notes

    Cet exemple n’est qu’un aperçu des données.

    Capture d’écran de la fenêtre Navigateur montrant un aperçu des données de Lab 01 - Source de données - Customers.csv.

  4. Sélectionnez le bouton Transformer les données. La fenêtre de l’Éditeur Power Query s’ouvre.

    Notes

    Quand vous travaillez dans Power Query, il est préférable d’agrandir au maximum la fenêtre Éditeur Power Query pour pouvoir afficher entièrement les menus, les volets et les options de la fenêtre Power Query.

  5. Par défaut, le volet Requêtes situé à gauche dans la fenêtre Éditeur Power Query est réduit. Sélectionnez la flèche dans le volet Requêtes pour le développer et l’ouvrir.

    Capture d’écran de la fenêtre Éditeur Power Query avec le volet Requêtes réduit et la flèche affichée pour développer le volet.

  6. Dans le volet Requêtes, cliquez avec le bouton droit sur le nom de requête par défaut appelé MAIAD Lab 01 – Source de données – Clients, puis Renommez la requête Clients.

    Capture d’écran du volet Requêtes ouvert, montrant la requête Lab 01 - Data Source - Customers et le bouton Renommer mis en évidence.

    Conseil

    Les requêtes que vous utilisez dans le cadre d’un modèle de données doivent avoir un nom clair, explicite et convivial, qui décrit ce que les données représentent. Par exemple : Clients, Devis, Factures, Produits, Région, etc.

Exercice 2 : Utiliser les transformations Power Query pour fractionner une colonne par délimiteur – Clients

Dans cet exercice, vous allez utiliser Power Query pour extraire le Prénom (First Name) de la colonne Contact.

Tâche 1 : Utiliser Colonne à partir d’un exemple

Dans cette tâche, vous allez créer une nouvelle colonne appelée First Name en utilisant la transformation Ajouter une colonne > Colonne à partir d’exemples pour diviser le Contact avec un délimiteur.

  1. Dans la grille Aperçu, sélectionnez la colonne Contact.

  2. Dans l’onglet Ajouter une colonne, sélectionnez la flèche bas de Colonne à partir d’exemples, puis l’option À partir de la sélection.

    Capture d’écran de la fenêtre Éditeur Power Query montrant les options du menu Ajouter une colonne.

    Notes

    Ceci ouvre une nouvelle fenêtre d’interface utilisateur nommée « Ajouter des colonnes à partir d’exemples » s’ouvre. Elle ressemble à la grille d’aperçu de Power Query, mais c’est une fenêtre distincte qui vous permet de taper la valeur proposée : Power Query peut donc identifier le modèle et la formule à appliquer pour obtenir les résultats finaux.

  3. Dans la colonne nommée [Column1] (Colonne 1) de la fenêtre Ajouter une colonne à partir d’exemples, tapez la valeur « Hugo », puis appuyez sur la touche Entrée.

    Capture d’écran de la fenêtre Ajouter une colonne à partir d’exemples montrant Hugo entré dans Colonne1.

    Notes

    Une fois que vous avez appuyé sur la touche Entrée, Power Query identifie si un modèle existe dans les données pour remplir les valeurs de toutes les lignes.

  4. Double-cliquez sur l’en-tête par défaut nommé « Text Before Delimiter » (Texte avant le délimiteur) et renommez la nouvelle colonne « First Name » (Prénom). Cliquez sur le bouton OK.

    Capture d’écran de la fenêtre Ajouter une colonne à partir d’exemples montrant le nom de la colonne par défaut renommé en Prénom.

    Notes

    Dans la grille d’aperçu de l’Éditeur Power Query, vous remarquez maintenant la NOUVELLE colonne nommée First Name, créée en analysant la valeur [First Name] du [Contact], en utilisant la transformation Colonne à partir d’un exemple.

    Capture d’écran de la grille Aperçu dans l’Éditeur Power Query montrant la nouvelle colonne Prénom.

Exercice 3 : utiliser Power Query pour la connexion au fichier XLSX – Quotes.xlsx

Dans cet exercice, vous allez utiliser Excel pour vous connecter à un fichier de données sources XLSX.

Tâche 1 : se connecter aux données source XLSX depuis la fenêtre Éditeur Power Query

Dans cette tâche, vous allez démarrer à partir de la fenêtre Éditeur Power Query.

  1. Dans le menu Accueil de Power Query, sélectionnez l’option de fichier Nouvelle source > Classeur Excel.

    Capture d’écran de la fenêtre de l’Éditeur Power Query montrant les options de l’onglet Accueil avec l’option Nouvelle source >. Fichier > Classeur Excel sélectionnée.

  2. Accédez au fichier C:\ANALYST-LABS\Lab 01\Data Source - Quotes.xlsx.

  3. Dans la fenêtre Navigateur, sélectionnez la feuille de calcul nommée « Lab 01 - Quotes ».

    Notes

    Il s’agit seulement d’un aperçu des données.

    Capture d’écran de la fenêtre du navigateur montrant la feuille de calcul Lab 01A – Devis mise en évidence.

  4. Sélectionnez le bouton OK pour charger en tant que deuxième requête dans la fenêtre Éditeur Power Query.

  5. Dans le volet Requêtes, cliquez avec le bouton droit sur le nom par défaut de la requête, « Lab 01 - Quotes », puis renommez la requête en « Quotes » (Devis).

    Capture d’écran du volet Requêtes dans la fenêtre Éditeur Power Query, avec le nom Lab 01 – Devis sélectionné pour être renommé.

Exercice 4 : Utiliser Power Query pour supprimer le tableau croisé dynamique – Quotes (Devis)

Dans cet exercice, vous allez utiliser les transformations Power Query pour structurer les données de Quotes pour Power Pivot.

Tâche 1 : utiliser la première ligne comme bouton de transformation des en-têtes

Dans cette tâche, vous allez déplacer la première ligne avec les valeurs d’en-tête de colonne vers l’en-tête du tableau.

Dans le menu Accueil, sectionnez le bouton Utiliser la première ligne comme en-tête.

Capture d’écran de l’onglet Accueil montrant le bouton Utiliser la première ligne pour les en-têtes sélectionné.

Tâche 2 : utiliser dans le menu Transformer l’option Dépivoter

Dans cette tâche, vous allez dépivoter les données de Quotes.

  1. Dans le volet Aperçu, cliquez avec le bouton droit sur la colonne CustID pour afficher les options de menu.

  2. Choisissez ensuite l’option Supprimer le tableau croisé dynamique des autres colonnes.

    Capture d’écran de la colonne CustID et de l’option Dépivoter les autres colonnes mis en évidence pour la sélection.

  3. Double-cliquez sur la colonne Attribute, puis renommez-la QuoteDate.

  4. Double-cliquez sur la colonne Value, puis renommez-la QuoteAmt.

    Avant

    Capture d’écran des en-têtes de colonnes CustID, Attribut et Valeurs avant d’être renommés.

    Après

    Capture d’écran des colonnes Attribut et Valeur renommée QuoteDate et QuoteAmt.

Exercice 5 : Utiliser Power Query pour nettoyer les données – Quotes (Devis)

Dans cet exercice, vous allez utiliser les transformations Power Query pour nettoyer les données de Quotes.

Tâche 1 : utiliser la transformation Remplacement

Dans cette tâche, vous allez utiliser une technique de remplacement pour changer QuoteDate en une date complète que vous pouvez convertie en un type de données Date.

  1. Dans la fenêtre Aperçu, cliquez avec le bouton droit sur la colonne QuoteDate pour afficher les options de menu.

  2. Ensuite, choisissez l’option Remplacer les valeurs….

    Capture d’écran de la colonne QuoteDate mettant en évidence l’option de menu Replacer les valeurs.

  3. Dans la fenêtre de l’interface utilisateur Remplacer les valeurs :

    1. Entrez un tiret - dans la zone de texte Valeur à rechercher.

    2. Entrez /1/ dans la zone de texte Remplacer par.

    Capture d’écran de la fenêtre Remplacer les valeurs montrant les entrées dans les zones de texte Valeur à rechercher et Remplacer par.

    1. Cliquez sur le bouton OK.

Tâche 2 : Utiliser l’icône Type de données

Dans cette tâche, vous allez utiliser l’icône Type de données pour changer le type de données de Texte en Date.

  1. Sélectionnez l’icône ABC dans l’en-tête de colonne QuoteDate qui indique que la colonne a un type de données Texte.

  2. Choisissez l’option Date parmi les options du menu Type de données.

    Capture d’écran de l’icône du format de données QuoteDate sélectionné.

Tâche 3 : fermer et charger le modèle de données

Dans cette tâche, vous allez charger les tables Customers et Quotes dans le modèle de données.

  1. Dans le menu Accueil, sélectionnez Fermer et charger > Fermer et charger dans....

    Capture d’écran du bouton Fermer et charger et de l’option Fermer et charger dans… mis en évidence.

  2. Dans la fenêtre Importer, sélectionnez la case d’option Créer uniquement une connexion.

    Capture d’écran de la fenêtre Importer des données affichant le bouton Créer uniquement la connexion et la case cochée Ajouter ces données au modèle de données.

  3. Cochez la case en regard de Ajouter ces données au modèle de données.

  4. Cliquez sur le bouton OK.

    Notes

    Les tables chargées s’affichent dans la fenêtre du volet Requêtes et connexions, avec le nombre total de lignes chargées.

    Capture d’écran du volet Requêtes et connexions affichant la requête Clients avec 7 560 lignes chargées et la requête Devis avec 84 307 lignes chargées.

    Notes

    À ce stade, nous nous sommes connectés aux sources de données avec Power Query et nous avons coché la case pour Ajouter ces données au modèle de données. Cependant, nous n’avons pas vu où ces données ont été chargées. Dans Lab 02A, vous allez utiliser Power BI Desktop pour importer les connexions Power Query, la table Customers et la table Quotes pour créer le modèle de données.

    Notes

    Pour modifier les requêtes et relancer la fenêtre de l’Éditeur Power Query, sélectionnez Données>Requêtes et connexions pour afficher le volet Requêtes et connexions.

    Capture d’écran du bouton Requêtes et connexions situé dans l’onglet Données.

    Notes

    Quand vous sélectionnez Fermer et charger dans... et que vous définissez les options Importer des données pour la première fois, c’est la valeur par défaut. Vous pouvez toujours modifier ces paramètres en cliquant avec le bouton droit sur la Requête dans le volet Requêtes et connexions, puis en sélectionnant l’option Charger dans... pour afficher et mettre à jour les paramètres Importer des données.

    Capture d’écran de l’option Charger dans... dans la boîte de dialogue contextuelle pour un élément du volet Requêtes et connexions.

Tâche 4 : enregistrer le fichier

Dans cette tâche, vous allez enregistrer le fichier Excel avec les connexions de requête de Customers et Quotes.

  1. Dans le ruban principal d’Excel, sélectionnez Fichier > Enregistrer.

  2. Accédez au dossier C:\ANALYST-LABS\Lab 01, puis enregistrez le fichier en tant que Lab 01 - My Solution.xlsx.

Résumé

Dans ce labo, vous avez utilisé Power Query dans Excel pour vous connecter à des fichiers de données sources CSV et XLSX, vous avez créé une colonne en utilisant Colonne à partir d’exemples, vous avez dépivoté et appliqué des transformations dans Power Query, vous avez chargé des données sources dans un modèle de données et vous avez enregistré le fichier Excel avec les connexions de données.

Capture d’écran des résultats finaux dans Excel montrant les requêtes Clients et Devis.