Power Query: Cómo pasar un Parámetro SQL de forma dinámica a un Query SQL Server

pquery_parametro_sql

El día de hoy vamos a explorar una capacidad para decidir cuánta información vamos a cargar desde un Query SQL Server (TSQL) utilizando un parámetro dinámico de Power Query. Esto nos sirve para poder limitar el conjunto de datos que nos es devuelto, puesto que algunas veces no necesitamos todos los datos.

pquery_parametro_sql

NOTA: Antes de explicar cómo podemos lograr esto hay que aclarar que esto es un poco avanzado, puesto que se ha tenido que añadir algo de código en el Editor Avanzado en Power Query.

Para nuestro ejemplo vamos a crear un Parámetro, el cual va a definir cuántos días va a regresar (que serán 31 días). Dentro del Query TSQL usaremos el parámetro para que solo nos regrese esos 31 días de datos.

CREANDO EL PARÁMETRO

Nuestro primer paso será crear el parámetro requerido. Como podemos ver, he creado el parámetro con los siguientes detalles:

parámetro Power Query 1

Lo más importante a resaltar aquí es que he establecido el tipo como Texto, esto es para asegurarnos de que se pasará correctamente.

parámetro Power Query 2

QUERY TSQL A USAR

A continuación tenemos el Query TSQL inicial que usé primero antes de modificarlo en el Editor Avanzado.

Así es cómo el Query TSQL se ve tras ser escrito y probado en SQL Server Management Studio:

TSQL

Una de las cosas a notar en la imágen de arriba es que se ha puesto a propósito SELECT Date, Calendar Year en la misma línea. Esto se debe a que a la hora de poner nuevas líneas o pestañas en SSMS esto es representado con carácteres adicionales en el Editor Avanzado en Power Query tal y como se muestra aquí:

Por lo tanto, esto añade complejidad que podemos evitar si ponemos nuestra variable al inicio del Query TSQL y en una línea.

MODIFICANDO EL QUERY TSQL EN EL EDITOR DE QUERIES

Ahora vamos a ver cómo es que se ha modificado el Query TSQL en el Editor de Queries.

Primero iremos al Editor de Query y le daremos click en la tabla llamada «Query 1», después daremos click en «Editor Avanzado» y nos encontraremos con esto:

Lo que se hará es crear una nueva entrada en nuestro código M poniendo lo siguiente:

DaysGoingBack = #”Days Back”,

Esto hará que el código ahora se vea así:

Lo que esto hace es tomar el parámetro inicial que cree antes y lo pone en un valor en esta tabla. Después voy a utilizar el parámetro DaysGoingBack de arriba y modificaré mi Query TSQL para usar este valor.

Así es como el código M se veía antes de hacer el cambio:

Source = Sql.Database(“localhost”, “WideWorldImportersDW”, 
[Query=
”Declare @DaysBack as Int Set @DaysBack = 31...

Y lo que he hecho es que cuando queramos pasar este valor dentro de nuestro Query TSQL en el Editor Avanzado (M) tendremos que ponerlo de la siguiente forma, lo cual estará resaltado en verde como veremos a continuación:

let
   DaysGone = #"Days Back",
   Source = Sql.Database("localhost", "WideWorldImportersDW",
   [Query=
      "Declare @DaysBack as Int Set @DaysBack = ' " & DaysGone & " 'SELECT Date, [Calendar Year]
      FROM [WideWorldImportersDW].[Dimension].[Date] as DbDate with (nolock)
      right join [WideWorldImportersDW].[Fact].[Transaction] as Tr with (nolock)
      on DbDate.[Date] = Tr.[Date Key]
      WHERE Date>=Getdate()-@DaysBack"
   ])
in
   Source

Para poder añadir un valor a un Query TSQL se debe usar la siguiente síntaxis:

‘ ” & ValueName & ”

Para terminar le damos click en Done en el Editor Avanzado.

PROBANDO EL PARÁMETRO

Ahora solo nos queda ver si el parámetro funciona como lo esperamos. Siendo que este parámetro actual tiene un valor de 31 y la fecha que vamos a usar es 30/11/2020, debo cambiar mi parametro a 2500 para ver algo cercano a 28/01/2014.

parámetro Power Query 2

Y si cambiaramos el parámetro a 3000

parámetro Power Query 3

Deberíamos ver en el conjunto de datos debería ser 02/01/2013.

parámetro Power Query 4

Con la habilidad de crear un parámetro SQL en Power Query puedes crear cosas maravillosas, y si no se te ocurre que, nos leemos abajo.

Power Query: Cómo pasar un Parámetro SQL de forma dinámica a un Query SQL Server

6 comentarios en «Power Query: Cómo pasar un Parámetro SQL de forma dinámica a un Query SQL Server»

  1. Que tal @angeldeOdin, queria consultarte si tenes algun curso, canal de youtube, etc para ver todo lo relacionado a M con la parametrizacion.

    Tengo que meter un Store parametrizado asi con una query gigantezca al parametrizarla no consume nada.

    Ejemplo

    exec @storePepito (@fechadesde, @fechahasta)

  2. Buenas, tengo consultas que se conectan a base sql muy grandes, lo que necesito es crear un parametro en power query de manera que cuando cambie el mes sobre el cual esté trabajando, cambiando el mes del parametro no tenga que estar cambiando todas las consultas el mes a consultar en el sql que es bastante engorroso, muchas gracias!

    1. Hola Osvaldo.
      Podrías generar una pequeña tabla en Excel que sirva como parámetro de identificación del mes. Luego, dentro del query de consulta haces lo de esta publicación.

      let
      Mes = Excel.CurrentWorkbook(){[Name=»meses»]}[Content]{0}[num_mes],
      Source = Sql.Database(«localhost»,»WideWorldImportersDW»,
      [Query=
      «Declare @ActMonth as varchar(3) Set @ActMonth = ‘» & Mes & «‘Select * From WideWorldImportersDW.Dimension.Date Where WideWorldImportersDW.Dimension.Date.[Short Month] = @ActMonth»])
      in
      Source

      Con eso, cada vez que el mes se actualiza, todo cambia.
      Saludos.

      parametro en tabla

Deja una respuesta

Tu dirección de correo electrónico no será publicada.

Scroll hacia arriba