Paramètres de requête M dynamiques dans Power BI Desktop

Article
11/10/2023

Cet article explique comment créer et utiliser des paramètres de requête M dynamiques dans Power BI Desktop. Avec les paramètres de requête M dynamiques, les auteurs de modèles peuvent configurer les valeurs de filtre ou de segment que les visionneuses de rapports peuvent utiliser pour un paramètre de requête M. Avec des paramètres de requête M dynamiques, les créateurs de modèles disposent d’un contrôle supplémentaire sur la manière dont les sélections de filtres sont incorporées dans les requêtes sources DirectQuery.

Les auteurs de modèles comprennent la sémantique voulue de leurs filtres et savent souvent comment écrire des requêtes efficaces sur leur source de données. Avec des paramètres de requête M dynamiques, les auteurs de modèles peuvent s’assurer que les sélections de filtres s’intègrent dans les requêtes sources au point approprié pour obtenir les résultats voulus avec des performances optimales. Les paramètres de requête M dynamiques peuvent être particulièrement utiles pour l’optimisation des performances des requêtes.

Regardez Sujata expliquer et utiliser les paramètres de requête M dynamiques dans la vidéo suivante, puis essayez-les vous-même.

Notes

Cette vidéo peut utiliser des versions antérieures de Power BI Desktop ou le service Power BI.

Prérequis

Pour suivre ces procédures, vous devez disposer d’une requête M valide qui utilise une ou plusieurs tables DirectQuery.

Créer et utiliser des paramètres dynamiques

L’exemple suivant transmet une valeur unique à un paramètre de façon dynamique.

Ajouter des paramètres

Dans Power BI Desktop, sélectionnez Accueil>Transformer les données>Transformer les données pour ouvrir l’éditeur Power Query.
Dans l’Éditeur Power Query, dans le ruban, sous Gérer les paramètres, sélectionnez Nouveaux paramètres.
Dans la fenêtre Gérer les paramètres, entrez les informations sur le paramètre. Pour plus d’informations, consultez Créer un paramètre.
Sélectionnez Nouveau pour ajouter d’autres paramètres.
Lorsque vous avez fini d’ajouter des paramètres, sélectionnez OK.

Référencer les paramètres de la requête M

Une fois que vous avez créé les paramètres, vous pouvez les référencer dans la requête M. Pour modifier la requête M, lorsque celle-ci est sélectionnée, ouvrez l’Éditeur avancé.
Ensuite, référencez les paramètres dans la requête M, mis en évidence en jaune dans l’image suivante :
Lorsque vous avez fini de modifier la requête, sélectionnez Terminé.

Créer des tables de valeurs

Créez une table pour chaque paramètre, avec une colonne qui fournit les valeurs possibles disponibles pour être définies de façon dynamique en fonction de la sélection de filtre. Dans cet exemple, vous souhaitez que les paramètres StartTime et EndTime soient dynamiques. Étant donné que ces paramètres nécessitent un Date/Time paramètre, vous générez les entrées possibles afin de définir la date pour le paramètre de façon dynamique.

Dans le ruban Power BI Desktop, sous Modélisation, sélectionnez Nouvelle table.
Créez une table pour les valeurs du paramètre StartTime, par exemple :

StartDateTable = CALENDAR (DATE(2016,1,1), DATE(2016,12,31))
Créez une deuxième table pour les valeurs du paramètre EndTime, par exemple :

EndDateTable = CALENDAR (DATE(2016,1,1), DATE(2016,12,31))

Remarque

Utilisez un nom de colonne ne figurant pas dans une table réelle. Si vous utilisez le même nom qu’une colonne de table réelle, la valeur sélectionnée s’applique en tant que filtre dans la requête.

Lier les champs aux paramètres

Maintenant que vous avez créé les tables avec les champs Date, nous pouvons lier chaque champ à un paramètre. La liaison d’un champ à un paramètre signifie que, à mesure que la valeur du champ sélectionné change, elle passe au paramètre et met à jour la requête qui fait référence à celui-ci.

Pour lier un champ, dans l’affichage Modèle de Power BI Desktop, sélectionnez le champ nouvellement créé, puis, dans le volet Propriétés, sélectionnez Avancé.

Notes

Le type de données de colonne doit correspondre au type de données de paramètre M.
Sélectionnez la liste déroulante sous Lier au paramètre, puis sélectionnez le paramètre que vous souhaitez lier au champ :

Dans la mesure où cet exemple a trait à la définition du paramètre sur une valeur unique, laissez l’option Sélection multiple définie sur Non, qui est sa définition par défaut :

Si vous définissez la colonne mappée sur Non pour l’option Sélection multiple, vous devez utiliser un mode de sélection unique dans le segment ou exiger une sélection unique dans la carte de filtre.

Si vos cas d’usage nécessitent le passage de plusieurs valeurs à un seul paramètre, définissez le contrôle sur Oui et assurez-vous que votre requête M est configurée pour accepter plusieurs valeurs. Voici un exemple pour RepoNameParameter, qui autorise plusieurs valeurs :
Répétez ces étapes si vous avez d’autres champs à lier à d’autres paramètres :

Vous pouvez désormais référencer ce champ dans un segment ou en tant que filtre :

Screenshot that shows referencing the fields.

Activer Sélectionner tout

Dans cet exemple, le modèle Power BI Desktop comprend un champ nommé Country, qui est une liste de pays/régions liés à un paramètre M nommé countryNameMParameter. Ce paramètre est activé pour la Sélection multiple, mais pas pour Sélectionner tout. Pour pouvoir utiliser l’option Sélectionner tout dans un segment ou une carte de filtre, procédez comme suit :

Screenshot that shows an example of a multiselect M parameter.

Pour activer Sélectionner tout pour Country :

Dans les propriétés Avancées pour Country, activez le bouton bascule Sélectionner tout, qui active l’entrée Sélectionner toutes les valeurs. Modifiez la valeur de Sélectionner tout ou notez la valeur par défaut.

La valeur Sélectionner tout passe au paramètre en tant que liste contenant la valeur que vous avez définie. C’est pourquoi, quand vous définissez cette valeur ou utilisez la valeur par défaut, vous devez vérifier que cette valeur est unique et n’existe pas dans le champ lié au paramètre.
Lancez l’Éditeur Power Query, sélectionnez la requête, puis sélectionnez Éditeur avancé. Modifiez la requête M afin d’utiliser la valeur Sélectionner tout pour faire référence à l’option Sélectionner tout.
Dans l’Éditeur avancé, ajoutez une expression booléenne qui prend la valeur true si le paramètre est activé pour Sélection multiple et contient la valeur Sélectionner tout, et qui autrement retourne false :
Incorporez le résultat de l’expression booléenne Sélectionner tout dans la requête source. L’exemple comprend dans la requête source un paramètre de requête booléen nommé includeAllCountries qui est défini sur le résultat de l’expression booléenne de l’étape précédente. Vous pouvez utiliser ce paramètre dans une clause de filtre dans la requête, de sorte que la valeur false pour le booléen filtre les noms de pays ou de région sélectionnés, et que la valeur true n’applique en fait aucun filtre.
Une fois que vous avez mis à jour votre requête M pour prendre en compte la nouvelle valeur Sélectionner tout, vous pouvez utiliser la fonction Sélectionner tout dans des sélecteurs ou des filtres.

À titre de référence, voici la requête complète pour l’exemple précédent :

let
    selectedcountryNames = if Type.Is(Value.Type(countryNameMParameter), List.Type) then 
      Text.Combine({"'", Text.Combine(countryNameMParameter, "','") , "'"})
    else
      Text.Combine({"'" , countryNameMParameter , "'"}),

    selectAllCountries = if Type.Is(Value.Type(countryNameMParameter), List.Type) then 
      List.Contains(countryNameMParameter, "__SelectAll__")
    else
      false,

    KustoParametersDeclareQuery = Text.Combine({"declare query_parameters(", 
                                 "startTimep:datetime = datetime(", DateTime.ToText(StartTimeMParameter, "yyyy-MM-dd hh:mm"), "), " , 
                                 "endTimep:datetime = datetime(", DateTime.ToText(EndTimeMParameter, "yyyy-MM-dd hh:mm:ss"), "), ",   
                                 "includeAllCountries: bool = ", Logical.ToText(selectAllCountries) ,",",
                                 "countryNames: dynamic = dynamic([", selectedcountryNames, "]));" }),

   ActualQueryWithKustoParameters = 
                                "Covid19
                                | where includeAllCountries or Country in(countryNames)
                                | where Timestamp > startTimep and Timestamp < endTimep
                                | summarize sum(Confirmed) by Country, bin(Timestamp, 30d)",

    finalQuery = Text.Combine({KustoParametersDeclareQuery, ActualQueryWithKustoParameters}),

    Source = AzureDataExplorer.Contents("help", "samples", finalQuery, [MaxRows=null, MaxSize=null, NoTruncate=null, AdditionalSetStatements=null]),
    #"Renamed Columns" = Table.RenameColumns(Source,{{"Timestamp", "Date"}, {"sum_Confirmed", "Confirmed Cases"}})
in
    #"Renamed Columns"

Risque de sécurité potentiel

Les lecteurs de rapports qui peuvent définir de façon dynamique les valeurs des paramètres de requête M peuvent accéder à davantage de données ou déclencher des modifications du système source à l’aide d’attaques d’injection. Cette possibilité dépend de la façon dont vous référencez les paramètres dans la requête M et des valeurs que vous leur passez.

Par exemple, vous avez une requête Kusto paramétrée construite comme suit :

Products
| where Category == [Parameter inserted here] & HasReleased == 'True'
 | project ReleaseDate, Name, Category, Region

Rien ne s’oppose à ce qu’un utilisateur bienveillant transmette une valeur appropriée pour le paramètre, par exemple, Games :

| where Category == 'Games' & HasReleased == 'True'

Toutefois, une personne malveillante peut être en mesure de transmettre une valeur qui modifie la requête pour obtenir l’accès à plus de données, par exemple, 'Games'// :

Products
| where Category == 'Games'// & HasReleased == 'True'
| project ReleaseDate, Name, Category, Region

Dans cet exemple, un attaquant peut accéder à des informations sur des jeux qui n’ont pas encore été publiés en changeant une partie de la requête en commentaire.

Atténuer les risques

Pour atténuer le risque de sécurité, évitez la concaténation de chaîne de valeurs de paramètre M dans la requête. Au lieu de cela, consommez ces valeurs de paramètre dans des opérations M qui se replient sur la requête source, afin que le moteur et le connecteur M construisent la requête finale.

Si une source de données prend en charge l’importation de procédures stockées, envisagez d’y stocker votre logique de requête et de l’appeler dans la requête M. Autrement, si vous en disposez, utilisez un mécanisme de passage de paramètre intégré au langage de requête source et aux connecteurs. Par exemple, Azure Data Explorer dispose de fonctionnalités de paramètres de requête intégrées qui sont conçues pour protéger contre les attaques par injection.

Voici quelques exemples de ces mesures d’atténuation :

Exemple utilisant les opérations de filtrage de la requête M :
```
Table.SelectRows(Source, (r) => r[Columns] = Parameter)
```
Exemple déclarant le paramètre dans la requête source, ou passant la valeur de paramètre en tant qu’entrée à une fonction de requête source :
```
declare query_parameters (Name of Parameter : Type of Parameter);
```

Exemple d’appel direct d’une procédure stockée :

let CustomerByProductFn = AzureDataExplorer.Contents("Help", "ContosoSales"){[Name="CustomerByProduct"]}[Data] in
CustomerByProductFn({1, 3, 5})

Observations et limitations

Certaines considérations et limitations sont à prendre en compte lors de l’utilisation de paramètres de requête M dynamiques :

Un seul paramètre ne peut pas être lié à plusieurs champs et inversement.
Les paramètres de requête M dynamiques ne prennent pas en charge les agrégations.
Les paramètres de requête M dynamiques ne prennent pas en charge la sécurité au niveau des lignes (SNL).
Les noms de paramètres ne peuvent pas être des mots réservés DAX (Data Analysis Expressions) ou contenir des espaces. L’ajout de Parameter à la fin du nom du paramètre peut vous aider à éviter cette limitation.
Les noms de table ne peuvent pas contenir d’espaces ou de caractères spéciaux.
Si votre paramètre est de type Date/Time, vous devez le caster dans la requête M en tant que DateTime.Date(<YourDateParameter>).
Si vous utilisez des sources SQL, il se peut qu’une boîte de dialogue de confirmation s’affiche chaque fois que la valeur du paramètre change. Cela est dû à un paramètre de sécurité : Exiger l’approbation de l’utilisateur pour les nouvelles requêtes de base de données natives. Vous pouvez trouver et désactiver ce paramètre dans la section Sécurité des Options de Power BI Desktop.
Il est possible que des paramètres de requête M dynamiques ne fonctionnent pas lors de l’accès à un modèle sémantique dans Excel.
Les paramètres de requête M dynamiques ne sont pas pris en charge sur Power BI Report Server.

Types de paramètres prédéfinis non pris en charge

Quelconque
Duration
Vrai/Faux
Binary

Filtres non pris en charge

Segment ou filtre d’heure relative
Date relative
Segment de hiérarchie
Filtre d’inclusion à plusieurs champs
Filtres d’exclusion / Pas de filtre
Mise en surbrillance croisée
Filtre d’exploration hiérarchique
Filtre d’extraction croisée
Filtre N principaux

Opérations non prises en charge

And
Contient
Inférieur à
Supérieur à
Starts With (Commence par)
Ne commence pas par
N'est pas
Ne contient pas
Est vierge
N’est pas vide

Pour plus d’informations sur les fonctionnalités de Power BI Desktop, consultez les ressources suivantes :