Es posible que esté bastante familiarizado con las consultas de parámetros con su uso en SQL o Microsoft Query. Sin embargo, los parámetros de Power Query tienen diferencias clave:
- Los parámetros se pueden usar en cualquier paso de consulta. Además de funcionar como un filtro de datos, los parámetros se pueden usar para especificar elementos como la ruta de acceso de un archivo o el nombre de un servidor.
- Los parámetros no solicitan entradas. En su lugar, puede cambiar rápidamente su valor con Power Query. Incluso puede almacenar y recuperar los valores de las celdas de Excel.
- Los parámetros se guardan en una consulta de parámetros simple, pero son independientes de las consultas de datos en las que se usan. Una vez creadas, puede agregar un parámetro a las consultas según sea necesario.
Nota Si desea la otra forma de crear consultas de parámetros, consulte Creación de una consulta de parámetros en Microsoft Query.
Crear un parámetro
Puede usar un parámetro para cambiar automáticamente un valor en una consulta y evitar editar la consulta cada vez que cambia el valor. Solo tiene que cambiar el valor del parámetro. Una vez creado un parámetro, se guarda en una consulta de parámetros especial que puede cambiar cómodamente directamente desde Excel.
Seleccionar datos>Obtener datos>Otros orígenes>Inicie el Editor de Power Query.
En el Editor de Power Query, seleccione Inicio>Administrar parámetros > Nuevos parámetros.
En el cuadro de diálogo Administrar parámetro , seleccione Nuevo.
Establezca lo siguiente según sea necesario:
Nombre Esto debe reflejar la función del parámetro, pero mantenerlo lo más corto posible. Descripción Puede contener cualquier detalle que ayude a los usuarios a usar correctamente el parámetro. Obligatorio Siga uno de estos procedimientos:
cualquier valor Puede especificar cualquier valor de cualquier tipo de datos en la consulta de parámetros.
Lista de valores Puede limitar los valores a una lista específica especificándolos en la cuadrícula pequeña. También debe seleccionar un valor predeterminado y un valor actual a continuación.
Consulta Seleccione una consulta de lista, que se asemeja a una columna estructurada de lista separada por comas y entre llaves.
Por ejemplo, un campo de estado de problemas podría tener tres valores: {"Nuevo", "En curso", "Cerrado"}. Debe crear la consulta de lista de antemano abriendo el Editor avanzado (seleccione Home>:Editor avanzado), quitando la plantilla de código, ingresando la lista de valores en el formato de lista de consultas y, a continuación, seleccionando Listo.
Cuando termine de crear el parámetro, la consulta de lista se mostrará en los valores de parámetro.Tipo Especifica el tipo de datos del parámetro. Valores sugeridos Si lo desea, agregue una lista de valores o especifique una consulta para proporcionar sugerencias de entrada. Valor predeterminado Esto solo aparece si Valores sugeridos está establecido en Lista de valores y especifica qué elemento de lista es el predeterminado. En este caso, debe elegir un valor predeterminado. Valor actual Según dónde se use el parámetro, si está en blanco, es posible que la consulta no devuelva resultados. Si se selecciona Requerido , el valor actual no puede estar vacío. Para crear el parámetro, seleccione Aceptar.
Usar un parámetro para cambiar un origen de datos
Esta es una manera de administrar los cambios en las ubicaciones de origen de datos y ayudar a evitar errores de actualización. Por ejemplo, suponiendo un esquema y un origen de datos similares, cree un parámetro para cambiar fácilmente un origen de datos y ayudar a evitar errores de actualización de datos. A veces, el servidor, la base de datos, la carpeta, el nombre de archivo o la ubicación cambian. Tal vez un administrador de bases de datos ocasionalmente cambia un servidor, una entrega mensual de archivos CSV va a una carpeta diferente o necesita cambiar fácilmente entre un entorno de desarrollo / prueba / producción.
Paso 1: Crear una consulta de parámetros
En el ejemplo siguiente, tiene varios archivos CSV que importa mediante la operación de importación de carpeta (Seleccione Datos>Obtener datos> de la carpeta FilesFrom>) desde la carpeta C:\DataFilesCSV1. Pero a veces se usa ocasionalmente una carpeta diferente como ubicación para colocar los archivos, C:\DataFilesCSV2. Puede usar un parámetro en una consulta como valor sustituto para la carpeta diferente.
Seleccione Inicio>Administrar parámetros>Nuevo parámetro.
Introduzca la siguiente información en el cuadro de diálogo Administrar parámetro :
Nombre CSVFileDrop Descripción Ubicación alternativa de colocación de archivos Obligatorio Sí Tipo Texto Valores sugeridos cualquier valor Valor actual C:\DataFilesCSV1 Seleccione Aceptar.
Paso 2: Agregar el parámetro a la consulta de datos
- Para establecer el nombre de la carpeta como un parámetro, en Configuración de la consulta, en Pasos de la consulta, seleccione Origen y después seleccione Editar configuración.
- Asegúrese de que la opción Ruta de acceso del archivo está establecida en Parámetro y, a continuación, seleccione el parámetro que acaba de crear en la lista desplegable.
- Seleccione Aceptar.
Paso 3: Actualizar el valor del parámetro
La ubicación de la carpeta acaba de cambiar, por lo que ahora solo puede actualizar la consulta de parámetros.
- Seleccione la pestaña Conexiones de datos>& Consultas>Consultas , haga clic con el botón derecho en la consulta de parámetros y, a continuación, seleccione Editar.
- Escriba la nueva ubicación en el cuadro Valor actual , como C:\DataFilesCSV2.
- Selecciona Inicio>Cerrar & Cargar.
- Para confirmar los resultados, agregue nuevos datos al origen de datos y, a continuación, actualice la consulta de datos con el parámetro actualizado (seleccione Actualizar datos>todo).
Usar un parámetro para filtrar datos
A veces desea una manera fácil de cambiar el filtro de una consulta para obtener resultados diferentes sin editar la consulta o realizar copias ligeramente diferentes de la misma consulta. En este ejemplo, cambiamos una fecha para cambiar cómodamente un filtro de datos.
Para abrir una consulta, busque una cargada previamente desde el Editor de Power Query, seleccione una celda en los datos y, a continuación, seleccione Editar consulta>. Para obtener más información , vea Crear, cargar o editar una consulta en Excel.
Seleccione la flecha de filtro en cualquier encabezado de columna para filtrar los datos y, a continuación, seleccione un comando de filtro, como Filtros > de fecha y horadespués. Aparecerá el cuadro de diálogo Filtrar filas .
Seleccione el botón que está a la izquierda del cuadro Valor y siga uno de estos procedimientos:
- Para usar un parámetro existente, seleccione Parámetro y, a continuación, seleccione el parámetro que desee en la lista que aparece a la derecha.
- Para usar un nuevo parámetro, seleccione Nuevo parámetro y, a continuación, cree un parámetro.
Escriba la nueva fecha en el cuadro Valor actual y, a continuación, seleccione Inicio>cerrar & Cargar.
Para confirmar los resultados, agregue nuevos datos al origen de datos y, a continuación, actualice la consulta de datos con el parámetro actualizado (seleccione Actualizar datos>todo). Por ejemplo, cambie el valor del filtro a una fecha diferente para ver nuevos resultados.
Escriba la nueva fecha en el cuadro Valor actual .
Selecciona Inicio>Cerrar & Cargar.
Para confirmar los resultados, agregue nuevos datos al origen de datos y, a continuación, actualice la consulta de datos con el parámetro actualizado (seleccione Actualizar datos>todo).
Usar un valor de celda para filtrar datos
En este ejemplo, el valor del parámetro de consulta se lee desde una celda del libro. No tiene que cambiar la consulta de parámetros, solo tiene que actualizar el valor de la celda. Por ejemplo, desea filtrar una columna por la primera letra, pero cambiar fácilmente el valor a cualquier letra de A a Z.
En la hoja de cálculo de un libro donde se carga la consulta que quiere filtrar, cree una tabla de Excel con dos celdas: un encabezado y un valor.
Mi filtro G Seleccione una celda de la tabla de Excel y seleccione Datos>Obtener datos>de tabla o rango. Aparece el Editor de Power Query.
En el cuadro Nombre del panel Configuración de la consulta a la derecha, cambie el nombre de la consulta para que sea más significativo, como FilterCellValue.
Para pasar el valor de la tabla y no la tabla en sí, haga clic con el botón derecho en el valor en Vista previa de datos y seleccione Explorar en profundidad.
Observe que la fórmula cambió a= #"Changed Type"{0}[MyFilter]
Cuando usa la tabla de Excel como filtro en el paso 10, Power Query hace referencia al valor de tabla como la condición de filtro. Una referencia directa a la tabla de Excel provocaría un error.Selecciona Inicio>Cerrar & Cargar>Cerrar & Cargar a. Ahora tiene un parámetro de consulta denominado "FilterCellValue" que usará en el paso 12.
En el cuadro de diálogo Importar datos , seleccione Solo crear conexión y, luego, Aceptar.
Abra la consulta que quiera filtrar con el valor de la tabla FilterCellValue, una cargada anteriormente desde el Editor de Power Query, seleccionando una celda en los datos y, a continuación, seleccionando Editar consulta>. Para obtener más información , vea Crear, cargar o editar una consulta en Excel.
Seleccione la flecha de filtro en cualquier encabezado de columna para filtrar los datos y, a continuación, seleccione un comando de filtro, como Text Filters>Starts With. Aparecerá el cuadro de diálogo Filtrar filas .
Escriba cualquier valor en el cuadro Valor , como "G" y, después, seleccione Aceptar. En este caso, el valor es un marcador de posición temporal para el valor de la tabla FilterCellValue que escriba en el paso siguiente.
Seleccione la flecha situada en el lado derecho de la barra de fórmulas para mostrar toda la fórmula. Este es un ejemplo de una condición de filtro en una fórmula:
= Table.SelectRows(#"Tipo cambiado", each Text.StartsWith([Name], "G"))
Selecciona el valor del filtro. En la fórmula, seleccione "G".
Con M Intellisense, escriba la primera letra de la tabla FilterCellValue que creó y selecciónela en la lista que aparece.
Seleccione Inicio>Cerrar>Cerrar & Cargar.
Resultado
La consulta usa ahora el valor de la tabla de Excel que creó para filtrar los resultados de la consulta. Para usar un nuevo valor, edite el contenido de la celda en la tabla de Excel original en el paso 1, cambie "G" a "V" y, a continuación, actualice la consulta.
Controlar el uso de consultas de parámetros
Puede controlar si se permiten o no las consultas de parámetros.
- En el Editor de Power Query, seleccioneOpciones y configuración>de archivos>Opciones de consulta>:Editor de Power Query.
- En el panel de la izquierda, en GLOBAL, seleccione Editor de Power Query.
- En el panel derecho, en Parámetros, active o desactive Permitir siempre la parametrización en los cuadros de diálogo de transformación y origen de datos.