Créer des formules Power Query dans Excel

L’Éditeur Power Query fournit une expérience de recherche et de mise en forme des données Excel que vous pouvez utiliser pour mettre en forme les données de nombreuses sources de données. Pour afficher la fenêtre De l’Éditeur Power Query, importez des données à partir de sources de données externes dans une feuille de calcul Excel, sélectionnez une cellule dans les données, puis sélectionnez Requête >Modifier. Voici un résumé des principaux composants.

1. Ruban de l’Éditeur Power Query qui vous utilisez pour mettre en forme vos données
2. Volet Requêtes que vous utilisez pour localiser des sources de données et des tables
3. Menus contextuels qui sont des raccourcis pratiques vers les commandes du ruban
4. Aperçu des données affichant les résultats des étapes appliquées aux données
5. La requête Paramètres volet répertoriant les propriétés et chaque étape de la requête

En arrière-plan, chaque étape d’une requête est basée sur une formule visible dans la barre de formule.

Vous souhaitez peut-être parfois modifier ou créer une formule. Les formules utilisent le langage de formule Power Query, que vous pouvez utiliser pour créer des expressions simples et complexes. Pour plus d’informations sur la syntaxe, les arguments, les remarques, les fonctions et les exemples, voir le langage de formule Power Query M.

À l’aide d’une liste de football à titre d’exemple, utilisez Power Query pour prendre les données brutes que vous avez trouvées sur un site web et les transformer en table bien mise en forme. Regardez comment les étapes de requête et les formules correspondantes sont créées pour chaque tâche dans le volet Paramètres requête sous Étapes appliquées et dans la barre de formule.

Procédure

1. Pour importer les données, sélectionnez Données> web, entrez « http://en.wikipedia.org/wiki/UEFA_European_Football_Championship » dans la zone URL, puis sélectionnez OK.
2. Dans la boîte de dialogue Navigateur, sélectionnez la table Résultats [Modifier] sur la gauche, puis transformer les données en bas. L’éditeur Power Query s’affiche.
3. Pour modifier le nom de la requête par défaut, dans le volet Paramètres requête, sous Propriétés, supprimez « Résultats [Modification] », puis entrez « Champs TAUX.AUT ».
4. Pour supprimer les colonnes indésirables, sélectionnez la première, la quatrième et la cinquième colonnes, puis sélectionnez Accueil >supprimer la > supprimer les autres colonnes.
5. Pour supprimer les valeurs indésirables, sélectionnez Colonne1,sélectionnez Accueil > Remplacer les valeurs, entrez « détails » dans la zone Valeurs à rechercher, puis sélectionnez OK.
6. Pour supprimer les lignes qui inséraient le mot « Année », sélectionnez la flèche de filtre dans Colonne1,décochez la case en regard de « Année », puis sélectionnez OK.
7. Pour renommer les en-têtes de colonne, double-cliquez sur chacun d’eux, puis changez « Colonne1 » en « Année », « Colonne4 » en « Gagnant » et « Colonne5 » en « Score final ».
8. Pour enregistrer la requête, sélectionnez Accueil> fermer & chargement.

Résultat

Le tableau suivant récapitule chaque étape appliquée et la formule correspondante :

Important    Modifiez soigneusement les étapes Source, Navigation et Type modifié, car elles sont créées par Power Query pour définir et   configurer la source de données.

Afficher ou masquer la barre de formule

La barre de formule s’affiche par défaut, mais si elle n’est pas visible, vous pouvez la réafficher.

• Sélectionnez Afficher > la >barre de formule.

Editer une formule dans la barre de formule

1. Pour ouvrir une requête, recherchez-en une précédemment chargée à partir de l’Éditeur Power Query, sélectionnez une cellule dans les données, puis sélectionnez Requête >Modifier. Pour plus d’informations, voir Créer, charger ou modifier une requête dans Excel.
2. Dans le volet Paramètres, sous Étapes appliquées, sélectionnez l’étape à modifier.
3. Dans la barre de formule, recherchez et modifiez les valeurs des paramètres, puis sélectionnez l’icône  entrée ou appuyez sur Entrée. Par exemple, modifiez cette formule pour également conserver Colonne2 :Avant : = Table.SelectColumns(#”Changed Type”,{“Column4”, “Column1”, “Column5”})
   Après := Table.SelectColumns(#”Changed Type”,{“Column2”, “Column4”, “Column1”, “Column5”})
4. Sélectionnez l’icône  entrée ou appuyez sur Entrée pour afficher les nouveaux résultats dans l’aperçu des données.
5. Pour voir le résultat dans une feuille de Excel, sélectionnez Accueil et> fermer & chargement.

Créer une formule dans la barre de formule

Pour obtenir un exemple de formule simple, nous allons mettre en première forme une valeur de texte avec la fonction Texte.Proper.

1. Pour ouvrir une requête vide, dans Excel sélectionnez Données> obtenir des données > d’autres sources > requête vide. Pour plus d’informations, voir Créer, charger ou modifier une requête dans Excel.
2. Dans la barre de formule, entrez=Text.Proper(“text value”), puis sélectionnez l’icône  entrée ou appuyez sur Entrée. Les résultats s’affichent dans Aperçu des données.
3. Pour voir le résultat dans une feuille de Excel, sélectionnez Accueil et> fermer & chargement.

Résultat :

 Lorsque vous créez une formule, Power Query valide la syntaxe de la formule. Toutefois, lorsque vous insérez, réordonnez ou supprimez une étape intermédiaire dans une requête, une requête peut éventuellement rompre une requête.  Vérifiez toujours les résultats dans l’aperçu des données.

Important    Modifiez soigneusement les étapes Source, Navigation et Type modifié, car elles sont créées par Power Query pour définir et   configurer la source de données.

Modifier une formule à l’aide d’une boîte de dialogue

Cette méthode utilise des boîtes de dialogue qui varient selon l’étape. Vous n’avez pas besoin de connaître la syntaxe de la formule.

1. Pour ouvrir une requête, recherchez-en une précédemment chargée à partir de l’Éditeur Power Query, sélectionnez une cellule dans les données, puis sélectionnez Requête >Modifier. Pour plus d’informations, voir Créer, charger ou modifier une requête dans Excel.
2. Dans le volet Paramètres requête, sous Étapes appliquées, sélectionnez l’icône modifier Paramètres  de l’étape que vous voulez modifier ou cliquez avec le bouton droit sur l’étape, puis sélectionnez Modifier la Paramètres.
3. Dans la boîte de dialogue, a apporter vos modifications, puis sélectionnez OK.

Insérer une étape

Une fois que vous avez terminé une étape de requête qui remodèle vos données, une étape de requête est ajoutée sous l’étape de requête actuelle. mais lorsque vous insérez une étape de requête au milieu des étapes, une erreur peut se produire au cours des étapes suivantes. Power Query affiche un avertissement Insérer une étape lorsque vous essayez d’insérer une nouvelle étape et que la nouvelle étape modifie les champs, tels que les noms des colonnes, qui sont utilisés dans les étapes qui suivent l’étape insérée.

1. Dans le volet Paramètres, sous Étapes appliquées, sélectionnez l’étape devant précéder immédiatement la nouvelle étape et sa formule correspondante.
2. Sélectionnez l’icône Ajouter  étape à gauche de la barre de formule. Vous pouvez également cliquer avec le bouton droit sur une étape, puis sélectionner Insérer l’étape après.Une nouvelle formule est créée au format := <nameOfTheStepToReference>, par exemple, =Production.WorkOrder.
3. Tapez la nouvelle formule en utilisant le format=Class.Function(ReferenceStep[,otherparameters])Par exemple, supposons que vous avez un tableau avec la colonne Sexe et que vous voulez ajouter une colonne avec la valeur « Ms ». ou « M. », selon le sexe de la personne. La formule serait :=Table.AddColumn(<ReferencedStep>, “Prefix”, each if [Gender] = “F” then “Ms.” else “Mr.”)

Réordonner une étape

• Dans le volet Paramètres sous Étapes appliquées, cliquez avec le bouton droit sur l’étape, puis sélectionnez Monter ou Descendre.

Supprimer l’étape

• Sélectionnez l’icône  à gauche de l’étape, ou cliquez avec le bouton droit sur l’étape, puis sélectionnez Supprimer ou Supprimer jusqu’à la fin. L’icône  de suppression est également disponible à gauche de la barre de formule.

Dans cet exemple, nous allons mettre en proper case le texte d’une colonne à l’aide d’une combinaison de formules dans l’Éditeur avancé.

Par exemple, vous avez une table Excel, appelée Commandes, dont la colonne ProductName doit être convertie en nom propre.

Avant :

Après :

Lorsque vous créez une requête avancée, vous créez une série d’étapes de formule de requête basées sur l’expression let.  Utilisez l’expression let pour attribuer des noms et calculer des valeurs qui sont ensuite référencés par la clause in, qui définit l’étape. Cet exemple renvoie le même résultat que celui de la section « Créer une formule dans la barre de formule ».

let  
    Source = Text.Proper(“hello world”)
in  
    Source  

Vous constaterez que chaque étape s’appuie sur une étape précédente en y faisant référence par son nom. Pour rappel, le langage de formule Power Query est sensible à la cas.

Phase 1 : ouvrir l’Éditeur avancé

1. Dans Excel, sélectionnez Données> obtenir des > d’autres sources > requête vide. Pour plus d’informations, voir Créer, charger ou modifier une requête dans Excel.
2. Dans l’Éditeur Power Query, sélectionnez Accueil> Éditeur avancé, qui s’ouvre avec un modèle de l’expression let.

Phase 2 : définir la source de données

1. Créez l’expression let à l’aide de Excel. Fonction CurrentWorkbook comme suit :let#x1in
   Source
2. Pour charger la requête dans une feuille de calcul, sélectionnez Terminé, puis Accueil > Fermer & chargement > fermer & chargement.

Résultat :

Phase 3 : promouvoir la première ligne en en-têtes

1. Pour ouvrir la requête, dans la feuille de calcul, sélectionnez une cellule dans les données, puis> Modifier. Pour plus d’informations, voir Créer, charger ou modifier une requête dans Excel (Power Query).
2. Dans l’Éditeur Power Query, sélectionnez Accueil > Éditeur avancé, qui s’ouvre avec l’instruction que vous avez créée à l’étape 2 :Définir la source de données.
3. Dans l’expression let, ajoutez #”First Row as Header » et table.PromoteHeaders comme suit :letSource = Excel.CurrentWorkbook(){[Name=”Orders”]}[Content],   #”First Row as Header” = Table.PromoteHeaders(Source)#x3    #”First Row as Header”
4. Pour charger la requête dans une feuille de calcul, sélectionnez Terminé,puis Accueil > Fermer & chargement > fermer & chargement.

Résultat :

Phase 4 : mettre en nom propre chaque valeur d’une colonne

1. Pour ouvrir la requête, dans la feuille de calcul, sélectionnez une cellule dans les données, puis> Modifier. Pour plus d’informations, voir Créer, charger ou modifier une requête dans Excel.
2. Dans l’Éditeur Power Query, sélectionnez Accueil > Éditeur avancé, qui s’ouvre avec l’instruction que vous avez créée à l’étape 3 : promouvoir la première ligne en en-têtes.
3. Dans l’expression let, convertissez chaque valeur de colonne ProductName en texte approprié à l’aide de la fonction Table.TransformColumns, en faisant référence à l’étape précédente de formule de requête « First Row as Header », en ajoutant #”Capitalized Each Word » à la source de données, puis en attribuant #”Capitalized Each Word » au résultat in.let
       Source = Excel.CurrentWorkbook(){[Name=”Orders”]}[Content],
    #”First Row as Header” = Table.PromoteHeaders(Source),
   #”Capitalized Each Word” = Table.TransformColumns(#”First Row as Header”,{{“ProductName”, Text.Proper}})
   in
     #”Capitalized Each Word”
4. Pour charger la requête dans une feuille de calcul, sélectionnez Terminé,puis Accueil > Fermer & chargement > fermer & chargement.

Résultat :

Vous pouvez contrôler le comportement de la barre de formule dans l’Éditeur Power Query pour tous vos livres.

Afficher ou masquer la barre de formule

1. Sélectionnez Fichier> Options de l’Paramètres > Options de requête.
2. Dans le volet gauche, sous GLOBAL, sélectionnez Éditeur Power Query.
3. Dans le volet droit, sous Disposition, sélectionnez ou sélectionnez Afficher la barre de formule.

Activer ou désactiver M IntelliSense

1. Sélectionnez Fichier> Options de l’Paramètres > Options de requête.
2. Dans le volet gauche, sous GLOBAL, sélectionnez Éditeur Power Query.
3. Dans le volet droit, sous Formule, activez ou ne sélectionnez pas Activer M IntelliSense dans la barre de formule, l’éditeur avancé et la boîte de dialogue de colonne personnalisée.

Remarque    La modification de ce paramètre prendra effet à la prochaine ouverture de la fenêtre de l’éditeur Power Query.