Passer des paramètres dans Power Query à l’aide d’un segment

Lors d’une formation que j’offrais récemment sur Power Query, un apprenant m’a demandé comment on pouvait passer des paramètres dans Power Query via un segment (slicer en anglais) dans Excel. Je me suis d’abord demandé à quoi cela pourrait bien servir puisque les résultats des requêtes Power Query sont au final des tables de données, que l’on peut aisément enregistrer dans Power Pivot (donc, pas de limite de taille comme dans Excel) et que l’on peut ensuite aisément filtrer par le biais de segments (slicers) dans Excel. Mais j’ai ensuite trouvé une raison intéressante de s’intéresser au sujet. D’abord, il faut savoir que le add-in Power Query est, depuis le mois de mai dernier, disponible en téléchargement pour tous les #skus d’Excel 2013. Mais Power Pivot n’est disponible que pour certains #skus (voir l’article suivant pour en apprendre davantage sur le sujet: Quelle version d’Excel faut-il pour utiliser les add-ins Power BI?). Ainsi, dans un contexte où vous utilisez Power Query pour obtenir des tables de données dans Excel (parce que vous n’avez pas accès à Power Pivot), que vous souhaitez réduire le plus possible la taille des tables résultantes (puisque les tables résultantes seront enregistrées dans une feuille Excel) et que vous souhaitez que les utilisateurs de vos fichiers n’aient pas à ouvrir Power Query pour effectuer des filtres, le truc de cet article pourrait largement vous intéresser.

Fonction Agregat (ou Aggregate en anglais) pour récupérer les valeurs du segment

Si vous n’avez pas encore eu la chance d’utiliser la fonction Agregat (ou Aggregate en anglais), je vous invite d’abord à relire l’article suivant: Excel 2010: La nouvelle fonction Aggregate (Agregat). Pour passer des paramètres dans Power Query via un segment (slicer), vous pouvez d’abord créer une table (mise sous forme de tableau) dans Excel avec une colonne qui comprend le paramètre que vous souhaitez utiliser et une colonne avec une fonction Agregat (ou Aggregate en anglais), qui permet de rapporter le nombre de cellules non vides (représenté par le 3 dans la fonction) lorsqu’une ligne n’est pas cachée (représenté par le 5 dans la fonction).

En effet, le 3 signifie que l’opération retenue est un COUNTA ou un NBVAL, soit une fonction qui compte le nombre de cellules non vides.

Le 5, quant à lui, faire référence à l’option Ignore hidden rows, i.e. Ignorer les lignes cachées.

Ainsi quand une ligne est visible, la colonne filtre montre un 1 et quand la ligne est cachée, la colonne filtre montre un 0 (mais vous ne pouvez pas encore le confirmer puisque, si vous êtes comme moi, vous n’êtes pas capable de voir des données dans des lignes cachées! À suivre…

Importation de la table de paramètres dans Power Query

Vous pouvez ensuite importer votre table de paramètres dans Power Query en cliquant dedans et en choisissant l’option From Table (À partir du tableau) de Power Query.

Fusion de table de paramètres dans Excel avec la requête Power Query

Ensuite, vous pouvez fusionner votre table de faits (ici la table buildings) avec votre table de paramètres. Dans notre exemple, la fusion se fait à partir de la colonne Month et le type de joint utilisé est un left join. Pour en savoir davantage sur les jointures de tables dans Power Query, je vous invite à relire l’article Power Query: Créer des joints entre des tables.

Une fois la fusion effectuée, ne reste plus qu’à ouvrir le menu d’expansion et de décocher la case Month pour ne retenir que la colonne Filter. C’est cette colonne qui nous permettra de filtrer la requête Power Query via le segment (ou slicer) dans Excel. Remarquez également que vous pouvez décocher l’option Use original column name as prefix pour éviter d’avoir à renommer votre colonne.

N’oubliez pas ensuite d’apposer un filtre sur la valeur 1 dans votre colonne Filter, puisque nous ne retiendrons que les lignes de données avec un 1 dans notre table de paramètres.

À ce stade-ci, votre fichier Excel comprend 3 requêtes.

• Buildings (table de faits) enregistrée avec connexion seulement
• Month (table liée de paramètres) enregistrée avec connexion seulement (note: il pourrait y avoir plusieurs tables de paramètres)
• BuildingsFiltered (fusion des 2 tables précédentes) chargée dans la feuille Excel

Comment fonctionne le filtre provenant de la table de paramètres dans Excel ?

D’abord, il faut comprendre que la colonne Filtre de notre table Excel (mise sous forme de tableau), qui comprend la fonction Agregat expliquée plus haut, rapporte un 1 lorsque la valeur est affichée et un 0 lorsque la valeur est sur une ligne cachée. Le truc est donc d’attacher un segment (slicer) à la table Excel de paramètres (mise sous forme de tableau) et de contrôler la table liée avec ce segment (slicer).

Segment (slicer) appliqué à la table Excel (mise sous forme de tableau)

Table Excel (mise sous forme de tableau)

Ainsi, quand un usager clique sur le segment Avril, la table Excel (mise sous forme de tableau) est filtrée sur le mois d’avril et la colonne 1 rapporte un 1 alors que toutes les lignes cachées rapportent 0. Vous ne pouvez pas le voir puisqu’elles sont cachées! Toutefois, comme vous avez lié cette table à Power Query, vous pouvez voir le résultat dans Power Query. Pour cela, il faut bien sûr d’abord actualiser la requête.

Ensuite, comme vous avez filtré la colonne Filter pour ne retenir que les 1, votre table de faits (Buildings) ne présentera que les données d’avril.

Vous êtes intéressé à en apprendre davantage sur Power Query?

Jetez un coup d’oeil à nos formations.

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