stepsAI 02

Power Query – Optimisation avec les fonctions et les variables

Par Simon-Pierre Morin, ing. – Mars 2024
(niveau intermédiaire)

Suite à l’article sur l’optimisation des étapes, je propose maintenant d’améliorer nos méthodes de travail avec Power Query de manière à être plus efficace et avoir des requêtes plus faciles à maintenir. Les exemples mentionnés dans cet article se basent sur le code obtenu dans l’article «Diminuer les étapes». Sachez que l’ordre n’a pas d’importance, et que le fichier d’exemple inclut les modifications de toute cette série d’articles sur l’optimisation.

À partir du code de l’article précédent, améliorons la maintenabilité et la lisibilité en utilisant:

  • des paramètres,
  • des constantes,
  • des fonctions personnalisées,
  • des enregistrements
  • simulation d’un switch-case en remplacement du if-else.

Paramètres

Les paramètres et les constantes sont des valeurs globales nommées dans Power Query qui peuvent être invoquées de partout dans les requêtes. Il est possible de gérer les paramètres facilement dans l’éditeur de Power Query sans l’aide de l’éditeur avancé. Pour ce faire, dans le ruban «Accueil», dans le groupe «Paramètre», cliquer sur «Gérer les paramètres» (en anglais: Home > Parameters > Manage parameters)

image 2
image 3

Dans l’éditeur de requête, les paramètres auront un look semblable à ceci

image 4

Et dans l’éditeur avancé, les paramètres ressembleront à ceci

"current value" meta [IsParameterQuery=true, List={"value1", "value2", ...}, DefaultValue="default", Type="...", IsParameterQueryRequired=true]

Où seules les métadonnées IsParameterQuery, Type et IsParameterQueryRequired sont requises.

Constantes

Les constantes sont comme les paramètres à l’exception qu’elles ne peuvent pas être modifiées par les utilisateurs après la publication. Dans leur déclaration, nous ne garderons que la métadonnée Type.

"value" meta [Type="..."]

Dans l’éditeur visuel, les constantes ressemblent à ceci

image 5

Pour un débutant, je suggère de créer la constante comme un paramètre afin de s’assurer de la syntaxe puis d’altérer le paramètre pour en faire une constante, mais après un certain temps, vous créerez probablement vos constantes manuellement.

Il est également possible d’utiliser les variables globales (des requêtes n’ayant qu’une valeur comme ceci, sans définir de type (causant ainsi le système à considérer le type «Any» et à accepter toutes les valeurs)

image 6

Toutefois, il est considéré comme une meilleure pratique d’utiliser une constante globale plutôt qu’une variable globale puisqu’elle force la vérification du type. Comme les constantes et les paramètres sont appelés à être utilisés dans plusieurs autres requêtes, forcer le type s’assure de l’intégrité des données et évite les erreurs.

Fonctions personnalisées

Il y a parfois certaines étapes intermédiaires qui sont présentes seulement pour ajouter de l’information. Il peut même arriver que nous réutilisions ce code plus d’une fois dans l’ensemble de nos requêtes. Dans l’exemple de base, c’est le cas pour les étapes Icon (qui prépare une information intermédiaire) et addTitlePlus (qui effectue le formatage). Pire encore que d’avoir des étapes intermédiaires inutiles, une autre étape servant à supprimer ces résultats intermédiaires est ajoutée par la suite. Précédemment, nous avons utilisé une sous-étape pour le formatage, mais un gain de performance et de maintenabilité plus important pourrait être réalisé en se créant une fonction. Cette fonction servirait à calculer l’icône qui doit être ajoutée dans l’étape addTitlePlus.

Cette fonction peut être mise dans une requête fonctionnelle plutôt que dans la requête courante pour être invoquée de partout. Dans l’exemple, j’ai mis cette fonction dans un enregistrement de fonctions utilitaire, mais cette organisation est optionnelle.

getIcon = (wit as text) as text =>
    if wit = "Epic" then "👑"
    else if wit = "Feature" then "🏆"
    else if wit = "User Story" then "📘"
    else if wit = "Bug" then "🐞"
    else if wit = "Action Plan" then "📑"
    else if wit = "Issue" then "🚩"
    else if wit = "Task" then "📋"
    else if wit = "Test Case" then "📃"
    else if wit = "Test Plan" then "🗃"
    else if wit = "Test Suite" then "📂"
    else "🚧"
,

Et dans l’étape pour l’ajout de la colonne du titre formaté, nous n’avons qu’à appeler la fonction

addTitlePlus = Table.AddColumn(
    url,
    "Title+",
    each utils[getIcon]([WorkItemType]) & " " & [Title],
    type text
)

Ou encore, si la colonne Title n’était jamais utilisée sans l’icône, plutôt que d’ajouter une colonne, nous pourrions simplement changer la colonne d’origine :

formatTitle =  Table.ReplaceValue(
    url,
    each [Title],
    each utils[getIcon]([WorkItemType]) & " " & [Title],
    Replacer.ReplaceText,
    {"Title"}
)

L’enregistrement de fonctions n’est pas obligatoire. C’est une préférence personnelle de n’avoir qu’une seule requête nommée «utils» dans laquelle je regroupe mes fonctions utilitaires plutôt que d’avoir une requête par fonction. L’un ou l’autre n’a aucun impact sur les performances. Nous verrons plus tard qu’il peut être avantageux d’utiliser des enregistrements plutôt que de laisser les fonctions libres, mais à ce stade, vous pouvez y aller à votre convenance puisqu’il n’y a aucun gain réel à l’un ou à l’autre. Pour la suite des exemples, sachez que mes fonctions utilitaires ont été mises dans un enregistrement.

L’avantage des fonctions utilitaires, en plus d’économiser des étapes et d’être réutilisables, est au niveau de la maintenabilité. Supposons que la fonction getIcon est utilisée dans cinq requêtes dans l’ensemble du projet. Si, après un certain temps, nous désirons ajouter un nouveau type (par exemple « Documentation ») qui n’est pas déjà présent, nous n’aurions que la fonction utilitaire à modifier pour ajouter le type en question et lui affecter son icône. Également, nous ne sommes plus dépendants du nom des colonnes puisque leur contenu est passé en paramètre. Cela permet d’avoir le même comportement en étant détaché du conteneur. Nous pourrions même aller plus loin en créant une fonction de formatage (qui prendrait le type et le titre en paramètre) pour uniformiser la mise en forme des titres. Cette fonction peut servir à la fois dans la création ou la modification de colonnes :

formatTitle = (wit as text, title as text) as text => getIcon(wit) & " " & title,

Dans cet exemple, j’ai réutilisé le getIcon déjà présent dans ma requête utilitaire, mais les deux fonctions auraient pu n’être qu’une seule.

L’appel à cette fonction se fait de la même manière que précédemment :

formatTitle = Table.ReplaceValue(
    url,
    each [Title],
    each utils[formatTitle]( [WorkItemType], [Title] ),
    Replacer.ReplaceText,
    {"Title"}
)

Voici à quoi pourrait ressembler la requête de fonctions utilitaires pour cet exemple en y ajoutant une fonction de formatage d’URL qui prend en considération que «org» est un paramètre ou une constante globale :

let

    getIcon = (wit as text) as text =>
        if wit = "Epic" then "👑"
        else if wit = "Feature" then "🏆"
        else if wit = "User Story" then "📘"
        else if wit = "Bug" then "🐞"
        else if wit = "Action Plan" then "📑"
        else if wit = "Issue" then "🚩"
        else if wit = "Task" then "📋"
        else if wit = "Test Case" then "📃"
        else if wit = "Test Plan" then "🗃"
        else if wit = "Test Suite" then "📂"
        else "🚧",

    formatTitle = (wit as text, title as text) as text => getIcon(wit) & " " & title,

    formatURL = (proj as text, id as number) as text =>
        "https://dev.azure.com/"& org & "/" & proj &"/_workitems/edit/"&Text.From(id),

    utils = [
        getIcon = getIcon,
        formatTitle = formatTitle,
        formatURL = formatURL
    ]

in utils

Cette technique n’est réellement utile que si nous devons faire ces opérations plus d’une fois. N’en reste pas moins que pour la maintenabilité, la performance et la lisibilité, les fonctions personnalisées restent avantageuses.

La puissance des enregistrements

La solution actuelle présente encore une amélioration possible. La structure if…elseif…else apporte une perte en efficacité, mais plusieurs s’y rabattent puisque la structure switch…case n’existe pas en Power Query. Toutefois, il existe une astuce pour être aussi efficace qu’une structure switch, et c’est d’utiliser un enregistrement (Record en anglais).

Dans notre requête utilitaire, créons un enregistrement de nos différents types d’items comme clé et leur icône comme valeur.

iconSet = [
   Epic =  "👑",
   Feature = "🏆",
   #"User Story" = "📘",
   Bug = "🐞",
   #"Action Plan" = "📑",
   Document = "🖺",
   Issue = "🚩",
   Task = "📋",
   #"Test Case" = "📃",
   #"Test Plan" = "🗃",
   #"Test Suite" = "📂"
]

Prenez note que les clés sont considérées comme des noms de variables devant répondre au même critère de nomenclature. Il est donc impératif de mettre les noms qui inclue des caractères spéciaux (espaces, accents, trait d’union) entre guillemets précédés par un croisillon (#) comme on peut le voir avec #»User Story», #»Action Plan» et les items relatifs aux tests.

En procédant ainsi, il est possible de modifier notre fonction getIcon comme ceci:

getIcon = (wit as text) as text => Record.FieldOrDefault(iconSet, wit, "🚧")

où notre valeur par défaut est inscrite dans la fonction FieldOrDefault plutôt que dans notre structure conditionnelle. Cette fonction demande essentiellement de retourner la valeur de la clé wit (WorkItem Type) de notre enregistrement iconSet. Cette opération est quasi instantanée puisqu’il s’agit d’une référence plutôt que de prendre les quelques millisecondes pour chaque test de condition, ce qui peut s’avérer extrêmement lourd pour des vérifications plus complexes.

Il en va de même pour n’importe quel type d’opération nécessitant une condition basée sur une valeur. Comme les enregistrements sont une structure clé-valeur, dont la clé doit être une chaîne de caractère et la valeur est de type ANY, il est possible d’y mettre n’importe quoi (une valeur primitive, une fonction, une liste, un autre enregistrement, ou même une table.) Garder en tête que les enregistrements sont de loin la structure la plus polyvalente de Power Query qui à elle seule, lorsque bien utilisée, peut simplifier un problème complexe.

Conclusion – solution actuelle

Il existe plusieurs méthodes pour optimiser les requêtes Power Query. En mettant tout ce qui a été vu dans cet article et le précédent en commun, nous obtenons la solution présentée ci-dessous. En bonus, j’ajoute la transformation des variables locales de configuration en enregistrement. Il n’y a pas de gain notable hormis le fait que la configuration se retrouve dans une étape unique qui contient toutes nos variables au début de la requête.

Paramètres globaux

org

"KuriosIT" meta [IsParameterQuery=true, Type="Text", IsParameterQueryRequired=true]

odataversion

"v4.0-preview" meta [IsParameterQuery=true, List={"v5.0-preview", "v4.0-preview", "v3.0-preview"}, DefaultValue="v4.0-preview", Type="Text", IsParameterQueryRequired=true]

Constantes globales

baseURL

"https://dev.azure.com/" meta [Type="Text"]

analyticsURL

"https://analytics.dev.azure.com/" meta [Type="Text"]

Enregistrement de fonctions utilitaires

utils

let
    iconSet = [
        Epic =  "👑",
        Feature = "🏆",
        #"User Story" = "📘",
        Bug = "🐞",
        #"Action Plan" = "📑",
        Document = "🖺",
        Issue = "🚩",
        Task = "📋",
        #"Test Case" = "📃",
        #"Test Plan" = "🗃",
        #"Test Suite" = "📂"
    ],

    getIcon = (wit as text) as text => Record.FieldOrDefault(iconSet, wit, "🚧"),

    formatTitle = (wit as text, title as text) as text => getIcon(wit) & " " & title,

    formatURL = (proj as text, id as number) as text => 
        baseURL & org & "/" & proj &"/_workitems/edit/"&Text.From(id),

    utils = [getIcon = getIcon, formatTitle = formatTitle, formatURL = formatURL]
in utils

Requête

let 
    setup = [
        feed = "WorkItems",
        query = "$select=WorkItemId, State, Title, WorkItemType, Priority, Risk, Severity, StoryPoints, TagNames, StateCategory, ParentWorkItemId"&
    "&$expand=Area($select=AreaName,AreaPath), Project($select=ProjectId,ProjectName), Iteration($select=IterationName,IterationPath,IsEnded), AssignedTo($select=UserName)"&
        "&$orderby=WorkItemId asc",
        fullquery = feed & "?" & query
    ],
    Source = OData.Feed(analyticsURL & org & "/_odata/" & odataversion & "/" & setup[fullquery], null, [Implementation="2.0"] ),

    expand = let
        expandProject = Table.ExpandRecordColumn(
            Source,
            "Project",
            {"ProjectName", "ProjectId"}
        ),
        expandArea = Table.ExpandRecordColumn(
            expandProject,
            "Area",
            {"AreaName", "AreaPath"}
        ),
        expandIteration = Table.ExpandRecordColumn(
            expandArea
            "Iteration"
            {"IterationName", "IterationPath", "IsEnded"}
        ),
        expandAssign = Table.ExpandRecordColumn(
            expandIteration,
            "AssignedTo",
            {"UserName"},
            {"AssignedTo.UserName"}
        )
    in expandAssign,
    url = Table.AddColumn(
        expand,
        "URL",
        each utils[formatURL]([ProjectName],[WorkItemId]),
        type text
    ),
    formatTitle = Table.ReplaceValue(
        url,
        each [Title],
        each utils[formatTitle]( [WorkItemType], [Title] ),
        Replacer.ReplaceText,{"Title"})
in
    formatTitle

Vous trouverez le fichier Power BI qui inclue cet exemple Power Query en suivant ce lien. Cet exemple présente également quelques cas en DAX qui seront présentés dans le prochain article.

Toujours curieux?

D’autres articles sur l’optimisation et sur des techniques avancées sont à venir. Pour ceux qui souhaitent poursuivre et en apprendre plus, je les invite à suivre la série de blogues intitulée « Série – La création d’un tableau de bord agile à l’aide de Power BI » de Simon-Pierre Morin et Mathieu Boisvert.