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 !
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.
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.
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.
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.
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.
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:
- 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 –“.
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]}
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.
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 :
La 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é.
