Dynamische M-queryparameters in Power BI Desktop

In dit artikel wordt beschreven hoe u dynamische M-queryparameters maakt en gebruikt in Power BI Desktop. Met dynamische M-queryparameters kunnen modelauteurs de filter- of slicerwaarden configureren die rapportviewers kunnen gebruiken voor een M-queryparameter. Dynamische M-queryparameters bieden modelauteurs meer controle over de filterselecties die moeten worden opgenomen in DirectQuery-bronquery's.

Modelauteurs begrijpen de beoogde semantiek van hun filters en weten vaak hoe ze efficiënte query's kunnen schrijven op basis van hun gegevensbron. Met dynamische M-queryparameters kunnen modelauteurs ervoor zorgen dat filterselecties op het juiste punt worden opgenomen in bronquery's om de beoogde resultaten met optimale prestaties te bereiken. Dynamische M-queryparameters kunnen met name nuttig zijn voor optimalisatie van queryprestaties.

Bekijk hoe Sujata dynamische M-queryparameters in de volgende video uitlegt en gebruikt en probeer ze vervolgens zelf uit.

Notitie

In deze video kunnen eerdere versies van Power BI Desktop of de Power BI-service worden gebruikt.

Vereisten

Als u deze procedures wilt doorlopen, moet u een geldige M-query hebben die gebruikmaakt van een of meer DirectQuery-tabellen.

Dynamische parameters maken en gebruiken

In het volgende voorbeeld wordt één waarde dynamisch doorgegeven aan een parameter.

Parameters toevoegen

1. Selecteer in Power BI Desktop De>gegevens> transformeren start om de Power Query-editor te openen.

2. Selecteer in de Power Query-editor nieuwe parameters onder Parameters beheren op het lint.

   

3. Vul in het venster Parameters beheren de informatie over de parameter in. Zie Een parameter maken voor meer informatie.

   

4. Selecteer Nieuw om meer parameters toe te voegen.

   

5. Wanneer u klaar bent met het toevoegen van parameters, selecteert u OK.

Verwijzen naar de parameters in de M-query

1. Zodra u de parameters hebt gemaakt, kunt u ernaar verwijzen in de M-query. Als u de M-query wilt wijzigen terwijl u de query hebt geselecteerd, opent u de geavanceerde editor.

   

2. Verwijs naar de parameters in de M-query, zoals geel gemarkeerd in de volgende afbeelding:

   

3. Wanneer u klaar bent met het bewerken van de query, selecteert u Gereed.

Tabellen met waarden maken

Maak een tabel voor elke parameter met een kolom die de mogelijke waarden biedt die dynamisch kunnen worden ingesteld op basis van filterselectie. In dit voorbeeld wilt u dat de StartTime parameters EndTime dynamisch zijn. Omdat voor deze parameters een Date/Time parameter is vereist, genereert u de mogelijke invoer om de datum voor de parameter dynamisch in te stellen.

1. Selecteer op het lint van Power BI Desktop onder Modellering de optie Nieuwe tabel.

   

2. Maak een tabel voor de waarden van de StartTime parameter, bijvoorbeeld:

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

   

3. Maak een tweede tabel voor de waarden van de EndTime parameter, bijvoorbeeld:

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

   

   Notitie

   Gebruik een kolomnaam die zich niet in een werkelijke tabel bevindt. Als u dezelfde naam gebruikt als een werkelijke tabelkolom, wordt de geselecteerde waarde toegepast als een filter in de query.

De velden binden aan de parameters

Nu u de tabellen met de Date velden hebt gemaakt, kunt u elk veld binden aan een parameter. Als een veld aan een parameter wordt gekoppeld, betekent dit dat wanneer de geselecteerde veldwaarde verandert, de waarde wordt doorgegeven aan de parameter en de query wordt bijgewerkt die verwijst naar de parameter.

1. Als u een veld wilt binden, selecteert u in de power BI Desktop-modelweergave het zojuist gemaakte veld en selecteert u in het deelvenster Eigenschappen Geavanceerd.

   Notitie

   Het kolomgegevenstype moet overeenkomen met het gegevenstype M-parameter.

   

2. Selecteer de vervolgkeuzelijst onder Binding met parameter en selecteer de parameter die u aan het veld wilt binden:

   

   Omdat dit voorbeeld is bedoeld voor het instellen van de parameter op één waarde, houdt u Meervoudige selectie ingesteld op Nee. Dit is de standaardwaarde:

   

   Als u de toegewezen kolom instelt op Nee voor meervoudige selectie, moet u één selectiemodus in de slicer gebruiken of één selectie in de filterkaart vereisen.

   Als in uw use cases meerdere waarden moeten worden doorgegeven aan één parameter, stelt u het besturingselement in op Ja en zorgt u ervoor dat uw M-query is ingesteld om meerdere waarden te accepteren. Hier volgt een voorbeeld waarin RepoNameParametermeerdere waarden zijn toegestaan:

   

3. Herhaal deze stappen als u andere velden hebt om te binden aan andere parameters.

   

U kunt nu verwijzen naar dit veld in een slicer of als filter:

Alles selecteren inschakelen

In dit voorbeeld heeft het Power BI Desktop-model een veld met de naam Land. Dit is een lijst met landen/regio's die zijn gebonden aan een M-parameter met de naam countryNameMParameter. Deze parameter is ingeschakeld voor meervoudige selectie, maar is niet ingeschakeld voor Alles selecteren. Als u de optie Alles selecteren in een slicer of filterkaart wilt kunnen gebruiken, voert u de volgende extra stappen uit:

Alles selecteren voor land inschakelen:

1. Schakel in de geavanceerde eigenschappen voor Land de wisselknop Alles selecteren in, waarmee de invoer alle waarden selecteren is ingeschakeld. Bewerk de waarde Alle selecteren of noteer de standaardwaarde.

   

   De waarde Alle selecteren wordt doorgegeven aan de parameter als een lijst die de waarde bevat die u hebt gedefinieerd. Wanneer u deze waarde definieert of de standaardwaarde gebruikt, moet u ervoor zorgen dat de waarde uniek is en niet bestaat in het veld dat is gebonden aan de parameter.

2. Start de Power Query-editor, selecteer de query en selecteer vervolgens Geavanceerde editor. Bewerk de M-query om de waarde Alles selecteren te gebruiken om te verwijzen naar de optie Alles selecteren.

   

3. Voeg in de Geavanceerde editor een Booleaanse expressie toe die evalueert true of de parameter is ingeschakeld voor meervoudige selectie en bevat de waarde Alles selecteren, en retourneert falseanders:

   

4. Neem het resultaat van de Boole-expressie selecteren op in de bronquery. Het voorbeeld bevat een Booleaanse queryparameter in de bronquery includeAllCountries die is ingesteld op het resultaat van de Boole-expressie uit de vorige stap. U kunt deze parameter gebruiken in een filtercomponent in de query, zodat false voor de Booleaanse filters op de geselecteerde land- of regionamen geen true filter wordt toegepast.

   

5. Zodra u uw M-query hebt bijgewerkt om rekening te houden met de nieuwe waarde Alles selecteren, kunt u de functie Alles selecteren in slicers of filters gebruiken.

   

Ter referentie: dit is de volledige query voor het voorgaande voorbeeld:

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"

Potentieel beveiligingsrisico

Rapportlezers die de waarden voor M-queryparameters dynamisch kunnen instellen, hebben mogelijk toegang tot meer gegevens of kunnen wijzigingen in het bronsysteem activeren met behulp van injectieaanvallen. Deze mogelijkheid is afhankelijk van hoe u verwijst naar de parameters in de M-query en welke waarden u doorgeeft aan de parameters.

U hebt bijvoorbeeld een geparameteriseerde Kusto-query gemaakt als volgt:

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

Er zijn geen problemen met een beschrijvende gebruiker die een geschikte waarde voor de parameter doorgeeft, bijvoorbeeld Games:

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

Een aanvaller kan echter mogelijk een waarde doorgeven waarmee de query wordt gewijzigd om toegang te krijgen tot meer gegevens, 'Games'//bijvoorbeeld:

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

In dit voorbeeld kan de aanvaller toegang krijgen tot informatie over games die nog niet zijn uitgebracht door een deel van de query te wijzigen in een opmerking.

Het risico beperken

Om het beveiligingsrisico te beperken, vermijdt u tekenreekssamenvoeging van M-parameterwaarden in de query. Gebruik in plaats daarvan die parameterwaarden in M-bewerkingen die worden gevouwen tot de bronquery, zodat de M-engine en connector de uiteindelijke query samenstellen.

Als een gegevensbron ondersteuning biedt voor het importeren van opgeslagen procedures, kunt u overwegen uw querylogica daar op te slaan en deze aan te roepen in de M-query. U kunt, indien beschikbaar, ook een mechanisme voor het doorgeven van parameters gebruiken dat is ingebouwd in de bronquerytaal en connectors. Azure Data Explorer heeft bijvoorbeeld ingebouwde mogelijkheden voor queryparameters die zijn ontworpen om te beschermen tegen injectieaanvallen.

Hier volgen enkele voorbeelden van deze oplossingen:

• Voorbeeld waarin de filterbewerkingen van de M-query worden gebruikt:

  Table.SelectRows(Source, (r) => r[Columns] = Parameter)
  

• Voorbeeld waarmee de parameter in de bronquery wordt gedeclareert of de parameterwaarde wordt doorgegeven als invoer aan een bronqueryfunctie:

  declare query_parameters (Name of Parameter : Type of Parameter);
  

• Voorbeeld van het rechtstreeks aanroepen van een opgeslagen procedure:

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

Overwegingen en beperkingen

Er zijn enkele overwegingen en beperkingen wanneer u dynamische M-queryparameters gebruikt:

• Eén parameter kan niet worden gebonden aan meerdere velden of omgekeerd.
• Dynamische M-queryparameters bieden geen ondersteuning voor aggregaties.
• Dynamische M-queryparameters bieden geen ondersteuning voor beveiliging op rijniveau (RLS).
• Parameternamen kunnen geen gereserveerde woorden (DAX) zijn voor Data Analysis Expressions of spaties. U kunt toevoegen Parameter aan het einde van de parameternaam om deze beperking te voorkomen.
• Tabelnamen mogen geen spaties of speciale tekens bevatten.
• Als uw parameter het Date/Time gegevenstype is, moet u deze casten in de M-query als DateTime.Date(<YourDateParameter>).
• Als u SQL-bronnen gebruikt, krijgt u mogelijk een bevestigingsdialoogvenster telkens wanneer de parameterwaarde wordt gewijzigd. Dit dialoogvenster wordt veroorzaakt door een beveiligingsinstelling: Gebruikersgoedkeuring vereisen voor nieuwe systeemeigen databasequery's. U kunt deze instelling vinden en uitschakelen in de sectie Beveiliging van de Opties voor Power BI Desktop.
• Dynamische M-queryparameters werken mogelijk niet bij het openen van een semantisch model in Excel.
• Dynamische M-queryparameters worden niet ondersteund in Power BI Report Server.

Niet-ondersteunde out-of-box parametertypen

• Alle
• Duur
• True/False
• Binary

Niet-ondersteunde filters

• Slicer of filter voor relatieve tijd
• Relatieve datum
• Hiërarchieslicer
• Filter voor opnemen in meerdere velden
• Filters uitsluiten/geen filters
• Kruislings markeren
• Inzoomfilter
• Kruisanalysefilter
• Top N-filter

Niet-ondersteunde bewerkingen

• And
• Bevat
• Kleiner dan
• Groter dan
• Begint met
• Begint niet met
• Is niet
• Bevat geen
• Is leeg
• Is niet leeg

Gerelateerde inhoud

Raadpleeg de volgende bronnen voor meer informatie over de mogelijkheden van Power BI Desktop:

• DirectQuery in Power BI Desktop
• Wat is Power BI Desktop?
• Queryoverzicht met Power BI Desktop
• Gegevenstypen in Power BI Desktop
• Gegevens vormgeven en combineren met Power BI Desktop
• Veelgebruikte querytaken in Power BI Desktop