Créer des formules Power Query dans Excel

Le Éditeur Power Query fournit une expérience de requête de données et de mise en forme pour Excel que vous pouvez utiliser pour remodeler les données de nombreuses sources de données. Pour afficher la fenêtre Éditeur Power Query, importez des données à partir de sources de données externesdans une feuille de calcul Excel, sélectionnez une cellule dans les données, puis sélectionnez Interroger > Modifier. Voici un résumé des composants main.

1. Ruban Éditeur Power Query que 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 dans le ruban

4. Aperçu des données qui affiche les résultats des étapes appliquées aux données

5. Volet Paramètres de requête qui répertorie 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.

Il peut arriver que vous souhaitiez modifier ou créer une formule. Les formules utilisent le langage de formule Power Query, que vous pouvez utiliser pour générer des expressions simples et complexes. Pour plus d’informations sur la syntaxe, les arguments, les remarques, les fonctions et les exemples, consultez Power Query langage de formule M.

À l’aide d’une liste de championnats de football comme exemple, utilisez Power Query pour prendre les données brutes que vous avez trouvées sur un site web et les transformer en un tableau bien mis 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 de la requête sous Étapes appliquées et dans la barre de formule.

Procédure

1. Pour importer les données, sélectionnez Données > à partir du 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 sélectionnez 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 de la requête , sous Propriétés, supprimez « Résultats [Modifier] », puis entrez « UEFA champs ».

4. Pour supprimer les colonnes indésirables, sélectionnez les première, quatrième et cinquième colonnes, puis sélectionnez Accueil > Supprimer la colonne > Supprimer d’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 contenant le mot « Année », sélectionnez la flèche de filtre dans Colonne1, décochez la case case activée 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 remplacez « Column1 » par « Year », « Column4 » par « Winner » et « Column5 » par « Final Score ».

8. Pour enregistrer la requête, sélectionnez Accueil > Fermer & Charger.

Result (Résultat)

Le tableau suivant est un résumé de chaque étape appliquée et de la formule correspondante.

Étape de requête et tâche	Formule
Source Se connecter à une source de données web	= Web.Page(Web.Contents("http://en.wikipedia.org/wiki/UEFA_European_Football_Championship"))
Navigation Sélectionner la table à connecter	=Source{2}[Data]
Type modifié Modifier les types de données (ce que Power Query fait automatiquement)	= Table.TransformColumnTypes(Data2,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}})
Autres colonnes supprimées Supprimer les autres colonnes pour afficher uniquement les colonnes utiles	= Table.SelectColumns(#"Changed Type",{"Column1", "Column4", "Column5"})
Valeur remplacée Remplacer des valeurs pour propre des valeurs dans une colonne sélectionnée	= Table.ReplaceValue(#"Removed Other Columns","Details","",Replacer.ReplaceText,{"Column1"})
Lignes filtrées Filtrer les valeurs d’une colonne	= Table.SelectRows(#"Replaced Value", each ([Column1] <> "Year"))
Colonnes renommées Modification des en-têtes de colonne pour qu’ils soient significatifs	= Table.RenameColumns(#"Filtered Rows",{{"Column1", "Year"}, {"Column4", "Winner"}, {"Column5", "Final Score"}})

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 est affichée par défaut, mais si elle n’est pas visible, vous pouvez la réafficher.

• Sélectionnez Afficher > Disposition > Barre de formule.

Edit une formule dans la barre de formule

1. Pour ouvrir une requête, recherchez-en une précédemment chargée à partir du É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 de la requête , sous Étapes appliquées, sélectionnez l’étape que vous souhaitez 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 conserver également Column2 :
   
   Before : = Table.SelectColumns(#"Changed Type",{"Column4", "Column1", "Column5"})
   After := Table.SelectColumns(#"Changed Type",{"Column2", "Column4", "Column1", "Column5"})

4. Sélectionnez l’icône Entrer ou appuyez sur Entrée pour afficher les nouveaux résultats dans l’aperçu des données.

5. Pour afficher le résultat dans une feuille de calcul Excel, sélectionnez Accueil > Fermer & Charger.

Créer une formule dans la barre de formule

Pour un exemple de formule simple, nous allons convertir une valeur de texte en casse appropriée à l’aide de la fonction Text.Proper.

1. Pour ouvrir une requête vide, dans Excel, sélectionnez Données > Obtenir des données > à partir 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 l’aperçu des données .

3. Pour afficher le résultat dans une feuille de calcul Excel, sélectionnez Accueil > Fermer & Charger.

Résultat :

 Lorsque vous créez une formule, Power Query valide la syntaxe de formule. Toutefois, lorsque vous insérez, réorganisez ou supprimez une étape intermédiaire dans une requête, vous pouvez potentiellement interrompre une requête.  Vérifiez toujours les résultats dans 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 en fonction de 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 du É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 de la requête, sous Étapes appliquées, sélectionnez l’icône Modifier les paramètres de l’étape à modifier ou cliquez avec le bouton droit sur l’étape, puis sélectionnez Modifier les paramètres.

3. Dans la boîte de dialogue, apportez 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 dans les é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 de colonnes, qui sont utilisés dans l’une des étapes qui suivent l’étape insérée.

1. Dans le volet Paramètres de la requête , sous Étapes appliquées, sélectionnez l’étape à laquelle vous souhaitez immédiatement précéder la nouvelle étape et sa formule correspondante.

2. Sélectionnez l’icône Ajouter une étape à gauche de la barre de formule. Vous pouvez également cliquer avec le bouton droit sur une étape, puis sélectionner Insérer étape après. Une nouvelle formule est créée au format :
   
   = <nameOfTheStepToReference>, comme =Production.WorkOrder.

3. Tapez la nouvelle formule au format :
   
   =Class.Function(ReferenceStep[,otherparameters])
   
   Par exemple, supposons que vous disposez d’une table avec la colonne Gender et que vous souhaitez 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éorganiser une étape

• Dans le volet Paramètres des requêtes 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 Supprimer à 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 Supprimer est également disponible à gauche de la barre de formule.

Dans cet exemple, nous allons convertir le texte d’une colonne en casse appropriée à l’aide d’une combinaison de formules dans le Éditeur avancé. 

Par exemple, vous disposez d’un tableau Excel, appelé Commandes, avec une colonne ProductName que vous souhaitez convertir en casse appropriée. 

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ées par la clause in , qui définit l’étape. Cet exemple retourne 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 verrez que chaque étape s’appuie sur une étape précédente en faisant référence à une étape par nom. Pour rappel, le langage de formule Power Query respecte la casse.

Phase 1 : Ouvrir le Éditeur avancé

1. 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 le É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 la fonction Excel.CurrentWorkbook comme suit :
   
   let
       Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content]
   in
       
   
   #x4Source

2. Pour charger la requête dans une feuille de calcul, sélectionnez Terminé, puis Accueil> Fermer & Charger > Fermer & Charger.

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 sélectionnez Requête > Modifier. Pour plus d’informations, voir Créer, charger ou modifier une requête dans Excel (Power Query).

2. Dans la Éditeur Power Query, sélectionnez Accueil > Éditeur avancé, qui s’ouvre avec l’instruction que vous avez créée dans Phase 2 : Définir la source de données.

3. Dans l’expression let, ajoutez #"First Row as Header » et Table.PromoteHeaders fonctionnent comme suit :
   
   let
       
   Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],   
   #x4in
   #"First Row as Header" = Table.PromoteHeaders(Source)

4. Pour charger la requête dans une feuille de calcul, sélectionnez Terminé, puis Accueil> Fermer & Charger > Fermer & Charger.

Résultat :

Phase 4 : Modifier chaque valeur d’une colonne à la casse appropriée

1. Pour ouvrir la requête, dans la feuille de calcul, 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 la Éditeur Power Query, sélectionnez Accueil > Éditeur avancé, qui s’ouvre avec l’instruction que vous avez créée dans Phase 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 affectant #« 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 & Charger > Fermer & Charger.

Résultat :

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

Afficher ou masquer la barre de formule

1. Sélectionnez Options et paramètres de> de fichiers > 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 désactivez Afficher la barre de formule.

Activer ou désactiver M IntelliSense

1. Sélectionnez Options de> de fichiers et 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, sélectionnez ou désactivez Activer M IntelliSense dans la barre de formule, l’éditeur avancé et la boîte de dialogue colonne personnalisée.

Remarque    La modification de ce paramètre prendra effet la prochaine fois que vous ouvrirez la fenêtre Éditeur Power Query.