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

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.

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:

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

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:

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.

Y si cambiaramos el parámetro a 3000

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

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.