Parámetros de Power Query: 3 métodos + 1 ejemplo simple
Como Power Query registra los pasos de transformación, incluye muchos valores codificados dentro del código M. Por ejemplo, si filtramos una columna para seleccionar todos los valores mayores que 50 , 50 es un valor codificado en el código M. Para algo más dinámico, que podamos cambiar fácilmente, usamos parámetros de Power Query.
Obviamente, podríamos editar la consulta cada vez que queramos usarla. Sin embargo, esto llevaría mucho tiempo (y se volvería muy molesto, muy rápidamente). Entonces, los parámetros son la mejor manera de hacer que nuestras consultas sean flexibles para diferentes resultados.
Tabla de contenido
- ¿Qué es un parámetro de Power Query?
- Guión
- Crear la consulta
- Aplicar transformaciones
- Crear los parámetros
- Crear el parámetro de texto
- Crear el parámetro Fecha
- Insertar los parámetros en la consulta.
- Usando los parámetros
- Otros tipos de parámetros
- Administrar parámetros
- Valores estáticos
- Notas
- Privacidad del firewall de fórmula
- Cómo seleccionar todo
- Envolver
Descargue el archivo de ejemplo: únase al programa Insiders gratuito y obtenga acceso al archivo de ejemplo utilizado para esta publicación.
Nombre del archivo: 0103 Parámetros de Power Query.zip
Tener acceso
La descarga contiene los datos de origen y la solución completa. En esta publicación, analizamos todos los pasos de principio a fin para demostrar cómo crear la solución completa. Entonces, abra el libro de trabajo 0103 Power Query Parameters.xlsx y comencemos. yendo.
El término "Parámetro" puede parecer un poco confuso. Usamos el término variable cuando escribimos VBA, o en Excel, podemos usar los términos condiciones, criterios o argumentos. Efectivamente, todos describen lo mismo. Los parámetros, variables, condiciones, criterios y argumentos son valores que cambiamos para obtener un resultado diferente. Microsoft decidió utilizar el término parámetro al diseñar Power Query.
Hay muchas formas de crear parámetros. Inicialmente, esta publicación se centra en la creación de parámetros a partir de valores de celda. Como usuarios de Excel, estos serán los más útiles para nuestras necesidades. Más adelante en la publicación, consideramos otros métodos para brindarle una imagen completa. Luego podrá elegir el mejor enfoque para su escenario.
Técnicamente, el uso de valores de celdas no puede denominarse parámetro. Esto se debe a que, en Power Query, los parámetros tienen un método de creación particular. Sin embargo, para nuestros propósitos, los valores de las celdas pueden operar como parámetros; los usaremos como parámetros; así que llamémoslos simplemente parámetros "no oficiales". Hay más información sobre los parámetros "oficiales" más adelante en la publicación.
Guión
En nuestro escenario de ejemplo, queremos filtrar los datos para una fecha específica y un nombre de empleado según los valores de las celdas. Entonces, cada vez que actualizamos los datos, Power Query lee los valores de celda actuales y los aplica como parámetros en la consulta.
Estos son los datos de ejemplo que estamos usando:
Crear la consulta
El primer paso es crear una consulta como de costumbre.
Seleccione una celda de la tabla de origen y haga clic en Datos de tabla/rango en la cinta.
El editor de Power Query se abre con los datos.
Aplicar transformaciones
Hagamos las siguientes transformaciones.
Haga clic en el icono Fecha y hora junto al encabezado Fecha y seleccione Fecha en el menú de tipo de datos.
Seleccione el encabezado de la columna Fecha y luego haga clic en Transformar fecha mes Fin de mes
Todos los valores de la columna Fecha ahora son fechas de fin de mes.
Filtre la columna Fecha solo para incluir el 31 de enero de 2019. Tenga en cuenta que el formato de fecha puede aparecer diferente según la configuración de su ubicación.
Asegúrese de que la columna Fecha todavía esté seleccionada, luego haga clic en Inicio Eliminar columnas
Las transformaciones requeridas para la columna Vendido por son similares a las anteriores.
En la columna Vendido por , haga clic en el icono de filtro y asegúrese de que solo esté seleccionado David .
Con la columna Vendido por aún seleccionada, haga clic en Inicio Quitar columnas .
Son suficientes transformaciones por ahora. Asigne a la consulta el nombre SalesData y luego haga clic en Inicio Cerrar y cargar para cargar los datos en una tabla de Excel en la hoja de cálculo.
La tabla debería verse así,
A partir de los datos de origen, creamos una tabla que muestra los productos de David en enero de 2019. Pero, ¿qué pasa si queremos los productos vendidos por Sally en marzo de 2019 o por Mark en febrero de 2019? Aquí es donde entran los parámetros.
En la siguiente sección, creamos parámetros para cambiar el nombre y la fecha dinámicamente.
Crear los parámetros
Como se señaló anteriormente, inicialmente nos centramos en parámetros basados en celdas. En estos escenarios, un parámetro es simplemente una consulta estándar en la que profundizamos en el valor de una sola celda. Esta consulta de parámetros no se vuelve a cargar en la hoja de trabajo; se utiliza únicamente como marcador de posición en el código M de otra consulta. Como resultado, todas las consultas de parámetros se cargan solo como una conexión .
En este ejemplo, usamos una tabla de Excel como fuente de parámetros, pero también podría ser un rango con nombre, CSV, lista de SharePoint o cualquier otra fuente de datos que podamos ingresar en Power Query.
En la hoja de trabajo que contiene la tabla de resultados de la consulta, cree dos tablas con valores únicos en cada una.
tabla 1
- Encabezado: Fecha
- Valor: 31 de marzo de 2019
Tabla 2
- Encabezado: Vendido por
- Valor: sally
Después de crear cada tabla, cámbieles el nombre para que tengan nombres significativos. La primera tabla la nombré Date y la segunda SoldBy .
Crear el parámetro de texto
Primero, creemos el parámetro para contener el nombre.
Seleccione la celda en la tabla VendidoPor y cree una consulta haciendo clic en Datos de tabla/rango .
Necesitamos prestar mucha atención al tipo de datos. La columna Vendido por en la consulta SalesData original era un tipo de datos de texto. El tipo de datos para esta consulta también debe ser texto. Si es necesario, cambie el tipo de datos a texto.
A continuación, dentro de Power Query, haga clic derecho en el valor y seleccione Profundizar en el menú.
La ventana cambiará a una vista que no hemos visto antes, la vista Herramientas de texto .
No necesitamos la consulta SoldBy en nuestra hoja de trabajo; por lo tanto, queremos cargarlo sólo como una conexión. Haga clic en Archivo Cerrar Cargar en… (o Inicio Cerrar Cargar (menú desplegable) Cerrar y cargar en… ).
En el cuadro de diálogo Importar datos , seleccione Sólo crear conexión y luego haga clic en Aceptar .
El panel Conexión de consultas ahora mostrará dos consultas: la consulta original, SalesData y el parámetro de texto, SoldBy .
Crear el parámetro Fecha
Bien, repitamos los mismos pasos para el parámetro Fecha.
Como se señaló anteriormente, los parámetros deben tener el tipo de datos correcto. En la consulta SalesData original , la columna Fecha tenía un tipo de fecha. Por lo tanto, necesitamos que el parámetro también tenga un tipo de fecha.
Cargue la tabla de fechas en Power Query. Luego, haga clic en el icono al lado del encabezado Fecha y seleccione Fecha en el menú. Esto cambia la columna a un tipo de datos de fecha.
A continuación, haga clic derecho en el valor y haga clic en Profundizar . En lugar de Herramientas de texto, como vimos para el primer parámetro, esta vez será la vista Herramientas de fecha y hora .
Como antes, haga clic en Cerrar Cargar en…
Luego, en el cuadro de diálogo Importar datos , seleccione Crear solo conexión y haga clic en Aceptar .
Ahora deberíamos tener dos parámetros creados, SoldBy como tipo de texto y Fecha como tipo de fecha.
Insertar los parámetros en la consulta.
Hemos creado los parámetros; ahora estamos listos para usarlos. Para hacer esto, haremos cambios simples en el código M. Podríamos usar el Editor avanzado o la Barra de fórmulas para esto. Para simplificar las cosas, usaremos la Barra de fórmulas.
Importante: tenga cuidado al escribir porque el código M distingue entre mayúsculas y minúsculas.
Abra la consulta SalesData original .
Si la barra de fórmulas no está visible, haga clic en Ver barra de fórmulas en la cinta.
Encuentre el paso donde codificamos el valor, David .
Reemplace “David” por el parámetro SoldBy .
= Table.SelectRows(#"Removed Columns", each ([Sold By] = "David"))Se convierte en:
= Table.SelectRows(#"Removed Columns", each ([Sold By] = SoldBy))A continuación, apliquemos el parámetro Fecha. Busque el paso en el que codificamos el 31 de enero de 2019 como fecha.
Reemplace #date(2019, 1, 31) por el parámetro Fecha .
= Table.SelectRows(#"Changed Type1", each ([Date] = #date(2019, 1, 31)))Se convierte en:
= Table.SelectRows(#"Changed Type1", each ([Date] = Date))Eso es todo lo que hay; Hemos aplicado los parámetros.
Haga clic en Inicio Cerrar Cargar para cargar los cambios en Excel.
Usando los parámetros
Una vez que estemos de regreso en Excel, podemos cambiar los valores de la tabla Fecha y Vendido por, luego hacer clic en Actualizar datos todos .
La consulta se actualiza para mostrar solo los elementos que hemos solicitado.
¡Guau! Magia, ¡eh!
Podemos usar este método para configurar cualquier valor codificado de Power Query como parámetro. Creo que las cosas más útiles para configurar como parámetros son:
- Rutas de archivo para importar archivos de datos externos (consulte esta publicación para obtener más detalles: Cambiar la fuente de Power Query según el valor de la celda )
- Fechas de finalización del período para informes financieros.
- Nombres de divisiones comerciales o centros de costos para crear informes solo para áreas específicas
- Cualquier configuración que otro usuario probablemente necesite cambiar
Este es el tipo de escenarios a los que es más probable que nos enfrentemos como usuarios de Excel.
Otros tipos de parámetros
Técnicamente, lo que hemos visto hasta ahora no son “parámetros”. Los parámetros dentro de Power Query tienen características especiales. Hemos utilizado consultas vinculadas a celdas para que se comporten como parámetros. Pero no contienen las características especiales.
Administrar parámetros
Los parámetros oficiales tienen características que Power Query utiliza para funcionalidad avanzada (por ejemplo, funciones personalizadas o actualización incremental dentro de Power BI). Sin embargo, a menudo no utilizamos estas funciones, por lo que primero consideramos los valores basados en celdas.
Creemos los parámetros que usamos en nuestro ejemplo.
En Power Query, haga clic en Inicio Administrar parámetros para abrir el cuadro de diálogo Administrar parámetros.
Haga clic en Nuevo para crear un nuevo parámetro y luego ingrese los siguientes detalles:
- Nombre: Vendido por
- Teclee el texto
- Valor actual: Sally
Haga clic en Aceptar para cerrar el cuadro de diálogo.
Los detalles de la consulta de fecha son:
- Nombre fecha
- Tipo: Datos
- Valor actual: 31/03/2019 (este es un formato de fecha del Reino Unido, use su formato de fecha local si lo sigue)
Este método crea parámetros que podemos cambiar a través de la interfaz de usuario de Power Query.
Si bien estos parámetros tienen propósitos especiales, se utilizan en la consulta SalesData exactamente de la misma manera.
Valores estáticos
Hay otra forma de crear un parámetro "no oficial" dentro de Power Query.
En el ejemplo principal anterior, convertimos una consulta normal en un parámetro no oficial profundizando en un valor único. Podemos utilizar este enfoque con cualquier fuente de datos. Sin embargo, también podemos utilizar cualquier consulta que contenga un único valor.
A continuación se muestra un ejemplo de una consulta en blanco donde el valor se ingresa directamente en la barra de fórmulas.
Para usar estos parámetros simples, abrimos Power Query y cambiamos los valores según sea necesario.
Aunque se trata de consultas de valores únicos, se pueden convertir fácilmente a parámetros oficiales haciendo clic derecho en la consulta y seleccionando Convertir a parámetro en el menú.
Notas
A continuación se presentan algunas cosas que le ayudarán en el proceso de implementación de los parámetros de Power Query.
Privacidad del firewall de fórmula
Si comienza a utilizar Parámetros para pasar datos entre diferentes fuentes, probablemente encontrará el error Formula Firewall y problemas de privacidad. Para obtener más información sobre esto y cómo resolver el problema, consulte esta publicación: https://www.thepoweruser.com/2019/03/12/data-privacy-and-the-formula-firewall/
Cómo seleccionar todo
Este método puede funcionar con múltiples valores, pero decidí mantener las cosas simples y no demostrarlo en esta publicación.
Si desea que un interruptor vuelva a mostrar todos los elementos, siga esta publicación: Filtrar todo en Power Query
Envolver
Esta publicación nos muestra cómo crear parámetros de Power Query en varias formas. Creo que los parámetros vinculados a celdas son los más valiosos y flexibles para los usuarios de Excel. Sin embargo, si necesitamos una funcionalidad más avanzada, también hemos visto cómo usar la funcionalidad de parámetros integrada de Power Query.
Leer más publicaciones en esta serie
- Introducción a Power Query
- Obtener datos en Power Query: cinco fuentes de datos comunes
- DataRefresh Power Query en Excel: 4 formas de opciones avanzadas
- Utilice el editor de Power Query para actualizar consultas
- Conozca las opciones de Power Query Cerrar Carga
- Parámetros de Power Query: 3 métodos
- Transformaciones comunes de Power Query (más de 50 transformaciones poderosas explicadas)
- Anexar Power Query: combine rápidamente muchas consultas en 1
- Obtenga datos de una carpeta en Power Query: combine archivos rápidamente
- Listar archivos en una carpeta subcarpetas con Power Query
- Cómo obtener datos del libro actual con Power Query
- Cómo desvincular en Excel usando Power Query (3 formas)
- Power Query: valor de búsqueda en otra tabla con combinación
- Cómo cambiar la ubicación de los datos de origen en Power Query (7 formas)
- Fórmulas de Power Query (cómo usarlas y errores que se deben evitar)
- Declaración If de Power Query: condiciones anidadas ifs múltiples
- Cómo utilizar Power Query Group By para resumir datos
- Cómo utilizar las funciones personalizadas de Power Query
- Power Query: errores comunes, cómo solucionarlos
- Power Query: consejos y trucos
Deja una respuesta