Créer des formules Power Query dans Excel

À l’aide de l’Éditeur Power Query, vous avez toujours créé des formules Power Query. Voyons le fonctionnement de Power Query en les regardant en l’air. Vous pouvez découvrir comment mettre à jour ou ajouter des formules en regardant l’Éditeur Power Query en action.  Vous pouvez même déployer vos propres formules avec l’Éditeur avancé.           

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 sourcesde 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.

Composants de l’Éditeur de requête

  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 contextux 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.

Exemple de formule dans l’Éditeur de requête

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.

Votre navigateur ne prend pas en charge la vidéo.

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

Résultats de la période pas à pas - les premières lignes

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

Étape et tâche de requête

Formule

Source

Connecter à une source de données web

= Web.Page(Web.Contents("http://en.wikipedia.org/wiki/UEFA_European_Football_Championship"))

Navigation

Sélectionnez le tableau à connecter

=Source{2}[Data]

Type modifié

Modifier les types de données (dans lequel Power Query effectue 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 nettoyer les valeurs d’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

En-têtes de colonne modifiés pour être significatifs

= Table.RenameColumns(#"Filtered Rows",{{"Column1", "Year"}, {"Column4", "Winner"}, {"Column5", "Final Score"}})

Important    Modifiez soigneusement les étapes Source, Navigationet 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 redisplayer.

  • Sélectionnez Afficher > la >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 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 modifierune requête dans Excel.

  2. Dans le volet Paramètres, sous Étapesappliqué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 Icône Entrée à gauche de la barre de formule dans Power Query 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 Icône Entrée à gauche de la barre de formule dans Power Query 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 modifierune requête dans Excel.

  2. Dans la barre de formule, entrez=Text.Proper("text value"), puis sélectionnez l’icône Icône Entrée à gauche de la barre de formule dans Power Query 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 :

Text.Proper

 Lorsque vous créez une formule, Power Query valide la syntaxe de la formule. Toutefois, lorsque vous insérez, réordez 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, Navigationet 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 modifierune requête dans Excel.

  2. Dans le volet Paramètres requête, sous Étapes appliquées, sélectionnez l’icône modifier Paramètres Icône des 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 remodele 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 Icône Fonction é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.")

Exemple de formule

Réorder une étape

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

Supprimer l’étape

  • Sélectionnez l’icône Supprimer l’étape à 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 l’étape 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 :

Avant

Après :

Étape 4 - Résultat

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 modifierune 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.

Éditeur avancé2

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

  1. Créez l’expression let à l’aide de Excel. Fonction CurrentWorkbook comme suit :

    let#x1

    in
        Source

    Étape 1 - Éditeur avancé

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

Résultat :

Étape 1 - 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 :

    let
        
    Source = 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 :

Étape 3 - 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 modifierune 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 :

Étape 4 - 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 IntelliSensedans 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.

Voir aussi

Aide Power Query pour Excel

Créer et appeler une fonction personnalisée

Utilisation de la liste Étapes appliquées (docs.com)

Utilisation de fonctions personnalisées (docs.com)

Formules Power Query M (docs.com)

Gérer les erreurs (docs.com)

Besoin d’aide ?

Développez vos compétences dans Office
Découvrez des formations
Accédez aux nouvelles fonctionnalités en avant-première
Rejoignez le programme Office Insider

Ces informations vous ont-elles été utiles ?

×