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

Publié le 17 septembre 2015
par Sophie Marchand M.Sc., CPA, CGA, MVP
Power Query et Agregat

Passer des paramètres dans Power Query à l’aide d’un slicer (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)

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).Power Query et Agregat

 

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.

Power Query et Agregat

 

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

Power Query et Agregat

 

 

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…

 

Table liée 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.

Power Query et Agregat

 

Fusion de tables dans 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.

Power Query et Agregat

 

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.

Power Query et Agregat

 

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.

Power Query et Agregat

 

À 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

Power Query et Agregat

 

Comment fonctionne le filtre?

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)

Agregat et segment

 

Table Excel (mise sous forme de tableau)

Agregat et segment

 

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.

Power Query Filtre

 

 

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.

 

CFO-Masque_Formations-en-ligne_FB Le CFO masqué offre un vaste choix de formations en informatique décisionnelle avec Excel et Power BI, via un portail en ligne et à distance en temps réel, selon un calendrier. Si vous désirez organiser des formations privées, faites nous simplement parvenir un courriel à info@lecfomasque.com . Des certificats convenant aux normes de formation continue des divers ordres professionnels du Québec sont offerts pour l'ensemble des formations.  

Découvrez quelles formations vous conviennent

 

4 réflexions sur “Passer des paramètres dans Power Query à l’aide d’un slicer (segment)”

    1. Bonjour Laurent,

      En effet, sauf si, comme je le mentionne en introduction, vous avez des données d’entrée trop volumineuses (plus d’un million de lignes) et que vous avez seulement Power Query et non Power Pivot. Dans ce cas, vous ne pourriez pas enregister le résultat ni dans Excel, ni dans Power Pivot (pour ensuite apposer un filtre). C’est ce que j’explique en introduction.

      Au plaisir,

      Sophie

Laisser un commentaire

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

Scroll to Top