Uno de los casos más interesantes y únicos son las Tablas de Parámetros en Power Query / Power BI.
Para crear una solución dinámica en Power Query / Power BI debemos asegurarnos de que pueda ser personalizada fácilmente y que funcione a la vez como un patrón en lugar de que sea una solución única para un solo archivo, tabla o cualquiera que sea nuestra consulta.
Aquí es donde entran en acción los parámetros y las tablas de parámetros.
Hemos visto antes como se pueden crear parámetros (ver las primeras 3 partes de la serie 1 | 2 | 3) usando la opción Gestionar Parámetros, pero ¿y si quieres que tus parámetros vengan de una tabla de cualquier otra fuente?
El propósito de organizar nuestros Parámetros de esta forma es que sea dinámico, y que en cada actualización podamos obtener los valores más recientes para tener otra capa de interacción en donde la gente pueda ingresar o cambiar valores o parámetros fuera de Power Query / Power BI. Quizás en una tabla de Excel o incluso en una base de datos de SQL Server.
Al final, se trata de proveer al usuario la forma más amigable de alcanzar sus objetivos basándonos en sus requerimientos específicos.
En esta entrada del blog cubriré las Tablas de Parámetros en Power Query / Power BI y los tipos que Tablas de Parámetros que puedes usar.
La receta original de by Ken Puls
Hace algunos años, antes de se inventara o creara “Gestionar parámetros” por el equipo de Power Query / Power BI, había una única forma de crear un pseudo Parámetro importado de una tabla de Excel.
La primera persona que escribió al respecto fue mi amigo y la leyenda Ken Puls. Puedes leer su entrada de blog sobre el tema aquí.
Ken Puls escribió esa entrada en 2014 y es aún relevante en la actualidad. Es una contribución muy profunda y de gran impacto al “juego” de Power Query que terminamos agregando al libro “M is for Data Monkey” que ambos escribimos.
Ken es alguien a quien podrías llamar una leyenda. Ha sido un MVP en Excel durante varios años, incluso antes que yo tuviera 18 años, y este tipo tiene gran conocimiento en muchas áreas de Excel, VBA, DAX, BI y muchas más (échale un vistazo a su Carta de Leyenda arriba).
Por su gran sabiduría inventó una fórmula de Excel que cambió por completo el escenario para todos los usuarios de Power Query dentro de Excel:
=LEFT(CELL(«filename»,B6),FIND(«[«,CELL(«filename»,B6),1)-1)
y esa fórmula de Excel te dará la ruta actual de tu archivo que luego puedes usar para hacer referencias sobre dónde pueden estar exactamente los demás archivos a partir de la solución.
De nuevo, haz clic en el vínculo y lee la publicación completa en el blog de Ken al respecto – es una muy buena lectura.
En su entrada del blog habla sobre una forma específica de hacer una tabla de Parámetros.
En esta entrada nos sumergiremos en el procedimiento para crear tablas de Parámetros y llevar tus habilidades de Parámetros a un nivel superior.
Posibles diseños para una tabla de parámetros
El primer tema que debemos abordar es ¿Cómo se ve una tabla de parámetros?
Tienen dos diseños distintos:
- Tabla en forma de Registros – esta es la que Ken utilizó en su blog y aquí hay una de ejemplo:
- Parámetros como Encabezados de Tabla – en lugar de depender de un único valor como parámetro, podemos tener múltiples valores para un único producto de salida. Aquí hay un ejemplo de cómo puedes usar una:
Ahora veamos como tratan de abordar diferentes casos.
El primer caso es bastante conciso: quieres usar un único valor para un Parámetro específico, pero si necesitas más de uno, como para cuando necesites invocar una función sobre varios parámetros al igual que en el caso de Parámetros como Encabezados de Tablas donde quieres consultar las mismas tablas de 2 servidores o bases de datos con direcciones IP diferentes, entonces la segunda opción será más sencilla que abordar el enfoque de una Tabla de Registros.
Referenciar Valores de una Tabla de Parámetros
En su publicación en el blog, Ken brindó una función personalizada fácil de usar y explicó cómo usarla para extraer los valores de una Tabla de Parámetros basada en un libro de Excel.
Como queremos ir más allá de eso y darte los consejos clave detrás del proceso para que puedas crear una propia, voy a hacer referencia a un artículo que creé hace unos cuantos meses sobre como Navegar a través de filas, columnas y celdas dentro de Power Query / Power BI. Haz clic en la siguiente imagen para ir a esa entrada del blog:
Este es de hecho un paso común para las Tablas de Parámetros en forma de Registros donde primero debes hacer referencia al parámetro que quieres usar como lo muestra Ken en su blog.
La forma más fácil de referenciar una celda específica es simplemente haciendo clic derecho en la celda y seleccionar la opción Rastrear desagrupando datos o Agregar como consulta nueva:
Este proceso puede consumir mucho tiempo si tienes múltiples parámetros a referenciar como el caso que mencioné anteriormente, donde tienes una tabla con todas las bases de datos que quieres conectar y las tablas específicas que quieres usar de esas bases de datos.
En ese caso, será mucho más eficiente utilizar los Parámetros como Encabezados de tabla y usar una Función Personalizada que use esos 3 parámetros para que podamos pasar cada fila como el argumento para la función usando el botón Invocar función personalizada, lo que es lo mismo que hemos cubierto en el artículo anterior sobre las Funciones Personalizadas:
(haz clic en la imagen para echarle un vistazo a la entrada sobre Funciones Personalizadas)
Cosas que debes tomar en consideración
Tu Tabla de Parámetros puede ser extraída de cualquier lugar, pero ten en mente que también tenemos que considerar los ajustes de privacidad para cada una de tus Fuentes de Datos ya que pueden activar algo llamado “Formula Firewall”, la cual cubriremos en una próxima oportunidad.
Nota que las llamamos Tablas de Parámetros, pero son psuedo Parámetros ya que, por definición, los Parámetros en Power Query / Power BI se definen dentro de la ventana Gestionar parámetros y también cubrimos la diferencia entre Argumentos y Parámetros, por lo que el término más apropiado en términos técnicos debería ser Tabla de Argumentos.
cordial saludo a todos.
Les cuento que tengo una consulta de Power Query en Excel que me funciona hasta cierto punto con un parametro de FECHA. Es aquí donde tengo problemas porque en el cuadro de dialogo me recibe la fecha de forma «21-09-2019» (sin comillas obviamente) pero cuando le pido al Query que me lea exactamente el mismo parámetro pero desde una celda me muestra un mensaje de error que dice: «el dia del mes debe estar entre el 1 y el ultimo dia del mes». Yo asumo que puede ser por el formato que en Power Query es de la forma: >=#21-09-2019#. He probado con varios formatos y nada que me funciona. Alguien se ha chocado con este problema ???
Hola! Te recomendaría revisar si en la celda el valor está como fecha. Si lo está, entonces el problema es a nivel de Power Query que probablemente esté utilizando el formato estadounidense y para solucionarlo puedes utilizar el siguiente artículo oficial de microsoft:
https://support.office.com/en-us/article/internationalization-power-query-d42b9390-1fff-413f-8120-d7df0ced20b9