Dado que Power Query registra los pasos de transformación, esto incluye muchos valores cifrados dentro del código M. Por ejemplo, si filtramos una columna para seleccionar todos los valores mayores a 50, el 50 será un valor cifrado en el código M; o si importamos un archivo .csv su ubicación será cifrada dentro del query.
¿Y qué pasaría si queremos cambiar estos valores? Pues podríamos editar el query cada vez que sea necesario, pero esto nos llevaría mucho tiempo. Lo que podemos hacer en su lugar es crear parámetros.
El término «parámetro» puede ser confuso. Cuando escribimos en VBA usamos el término «variable», así como también usamos «condiciones», «criterios» o «argumentos» cuando escribimos fórmulas en Excel. Si algo tienen en común todas estas es que son efectivamente la misma cosa: parámetros, variables, condiciones, criterios y argumentos son todos valores que podemos cambiar para obtener un resultado distinto.
En esta entrada vamos a estar usando valores de celda como parámetros. Por lo tanto, al cambiar el valor de celda podemos cambiar el resultado de un query. El parámetro puede estar dentro de un archivo .csv o como un ajuste de una base de datos, pero nos enfocaremos en la situación más probable a encontrar, y es un valor de celda.
A medida que Power Query sigue en desarrollo se introducen funciones nuevas y más fáciles de usar, siendo los parámetros una de esas funciones. El método que vamos a ver es uno de los más fáciles y también de los mejores para implementar.
Para poder abordar este tema mejor, se recomienda ir siguiéndolo mientras se trabaja con los ejemplos, los cuáles podrás descargar aquí. Este libro de trabajo contiene solo la fuente de datos, ya que la intención es ir trabajando todos los pasos de inicio a fin.
CREANDO EL QUERY
Nuestro primer paso será crear un query de forma normal. Como ya establecimos más arriba, todos los pasos serán cifrados. Seleccionaremos una celda en la fuente de datos y daremos click a la pestaña «Data» y luego a «Desde Tabla/Rango».
Esto abrirá el editor de Power Query, en donde haremos las siguientes transformaciones:
Recuerda que tambien enseño esto en vivo. Si te interesa checa nuestro curso de Power Query con fecha más proxima.
- Columnas de fecha. Daremos click en el ícono de Fecha y Hora junto al encabezado de Fecha y seleccionaremos Fecha del menú desplegable.
Seleccionaremos el encabezado de la columna de Fecha Orden y después daremos click en Transformar, Fecha, Mes, Fin de Mes.
Filtraremos la columna de Fecha para que solo incluya el 31 de Enero de 2017 (el formato de fecha puede variar dependiendo de nuestros ajustes de ubicación).
Nos aseguraremos de que la columna de Fecha aun está seleccionada y ahora iremos a Inicio y «Remover Columnas».
En la columna de «Prioridad» daremos click en el ícono de filtro y nos aseguraremos de que solo «M» esté seleccionado. Con la columna aun seleccionada, iremos de nuevo a Inicio y «Remover Columnas». De nuevo iremos a Inicio y ahora daremos a «Cerrar y Cargar». Con esto, la tabla deberá verse así:
De nuestra fuente de datos hemos creado una tabla que nos muestra los pedidos vendidos con prioridad «M» en Enero de 2017, ¿pero qué pasaría si quisieramos los pedidos vendidos con prioridad «H» en Marzo de 2017? Es aquí donde los parámetros entran en juego, por lo que a continuación estaremos creando algunos parámetros para cambiar el nombre y la fecha de forma dinámica.
CREANDO LOS PARÁMETROS DE POWER QUERY
Aunque tenga ese nombre, un parámetro es solo un query normal en donde profundizamos en el valor mismo y cargamos como una conexión. Para este ejemplo, vamos a usar una tabla de Excel como la fuente, pero de igual forma podría ser un rango nombrado, un archivo .csv o cualquier otra fuente de datos que podamos introducir en Power Query.
En la hoja de cálculo que contiene el query con la tabla de salida vamos a crear dos tablas con valores singulares en ellas y las vamos a nombrar «Fecha» y «Prioridad»:
CREANDO UN PARÁMETRO DE TEXTO
Primero vamos a crear el parámetro para poder cambiarle el nombre. Seleccionaremos la celda en la tabla «Prioridad» y crearemos un query al darle click a «Data» y luego «Desde Tabla/Rango». Aquí tendremos que prestar atención al tipo de datos, pues la columna «Prioridad» en el query original de arriba es un dato de tipo texto, mientras que el tipo de datos en este query también es un dato de tipo texto, por lo que debemos asegurarnos que ambos sean idénticos.
En Power Query le vamos a dar click derecho en el valor y seleccionaremos «Drill-Down» en el menú.
Nos aparecerá una ventana nueva, llamada «Herramientas de Texto»
Aquí crearemos una nota del nombre del query, el cual será «Priority», tal y como se muestra en la imágen de arriba. Ahora daremos click a «Archivo» y a «Cerrar y Cargar en…». De la ventana de selección de «Importar Datos» elegiremos «Solo crear conexión» y daremos OK.
El menú de Queries y Conexiones ahora nos mostrará dos queries: los datos originales y el parámetro de texto (llamado «Prioridad»).
CREANDO EL PARÁMETRO DE FECHA
Para el parámetro de fecha habrá que seguir los mismos pasos de nuevo. Comparado al parámetro de texto que creamos arriba, existe un paso adicional que necesitaremos. En el query original, la columna de Fecha tenía un tipo de fecha cuando fue filtrado, por lo que necesitamos ese tipo de fecha para este parámetro también.
Repitiendo el proceso, después de cambiar la columna de «Fecha» a un tipo de fecha daremos click derecho en el valor y click a «Drill-Down». Aquí en vez de que se abra la ventana de Herramientas de Texto, se abrirá la ventana de Herramientas de Fecha y Hora.
Crearemos una nota del nombre del query, que es «Fecha» en este caso. Tal y como hicimos la vez anterior, daremos click en «Cerrar y Cargar en…», seleccionaremos «Solo crear conexión» y OK. Con esto deberemos tener dos parámetros creados, «Prioridad» como un tipo de texto y «Fecha» como un tipo de fecha.
INSERTANDO LOS PARÁMETROS EN EL QUERY
Ya que hemos creado los parámetros, es hora de utilizarlos. Para hacer esto, vamos a hacer algunos cambios básicos al código M, y podemos utilizar ya sea el Editor Avanzado o la barra de fórmulas. Para mantenerlo simple, vamos a usar la barra de fórmulas para este ejemplo.
NOTA: El código M distingue mayúsculas y minúsculas, por lo que «Prioridad» y «prioridad» no serán tratados como valores iguales.
Si la barra de fórmulas no es visible, iremos a «Vista» y «Barra de fórmulas». Vamos a abrir el query original (que en este caso es «Table1») y buscaremos el paso en donde hemos cifrado el valor «M».
Y reemplazaremos «M» por el parámetro «Prioridad».
= Table.SelectRows(#"Removed Columns", each ([Order Priority] = "M"))
Se volverá
= Table.SelectRows(#"Removed Columns", each ([Order Priority] = prioridad))
Como paso siguiente vamos a aplicar el parámetro de Fecha. Buscaremos el paso en donde ciframos el 31 de Enero de 2017 como la fecha.
Reemplazaremos «#date(2017, 1, 31)» por el parámetro «Fecha».
= Table.SelectRows(#"Calculated End of Month", each ([Order Date] = #date(2017, 1, 31)))
Se convertirá ahora en:
= Table.SelectRows(#"Calculated End of Month", each ([Order Date] = fecha))
Y con todo esto hemos aplicado los parámetros. Iremos a «Inicio» y «Cerrar y cargar» para que los cambios se carguen en Excel.
USANDO LOS PARÁMETROS
Ahora que estamos de vuelta en Excel podemos cambiar los valores de las celdas «Fecha» y «Prioridad» para luego ir a «Data» y «Actualizar todo».
Si quieres ver el código completo del editor avanzado, aquí lo tienes:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Region", type text}, {"Country", type text}, {"Tansaction ID", type text}, {"Order Priority", type text}, {"Order Date", type date}, {"Order ID", type text}, {"Ship Date", type date}, {"Units Sold", Int64.Type}, {"Unit Price", type number}, {"Unit Cost", type number}, {"Total Revenue", type number}, {"Total Cost", type number}, {"Total Profit", type number}}),
#"Calculated End of Month" = Table.TransformColumns(#"Changed Type",{{"Order Date", Date.EndOfMonth, type date}}),
#"Filtered Rows" = Table.SelectRows(#"Calculated End of Month", each ([Order Date] = fecha)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Order Date"}),
#"Filtered Rows1" = Table.SelectRows(#"Removed Columns", each ([Order Priority] = prioridad)),
#"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows1",{"Order Priority"})
in
#"Removed Columns1"
Y así el query se actualiza para solo mostrar los valores para los parámetros que seleccionamos. Ya con esto podemos preparar cualquier valor cifrado de Power Query como parámetros. Entre las cosas más útiles que podemos tener como parámetro tenemos:
- Direcciones de archivos para importar archivos de datos externos
- Fechas de finalización de periodos para reportes financieros
- Nombres de divisiones de negocios o centros de costos para crear reportes para áreas específicas
- Algún otro ajuste que otro usuario pueda necesitar cambiar
Usa los parametros de Power Query sabiamente y recuerda que todo en exceso hace daño.
Cualquier duda nos leemos abajo.
