Ajouter une ligne avec Power Query

Publié le 09 janvier 2023
par Kim Leblanc
Ajouter une ligne avec Power Query

Ajouter une ligne avec Power Query

Il est possible avec Power Query d’ajouter une ligne à une table de données. Pour ce faire, il faut utiliser la fonction Table.InsertRows.

Un cas pratique où utiliser cette fonction est la préparation de données qui seront intégrées dans un menu déroulant sous Excel.

Il est fortement recommandé d’utiliser des menus déroulants dans un fichier de saisie sous Excel pour s’assurer de l’uniformité des données.

 

Problématique

Le problème avec les menus déroulants intégrés par le menu Données / Validation des données est que le menu déroulant n’est pas visible avant de cliquer sur la cellule.

Effectivement, vous pouvez le constater dans l’image suivante : il y a un menu déroulant dans les cellules à droite de Client, Région et Type d’intervention. Mais on ne les voit pas !

Ajouter une ligne avec Power Query

 

Résultat souhaité

En ajoutant un élément au menu déroulant exemple : « — Sélectionnez un item –», et en sélectionnant cette valeur par défaut, la personne qui doit entrer les données saura qu’il y a un menu déroulant dans cette cellule et qu’elle doit aller sélectionner un élément.

Ajouter une ligne avec Power Query

 

Voici comment préparer les données et ajouter cet élément

Lorsque les données utilisées dans le menu déroulant proviennent d’une source externe ou d’un tableau de données, il est possible de préparer les données par le biais de Power Query et d’utiliser la fonction Table.InsertRows pour ajouter la mention : « — Sélectionnez un item –».

Données Sources

Partons d’une table de données qui contient les interventions effectuées pour des clients.

Power Query données source

 

Liste sans doublons

À l’aide de Power Query, nous avons créé 3 requêtes distinctes : une pour les clients, l’intervenant et le type d’intervention. Nous avons gardé pour chacune des requêtes la colonne souhaitée puis nous avons supprimé les doublons.

Voici en exemple les étapes pour créer le menu Type d’intervention.

Power Query supprimer colonnes

Power Query supprimer doublons

Power Query données source

 

Ajout d’une ligne

Puis nous avons ajouté une dernière étape de transformation en cliquant sur Fx qui se trouve à gauche de la barre de formule.

Power Query fonction ajouter ligne

 

Lorsqu’on ajoute une étape de transformation de cette façon, Power Query indique automatiquement la dernière étape de transformations dans la barre de formule. Dans notre exemple il s’agit de l’étape Doublons supprimés. Il faut maintenant réutiliser cette information dans la formule que nous voulons créer.

Power Query fonction

 

Paramètres de la fonction Table.InsertRows

Nous avons modifié la formule pour intégrer la fonction Table.InsertRows qui contient les paramètres suivants:

Power Query insérer ligne

Power Query

 

  • Le 1er paramètre, table as table, constitue ma table de données avant l’ajout de la ligne supplémentaire, soit l’équivalent de la dernière étape de transformations #”Doublons supprimés”.
  • Le 2e paramètre, offset as number, indique la position où l’on veut insérer la ou les lignes à ajouter. Dans le cas présent, nous avons mis 0 pour que la nouvelle ligne soit au début de la liste.

Nous aurions pu également utiliser la fonction Table.RowCount pour calculer le nombre de lignes de notre table et ajouter la nouvelle ligne à la toute fin : Table.RowCount(#”Doublons supprimés”)

  • Le 3e paramètre: rows as list, nous demande d’entrer les colonnes et données associées pour chacune des lignes que nous voulons ajouter dans notre table.

La colonne étant #”Type d’intervention” (ici nous devons entourer le nom de la colonne par un dièse et des guillemets, car le nom comporte un espace) et la donnée à ajouter “– sélectionnez un type d’intervention –“.

Power Query

 

Dans le cas où notre table contiendrait plusieurs colonnes, il faudrait ajouter la combinaison souhaitée pour chacune des colonnes. Exemple si nous avions une 2e colonne nommée Index pour laquelle nous voudrions ajouter un 0:

{[#”Type d’intervention” = “– sélectionnez un type d’intervention –“, Index = 0]}

Power Query

 

Ajouter plusieurs lignes

Puis dans le cas où nous voudrions ajouter plusieurs lignes, il faut ajouter, entre crochets [ ] , les informations pour chacune des lignes à ajouter (en séparant chacun des groupes entre crochets par une virgule).

Dans l’exemple suivant, nous ajoutons une 2e ligne avec — Ajout 2e ligne– dans la colonne Type d’intervention et un Index de 4.

Power Query ajouter 2e ligne

 

La fonction Table.InsertRows n’est pas accessible directement à partir des menus de Power Query. Cet exemple nous démontre que de bien maîtriser le Code M dans Power Query nous permet d’aller beaucoup plus loin dans nos transformations.

 

Ressources supplémentaires

Si vous voulez en connaitre davantage sur le sujet, voici un article qui vous informe sur des ressources disponibles pour apprendre le code M : Quelles sont les ressources disponibles pour apprendre le code M.

Sachez également que Le CFO masqué offre une formation sur Power Query qui contient, en 2e partie, une introduction au language M.


Fichier d’accompagnement VIP à télécharger

Pour télécharger le fichier utilisé dans ce tutoriel, devenez membre VIP du CFO masqué.

Formation complémentaire

Afin d’explorer les principales fonctions et fonctionnalités de Power Query, qui permet d’importer, de transformer et de fusionner des données de diverses sources et de pouvoir les analyser efficacement, suivez la formation Excel – Introduction à Power Query et au langage M.

 

Voici quelques commentaires d’apprenants ayant suivi cette formation :

Commentaire d'apprenant - Excel – Introduction à Power Query et au langage M
CFO-Masque_Formations-en-ligne_FBLa mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Scroll to Top