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

En este artículo se describe cómo crear parámetros de consulta M dinámicos y trabajar con ellos en Power BI Desktop. Con los parámetros de consulta M dinámicos, los autores de modelos pueden configurar los valores de filtro o segmentación que los visores de informes pueden usar con los parámetros de consulta M. Los parámetros de consulta M dinámicos proporcionan a los autores de modelos más control sobre las selecciones de filtro que se van a incorporar a las consultas de origen de DirectQuery.

Los autores de modelos comprenden la semántica de sus filtros y, a menudo, saben cómo escribir consultas eficaces en su origen de datos. Con los parámetros de consulta M dinámicos, los autores de modelos pueden garantizar que las selecciones de filtro se incorporen a las consultas de origen en el punto adecuado para lograr los resultados previstos con un rendimiento óptimo. Estos parámetros pueden ser especialmente útiles para la optimización del rendimiento de las consultas.

Mire cómo Sujata explica y utiliza los parámetros de consulta M dinámicos en el siguiente vídeo, y luego pruébelos usted mismo.

Nota

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

Requisitos previos

Para trabajar con estos procedimientos, debe tener una consulta M válida que use una o varias tablas de DirectQuery.

Creación y uso de parámetros dinámicos

En el ejemplo siguiente se pasa un valor único a un parámetro dinámicamente.

Incorporación de parámetros

1. En Power BI Desktop, seleccione Inicio>Transformar datos>Transformar datos para abrir el Editor de Power Query.

2. En el Editor de Power Query, seleccione Nuevos parámetros en Administrar parámetros en la cinta de opciones.

   

3. En la ventana Administrar parámetros, rellene la información sobre el parámetro. Para más información, consulte Creación de un parámetro.

   

4. Seleccione Nuevo para agregar más parámetros.

   

5. Cuando haya terminado de agregar parámetros, seleccione Aceptar.

Referencia a los parámetros de la consulta M

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

   

2. Haga referencia a los parámetros de la consulta M, como se resalta en amarillo en la imagen siguiente:

   

3. Cuando haya terminado de editar la consulta, seleccione Listo.

Creación de tablas de valores

Cree una tabla para cada parámetro con una columna que proporcione los valores posibles disponibles para establecerse dinámicamente en función de la selección de filtros. En este ejemplo, quiere que los parámetros StartTime y EndTime sean dinámicos. Dado que estos parámetros requieren un parámetro Date/Time, se generan las posibles entradas para establecer dinámicamente la fecha del parámetro.

1. En la cinta de opciones de Power BI Desktop, en Modelado, seleccione Nueva tabla.

   

2. Cree una tabla para los valores del parámetro StartTime, por ejemplo:

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

   

3. Cree una segunda tabla para los valores del parámetro EndTime, por ejemplo:

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

   

   Nota

   Use un nombre de columna que no esté en una tabla real. Si usa el mismo nombre que una columna de tabla real, el valor seleccionado se aplica como filtro en la consulta.

Enlace de los campos a los parámetros

Ahora que se han creado las tablas con los campos Date, podemos enlazar cada campo a un parámetro. Enlazar un campo a un parámetro significa que, a medida que cambia el valor del campo seleccionado, el valor pasa al parámetro y actualiza la consulta que hace referencia a este.

1. Para enlazar un campo, en la vista Modelo de Power BI Desktop, seleccione el campo recién creado y, en el panel Propiedades, seleccione Avanzado.

   Nota

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

   

2. Seleccione la lista desplegable en Enlazar a parámetro y seleccione el parámetro que quiere enlazar al campo:

   

   Dado que este ejemplo es para establecer el parámetro en un valor único, mantenga Selección múltiple establecido en No, que es el valor predeterminado:

   

   Si la columna asignada se establece en No en 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.

   Si los casos de uso requieren pasar varios valores a un único parámetro, establezca el control en Sí y asegúrese de que la consulta M esté configurada para aceptar valores múltiples. He aquí un ejemplo de RepoNameParameter, que permite valores múltiples:

   

3. Si tiene otros campos para enlazar a otros parámetros, puede repetir estos pasos.

   

Ahora puede hacer referencia a este campo en una segmentación o como filtro:

Habilitación de Seleccionar todo

En este ejemplo, el modelo de Power BI Desktop tiene un campo denominado Country, que es una lista de países o regiones enlazados a un parámetro M denominado countryNameMParameter. Este parámetro está habilitado para Selección múltiple, pero no para la opción Seleccionar todo. Para poder usar la opción Seleccionar todo en una segmentación de datos o una tarjeta de filtro, siga estos pasos agregados:

Para habilitar Seleccionar todo en Country:

1. En las propiedades Avanzadas de Country, habilite el botón de alternancia Seleccionar todo, que habilita la entrada Valor Seleccionar todo. Edite Valor Seleccionar todo o anote el valor predeterminado.

   

   La opción Valor Seleccionar todo pasa al parámetro como una lista que contiene el valor definido. Por lo tanto, cuando defina este valor o use el predeterminado, tiene que asegurarse de que sea único y no exista en el campo enlazado al parámetro.

2. Inicie el Editor de Power Query, seleccione la consulta y, luego, elija Editor avanzado. Edite la consulta M para usar Valor Seleccionar todo para hacer referencia a la opción Seleccionar todo.

   

3. En el Editor avanzado, agregue una expresión booleana que se evalúe como true si el parámetro está habilitado para Selección múltiple y contiene la entrada Valor Seleccionar todo (de lo contrario, se devuelve false).

   

4. Incorpore el resultado de la expresión booleana 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. Puede usar este parámetro en una cláusula de filtro de la consulta, de modo que un valor de false para el valor booleano filtre hasta los nombres de país o región seleccionados y un valor de true no aplique ningún filtro.

   

5. Cuando haya actualizado la consulta M para tener en cuenta la nueva entrada Valor Seleccionar todo, ya puede usar la función Seleccionar todo en segmentaciones o filtros.

   

Como referencia, esta es la consulta completa del ejemplo anterior:

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"

Riesgo potencial de seguridad

Los lectores de informes que pueden establecer dinámicamente los valores de los parámetros de consulta M podrían tener acceso a más datos o desencadenar modificaciones en el sistema de origen mediante ataques por inyección. Esta posibilidad depende de cómo haga referencia a los parámetros de la consulta M y de los valores que pase a los parámetros.

Por ejemplo, tiene una consulta Kusto parametrizada construida de la manera siguiente:

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

No hay problemas con un usuario sin malas intenciones que pase un valor apropiado 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 juegos que todavía no se han publicado cambiando parte de la consulta por un comentario.

Mitigación del riesgo

Para mitigar el riesgo de seguridad, evite la concatenación de cadenas de valores de parámetros 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.

Si un origen de datos admite la importación de procedimientos almacenados, considere la posibilidad de almacenar la lógica de consulta allí e invocarla en la consulta M. O bien, si está disponible, use 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 de estas mitigaciones:

• Ejemplo que usa las operaciones de filtrado de la 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);
  

• Ejemplo de llamada directa a un procedimiento almacenado:

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

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.
• Los parámetros de consulta M dinámicos no admiten agregaciones.
• Los parámetros de consulta M dinámicos no admiten la seguridad de nivel de fila (RLS).
• Los nombres de parámetro no pueden ser palabras reservadas de expresiones de análisis de datos (DAX) ni contener espacios. La anexión de Parameter al final del nombre del parámetro ayuda a evitar esta limitación.
• Los nombres de tabla no pueden contener espacios ni caracteres especiales.
• Si el parámetro tiene el tipo de datos Date/Time, deberá convertirlo dentro de la consulta M como DateTime.Date(<YourDateParameter>).
• Si usa orígenes SQL, puede que reciba un cuadro de diálogo de confirmación cada vez que cambie el valor del parámetro. Este cuadro de diálogo se debe a la configuración de seguridad Requerir la aprobación del usuario de las nuevas consultas de bases de datos nativas. Puede encontrar y desactivar esta configuración en la sección Seguridad del cuadro de diálogo Opciones de Power BI Desktop.
• Es posible que los parámetros de consulta M dinámicos no funcionen al acceder a un modelo semántico en Excel.
• Los parámetros de consulta M dinámicos no se admiten en Power BI Report Server.
• No se admite el cambio de orígenes de datos mediante parámetros de consulta M dinámicos en el servicio Power BI. Consulte Actualización y orígenes de datos dinámicos para obtener información adicional.

Los tipos de parámetros predefinidos no admitidos 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/sin 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

Contenido relacionado

Para más información sobre las funcionalidades de Power BI Desktop, consulte los recursos siguientes:

• DirectQuery en Power BI
• ¿Qué es Power BI Desktop?
• Información general sobre consultas en Power BI Desktop
• Tipos de datos en Power BI Desktop
• Tutorial: Dar forma a los datos y combinarlos en Power BI Desktop
• Realización de tareas de consultas comunes en Power BI Desktop