Power Query – Optimiser en diminuant les étapes

Par Simon-Pierre Morin, ing.
(niveau débutant-intermédiaire)

Dans le monde merveilleux d’acquisition des données avec Power Query, il arrive que certaines requêtes soient plus longues, et ce même dans l’aperçu qui ne charge que les 1000 premières lignes. Il existe plusieurs méthodes afin d’optimiser les requêtes dans Power Query. Cet article en présentera quelques-unes avec leurs avantages et inconvénients.

La raison pour laquelle cet article est considéré comme intermédiaire vient du fait qu’en utilisant ces techniques, vous perdrez la possibilité d’utiliser l’éditeur visuel pour modifier vos étapes optimisées. Il demeure possible d’utiliser l’éditeur visuel pour créer de nouvelles étapes pour, par la suite, aller les modifier avec l’éditeur avancé.

Base créée avec l’éditeur visuel

Pour l’ensemble de cet article, je vais me baser sur cet exemple de base qui serait fait dans l’éditeur de Power Query sans utiliser l’éditeur avancé. Pour des questions de lisibilité, j’ai tout de même pris la liberté d’aérer le code dans l’éditeur avancé puisque normalement chaque étape se trouve sur une seule ligne.

let
    Source = OData.Feed(
        "https://analytics.dev.azure.com/HypershellTechno/zzz_Formation/_odata/v4.0-preview/WorkItems?$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", 
        null, 
        [Implementation="2.0"] 
    ),

    expandProject = Table.ExpandRecordColumn(
        Source,
        "Project",
        {"ProjectName", "ProjectId"},
        {"ProjectName", "ProjectId"}
    ),

    expandArea = Table.ExpandRecordColumn(
        expandProject,
        "Area",
        {"AreaName", "AreaPath"},
        {"AreaName", "AreaPath"}
    ),

    expandIteration = Table.ExpandRecordColumn(
        expandArea,
        "Iteration",
        {"IterationName", "IterationPath "},
        {"IterationName", "IterationPath "}
    ),

    expandAssign = Table.ExpandRecordColumn(
        expandIteration,
        "AssignedTo",
        {"UserName"},
        {"AssignedTo.UserName"}
        ),

    Sort_byID = Table.Sort(
        expandAssign,
        {{"WorkItemId", Order.Ascending}}
    ),

    url = Table.AddColumn(
        Sort_byID,
        "URL",
        each baseURL&"/"&[ProjectName]&"/_workitems/edit/"&Text.From([WorkItemId])
    ),

    icon = Table.AddColumn(url, "Icon",
        each
            if [WorkItemType] = "Epic" then "👑"
            else if [WorkItemType] = "Feature" then "🏆"
            else if [WorkItemType] = "User Story" then "📘"
            else if [WorkItemType] = "Bug" then "🐞"
            else if [WorkItemType] = "Action Plan" then "📑"
            else if [WorkItemType] = "Issue" then "🚩"
            else if [WorkItemType] = "Task" then "📋"
            else if [WorkItemType] = "Test Case" then "📃"
            else if [WorkItemType] = "Test Plan" then "🗃"
            else if [WorkItemType] = "Test Suite" then "📂"
            else "🚧"
     ),

    addTitlePlus = Table.AddColumn(
        icon,
        "Title+",
        each [Icon] & " " & [Title]
    ),

    del_icon = Table.RemoveColumns(addTitlePlus,{"Icon"})

in del_icon

Les étapes de cette requête ressemblent à ceci dans l’éditeur :

Il est important de se rappeler que Power Query garde en mémoire les aperçus de chacune des étapes intermédiaires et qu’il doit recalculer celles-ci chaque fois qu’il y a un changement ou que nous actualisons les données. Plusieurs de ces étapes n’ont pas de valeur ajoutée à être conservées en mémoire. Nous pouvons donc optimiser à la fois le temps d’exécution et d’espace mémoire utilisée.

Fusion des étapes imbriquées

L’une des possibilités est d’imbriquer les étapes l’une dans l’autre. Nous pourrions donc penser à modifier les quatre expansions de colonnes de la manière suivante :

expand = Table.ExpandRecordColumn(
        Table.ExpandRecordColumn(
            Table.ExpandRecordColumn(
                Table.ExpandRecordColumn(
                    Source
                    , "Project"
                    , {"ProjectName", "ProjectId"}
                )
                , "Area"
                , {"AreaName", "AreaPath"}
            )
            , "Iteration"
            , {"IterationName", "IterationPath"}
        )
        , "AssignedTo"
        , {"UserName"}
    ),

Dans l’éditeur et pour Power Query, cela est considéré comme une seule étape nommée « expand » plutôt que quatre étapes comme précédemment.

C’est la méthode qui est la plus optimale en termes de performance. La contrepartie est qu’en plus de perdre l’engrenage pour l’édition dans l’interface, il devient plus difficile de faire la maintenance causée par la lisibilité déficiente (ici, j’ai tout de même pris la peine d’indenter le code avec des retours de ligne. Cependant, lorsque celui-ci est sur une seule ligne, il devient pratiquement impossible de savoir quel paramètre va avec quelle fonction).

L’exemple ici montre un cas extrême. Le cas d’utilisation typique de l’imbrication se limite généralement à deux étapes et non quatre comme ici.

Les sous-étapes – l’opérateur let…in

Une autre méthode pour diminuer les étapes intermédiaires dans l’éditeur sans perdre la lisibilité est d’utiliser let…in.

    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,

Cette technique permet de réduire le nombre d’étapes intermédiaires en les considérant comme une seule. Un seul aperçu est également calculé. Au niveau des performances, cette technique est comparable à l’imbrication. Il y aura tout de même une différence par rapport à l’imbrication causée par la création des variables locales, mais l’impact sur la mémoire est négligeable.

En plus du gain similaire à l’imbrication, nous obtenons un bonus de lisibilité et de maintenabilité. Il est effectivement très simple de déboguer ces étapes puisqu’en défaisant le let…in, les étapes réapparaitront dans l’éditeur. Notez cependant que l’étape (et donc la référence pour les étapes suivantes) devient le nom de la variable précédant le let et non le nom de la variable visible après le in. Afin d’éviter un oublie lors du débogage, vous pourriez toujours appeler votre dernière étape du même nom que votre variable, mais ceci est optionnel. (N’oubliez pas non plus de réajuster la virgule lorsqu’il ne s’agit pas de la dernière étape.)

Éliminer les étapes redondantes ou inutiles

Un titre comme celui-ci semble évident, mais avez-vous remarqué qu’une étape de tri avait été ajoutée alors que la requête OData fait déjà ce tri ? Il en va de même pour les modifications de type. Dans l’exemple de base, l’étape n’avait pas été ajoutée, mais pour être conforme aux bonnes pratiques, le type de la nouvelle colonne URL aurait dû être text plutôt que any. Il est aussi possible que vous ayez utilisé ReplaceValue plutôt que ReplaceText en modifiant vos valeurs. Typiquement, nous ajouterions l’étape suivante pour ajuster les types erronés :

changeType = Table.TransformColumnTypes(formatTitle,{{"Title", type text}, {"URL", type text}})

Remarquez que si vous avez fait tous vos changements de type au même moment, l’éditeur fera déjà une liste de liste. Par contre, il arrive qu’il fasse ces étapes à des moments différents

changeType = Table.TransformColumnTypes(formatTitle,{ {"Title", type text} })
{…}
changeType2 = Table.TransformColumnTypes(otherOperation,{ {"URL", type text} })

Cette seconde version est à éviter, autant au niveau de la performance que de la mémoire. Le système est beaucoup plus performant à le faire en une seule fois. Cependant, il existe une manière encore plus simple qui évite complètement cette étape.

Pour une raison qui m’échappe, l’éditeur graphique ne permet pas d’ajouter ce paramètre, mais ajouter ce paramètre manuellement n’empêche pas d’utiliser l’éditeur graphique. Il s’agit, à la création de la colonne, de spécifier directement le type de la colonne. Donc voici l’étape telle qu’écrite par l’éditeur.

url = Table.AddColumn(
    expand,
    "URL",
    each baseURL&"/"&[ProjectName]&"/_workitems/edit/"&Text.From([WorkItemId])
),

Et voici la version modifiée pour indiquer le type à la création.

url = Table.AddColumn(
    expand,
    "URL",
    each baseURL&"/"&[ProjectName]&"/_workitems/edit/"&Text.From([WorkItemId]),
    type text
),

Il est également possible de le faire pour les données créées manuellement. (Il existe plusieurs méthodes, mais en voici celle qui utilise Record.)

Table.FromRecords(
    {
        [ ID = 7665, StartDate = #date(2013,05,12), EndDate = null, GivenName = "John", FamilyName = "Smith" , isAnnual = true, efficiency = 2.54 ],
        [ ID = 7788, StartDate = #date(2015,02,28), EndDate = null, GivenName = "Jane", FamilyName = "Doe", isAnnual = true, efficiency = 1.54 ],
        [ ID = 7890, StartDate = #date(2020,03,27), EndDate = #date(2021,06,30), GivenName = "Juliette", FamilyName = "Capulet", isAnnual = false, efficiency = 0.68 ],
        [ ID = 8756, StartDate = #date(2020,11,17), EndDate = #date(2021,06,30), GivenName = "Romeo", FamilyName = "Montaigu", isAnnual = false, efficiency = 0.35 ],
        [ ID = 8857, StartDate = #date(2021,06,30), EndDate = null, GivenName = "Barbara", FamilyName = "Delacruz", isAnnual = false, efficiency = 3.4 ],
        [ ID = 9563, StartDate = #date(2023,12,01), EndDate = null, GivenName = "George", FamilyName = "Washington", isAnnual = true, efficiency = 1.48 ]
    }
    , let 
        _t = ((type nullable text) meta [Serialized.Text = true]), 
        _d = ((type nullable datetime) ),
        _i = ((type nullable Int64.Type) ),
        _n = ((type nullable number) ),
        _b = ((type logical) )
    in type table [ID = _i, StartDate = _d, EndDate = _d, GivenName = _t, FamilyName = _t, isAnnual = _b, efficiency = _n]
)

En ajoutant le type à la création, il est possible d’éliminer complètement l’étape de conversion, ce qui est un double gain de performance. Prenez la peine de lire la documentation officielle des fonctions que vous utilisez pour en savoir plus sur les différents paramètres que l’éditeur visuel ignore ou utilise de manière erronée.

Toujours afin d’éliminer les étapes inutiles, plutôt que d’ajouter une colonne pour l’icône puis calculer le titre pour ensuite détruire l’icône, il est possible de créer directement le changement sans étapes supplémentaires.

formatTitle = Table.ReplaceValue(
        url,
        each [Title],
        each (
            if [WorkItemType] = "Epic" then "👑"
            else if [WorkItemType] = "Feature" then "🏆"
            else if [WorkItemType] = "User Story" then "📘"
            else if [WorkItemType] = "Bug" then "🐞"
            else if [WorkItemType] = "Action Plan" then "📑"
            else if [WorkItemType] = "Issue" then "🚩"
            else if [WorkItemType] = "Task" then "📋"
            else if [WorkItemType] = "Test Case" then "📃"
            else if [WorkItemType] = "Test Plan" then "🗃"
            else if [WorkItemType] = "Test Suite" then "📂"
            else "🚧"
        ) & [Title],
        Replacer.ReplaceText,{"Title"})

En concaténant directement notre structure if…elseif…else et le titre, nous évitons ainsi deux étapes qui était totalement inutiles, à savoir la création et la suppression d’une colonne pour un état intermédiaire. Dans un prochain article, je présenterai comment améliorer davantage cette structure sans utiliser de structure conditionnelle, mais il s’agit tout de même d’une première amélioration non négligeable. Tenter toujours de voir si vos étapes intermédiaires sont réellement nécessaires ou s’il est possible de faire plus avec moins comme c’est le cas ici.

Conclusion – la 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 en commun, nous obtenons la solution présentée ci-dessous. Notez que le formatage indenté est optionnel, mais aide à la lisibilité et n’a aucun impact sur l’éditeur ou les performances. Je vous suggère donc, quand vous utilisez l’éditeur avancé, d’utiliser une technique similaire pour aider à la lisibilité. En bonus, j’ajoute une variable locale qui permet l’édition de la requête plus facilement dans l’éditeur. Je parlerai plus en détail de ces variables dans un autre article.

Requête

let 
    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",

    Source = OData.Feed("https://analytics.dev.azure.com/KuriosIT/_odata/v4.0-preview/WorkItems?" & query, 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 baseURL&"/"&[ProjectName]&"/_workitems/edit/"&Text.From([WorkItemId]),
        type text
    ),
    formatTitle = Table.ReplaceValue(
        url,
        each [Title],
        each (
            if [WorkItemType] = "Epic" then "👑"
            else if [WorkItemType] = "Feature" then "🏆"
            else if [WorkItemType] = "User Story" then "📘"
            else if [WorkItemType] = "Bug" then "🐞"
            else if [WorkItemType] = "Action Plan" then "📑"
            else if [WorkItemType] = "Issue" then "🚩"
            else if [WorkItemType] = "Task" then "📋"
            else if [WorkItemType] = "Test Case" then "📃"
            else if [WorkItemType] = "Test Plan" then "🗃"
            else if [WorkItemType] = "Test Suite" then "📂"
            else "🚧"
        ) & [Title],
        Replacer.ReplaceText,{"Title"})
in
    formatTitle

Vous trouverez le fichier Power BI qui inclut cet exemple Power Query en suivant ce lien. Cet exemple présente également quelques cas en DAX et une autre solution incluant des fonctions qui seront discutés dans les prochains articles.

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.