Parámetros de consulta M dinámicos en Power BI Desktop

Con los parámetros de consulta M dinámicos, los autores de modelos pueden permitir que los visores de informes usen filtros o segmentaciones para establecer los valores de un parámetro de consulta M, lo que puede ser especialmente útil para las optimizaciones del rendimiento de las consultas. Con los parámetros de consulta M dinámicos, los autores de modelos tienen más control sobre cómo se incorporan las selecciones de filtro en las consultas de origen de DirectQuery.

Cuando los autores del modelo entienden la semántica prevista de sus filtros, a menudo saben cómo escribir consultas eficaces en su origen de datos y, por tanto, pueden garantizar que las selecciones de filtro se incorporen en las consultas de origen en el punto adecuado para lograr los resultados deseados con un mejor rendimiento.

Vea Explicar y usar parámetros de consulta M dinámicos y pruébelo usted mismo.

Nota

Es posible que en este vídeo se usen versiones anteriores de Power BI Desktop o del servicio Power BI.

Creación de parámetros de consulta M dinámicos

Como requisito previo para esta característica, debe haber creado un parámetro de consulta M válido al que se haga referencia en una o varias tablas de Direct Query.

Vamos a examinar un ejemplo acerca del paso de un valor único a un parámetro dinámicamente:

1. En Power BI Desktop, seleccione Inicio>Transformar datos>Transformar datos para abrir el Editor de Power Query. Seleccione Nuevos parámetros debajo del botón Administrar parámetros de la cinta de opciones.

   

2. A continuación, rellene la siguiente información sobre el parámetro.

   

3. Vuelva a hacer clic en Nuevo si tiene más parámetros que agregar.

   

4. Una vez que haya creado los parámetros, puede hacer referencia a ellos en la consulta M. Para modificar la consulta M, abra el editor avanzado mientras tiene seleccionada la consulta que desea modificar:

   

5. A continuación, haga referencia a los parámetros de la consulta M, resaltados en amarillo en la siguiente imagen.

   

6. Ahora que ha creado los parámetros y ha hecho referencia a ellos en la consulta M, tendrá que crear una tabla con una columna que proporcione los posibles valores disponibles para ese parámetro. Esto permitirá que los parámetros se establezcan dinámicamente en función de la selección del filtro. En este ejemplo, queremos que los parámetros StartTime y EndTime sean dinámicos. Dado que estos parámetros requieren un parámetro de fecha y hora, deseo generar entradas de fecha que se pueden usar para establecer la fecha del parámetro. Para empezar, creamos una nueva tabla:

   

7. Esta es la primera tabla que creé para los valores del parámetro StartTime:

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

   

8. Esta es la segunda tabla que creé para los valores del parámetro EndTime:

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

   

   Nota

   Se recomienda usar un nombre de columna diferente que no esté en una tabla real. Si tienen el mismo nombre, el valor seleccionado se aplicará como filtro a la consulta real.

9. Ahora que se han creado las tablas con el campo Date, podemos enlazar cada campo a un parámetro. Enlazar el campo a un parámetro significa esencialmente que a medida que cambia el valor seleccionado para el campo, el valor se pasará al parámetro y se actualizará la consulta donde se hace referencia al parámetro. Para enlazar el campo, vaya a la pestaña Modelado, seleccione el campo recién creado y, a continuación, vaya a las Propiedades avanzadas:

   Nota

   El tipo de datos de la columna debe coincidir con el tipo de parámetro de M.

   

10. Seleccione la lista desplegable en Bind to parameter (Enlazar al parámetro) y seleccione el parámetro que desea enlazar al campo:

    

    Dado que este ejemplo es para un valor de selección única (estableciendo el parámetro en un valor único), querrá mantener Selección múltiple en No, que es el valor predeterminado:

    

    Si los casos de uso requieren selección múltiple (paso de varios valores a un solo parámetro), debe cambiar el modificador a Sí y asegurarse de que la consulta M está configurada correctamente para aceptar varios valores en la consulta M. Este es un ejemplo de RepoNameParameter, que permite varios valores:

    

11. Puede repetir estos pasos si tiene otros campos para enlazar a otros parámetros:

    

12. Por último, puede hacer referencia a este campo en una segmentación o como un filtro:

    

Si la columna asignada se establece en No para la selección múltiple, debe usar un modo de selección única en la segmentación o requerir una selección única en la tarjeta de filtro.

Habilitación de Seleccionar todo

Hay pasos adicionales para que los usuarios finales puedan usar la opción Seleccionar todo de la tarjeta de segmentación o filtrado. Vamos a usar el siguiente escenario como ejemplo. En la pestaña Modelo de Power BI Desktop, puede ver que hay un campo de nombre País (lista de países) enlazado a un parámetro M denominado countryNameMParameter:

También puede ver que este parámetro tiene habilitada la Selección múltiple, pero no la opción Seleccionar todo. Al habilitar el botón de alternancia Seleccionar todo, se ve una entrada habilitada de nombre Valor Seleccionar todo:

El valor Seleccionar todo se usa para referirse a la opción Seleccionar todo de la consulta M. Este valor se pasa al parámetro como una lista que contiene el valor definido de Seleccionar todo. Por lo tanto, cuando defina este valor o use el predeterminado, tiene que asegurarse de que es único y no existe en el campo enlazado al parámetro. Una vez que haya establecido el valor o usado el predeterminado para Seleccionar todo, tiene que actualizar la consulta M para tener en cuenta este valor Seleccionar todo.

Para editar la consulta M, primero debe iniciar el Editor de Power Queryy luego seleccionar Editor avanzado en la sección Consulta:

En el Editor avanzado es necesario agregar una expresión booleana que se evalúe en true si el parámetro tiene habilitada la Selección múltiple y contiene el valor Seleccionar todo (de lo contrario, devuelve false). En este ejemplo, este sería el aspecto:

A continuación, es necesario incorporar el resultado de esta expresión booleana de Seleccionar todo a la consulta de origen. En este ejemplo hay un parámetro de consulta booleano en la consulta de origen denominado includeAllCountries que se establece en el resultado de la expresión booleana del paso anterior. A continuación, se usa este parámetro en una cláusula de filtro de la consulta, de modo que false en el valor booleano filtre por los nombres de país seleccionados y true no aplique en realidad ningún filtro:

Como referencia, esta es la consulta completa que se ha usado:

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"

Una vez que haya actualizado la consulta M para tener en cuenta este nuevo valor Seleccionar todo, ya puede usar la función Seleccionar todo en segmentaciones o filtros:

Riesgo potencial de seguridad

Cuando permite que los lectores de informes establezcan dinámicamente los valores de los parámetros de consulta M, es posible que puedan tener acceso a datos adicionales o desencadenar modificaciones en el sistema de origen mediante ataques de inyección, en función de cómo se haga referencia a los parámetros en la consulta M y qué valores se pasen a ese parámetro.

Por ejemplo, supongamos que tiene una consulta parametrizada Kusto construida como la siguiente:

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

Es posible que no tenga ningún problema con un usuario sin malas intenciones que pase un valor adecuado para el parámetro, por ejemplo Games:

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

Sin embargo, es posible que un atacante pueda pasar un valor que modifique la consulta para obtener acceso a más datos, por ejemplo, 'Games' // :

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

En este ejemplo, el atacante puede obtener acceso a información sobre los juegos que todavía no se ha publicado cambiando parte de la consulta a un comentario.

Mitigación del riesgo

Para mitigar el riesgo de seguridad, es mejor evitar la concatenación de cadenas de valores de parámetros de M dentro de la consulta. En su lugar, utilice esos valores de parámetro en operaciones de M que se plieguen en la consulta de origen, de modo que el conector y el motor de M construyan la consulta final. O bien, si está disponible, haga uso de un mecanismo de paso de parámetros integrado en el lenguaje de consulta de origen y los conectores. Por ejemplo, Azure Data Explorer tiene capacidades de parámetro de consulta integradas que están diseñadas para protegerse frente a ataques por inyección.

A continuación se muestran algunos ejemplos:

• Ejemplo de uso de las operaciones de filtrado de consulta M:

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

• Ejemplo de declaración del parámetro en la consulta de origen (o paso del valor del parámetro como entrada a una función de consulta de origen):

  declare query\_parameters (Name of Parameter : Type of Parameter);
  

Consideraciones y limitaciones

Hay algunas consideraciones y limitaciones que se deben tener en cuenta al usar parámetros de consulta M dinámicos:

• Un parámetro único no se puede enlazar a varios campos ni viceversa.

• La característica no admite las agregaciones.

• La seguridad de nivel de fila (RLS) no se admite con la característica

• Los nombres de parámetro no pueden ser palabras reservadas de DAX ni contener espacios. La anexión de "Parámetro" al final del nombre del parámetro puede ayudar a evitar esta limitación.

• Si el parámetro es del tipo de datos Fecha y hora, deberá convertirlo dentro de la consulta M como DateTime.Date(<YourDateParameter>)

• Si usa orígenes de SQL, puede observar un cuadro de diálogo de confirmación cada vez que cambia el valor del parámetro. Esto se debe a una configuración de seguridad: requerir la aprobación del usuario para las nuevas consultas de base de datos nativas. Puede encontrar y desactivar esta configuración en la pestaña Seguridad del cuadro de diálogo Opciones en Power BI Desktop.

• Los tipos de parámetros no integrados son los siguientes:

  • Any
  • Duration
  • Verdadero/Falso
  • Binary

• Filtros no admitidos:

  • Filtro o segmentación de tiempo relativo
  • Fecha relativa
  • Segmentación de jerarquía
  • Filtro de inclusión de varios campos
  • Filtro de exclusión/ausencia de filtros
  • Resaltado cruzado
  • Filtro de exploración en profundidad
  • Filtro de exploración cruzada
  • N filtro principal

• Operaciones no admitidas:

  • And
  • Contiene
  • Menor que
  • Mayor que
  • Empieza por
  • No empieza con
  • No es
  • No contiene
  • Está en blanco
  • No está en blanco

Pasos siguientes

Puede hacer todo tipo de cosas con Power BI Desktop. Para obtener más información sobre sus capacidades, consulte los siguientes recursos:

• Acerca del uso de DirectQuery en Power BI
• ¿Qué es Power BI Desktop?
• Información general sobre consultas con Power BI Desktop
• Tipos de datos en Power BI Desktop
• Combinar datos y darles forma con Power BI Desktop
• Tareas de consultas comunes en Power BI Desktop