Los datos provienen de todo tipo de fuentes y Microsoft Power Query puede ayudarlo cuando necesita combinar datos externos. Es un poco más fácil que tratar de hacerlo en Microsoft Excel a veces.
Microsoft Power Query apareció en Excel 2016, aunque las versiones anteriores pueden acceder a Power Query mediante un complemento. Esta herramienta le permite importar, limpiar y editar datos de fuentes externas y luego usarlos en Excel o prepararlos para Power BI. Es una gran herramienta para tener en su caja de herramientas. En este artículo, le mostraré cómo combinar tres rangos de datos de Excel en Power Query. También agregaremos y completaremos una nueva columna para ayudar con el filtrado y el análisis posteriores.
VER: Política de instalación de software (TechRepublic Premium)
Uso Microsoft 365 en un sistema Windows 10 de 64 bits. Power Query está disponible como parte de la interfaz a través de Excel 2016. Excel para la web no es compatible con Power Query, pero puede ejecutar consultas existentes en un libro de Excel. Puede descargar un archivo de demostración.
los datos de excel
Muchos usuarios almacenan tipos de datos relacionados en hojas separadas, divididas por algún tipo de entidad que tiene sentido para los datos. Por ejemplo, puede mantener hojas de ventas mensuales o hojas de créditos y débitos mensuales. O puede realizar un seguimiento de las ventas y las comisiones por hojas de personal: una hoja para cada persona.
Este tipo de configuración funciona bien hasta que alguien quiere ver el panorama general: una revisión de todas las cifras de ventas mensuales junto con los detalles o todas las ventas y comisiones de todos los empleados. Si usa referencias 3D, es posible que tenga una hoja en algún lugar que combine todos los datos, pero esa hoja no mostrará los detalles, solo los subtotales y los totales. Además, esta disposición requiere que las láminas tengan la misma estructura. Ese no será siempre el caso.
Figura A muestra una hoja simple de ventas y comisiones por personal para el mes de enero. Hay otras dos hojas para febrero y marzo. Aquí es donde entra Power Query. Lo usaremos para combinar rápidamente las ventas y comisiones de enero, febrero y marzo.
Figura A
Cómo cargar los datos de Excel
El primer paso es cargar los tres conjuntos de datos en Power Query. Si no está trabajando con objetos de tabla, Power Query le pedirá que convierta el rango de datos en una tabla. Power Query solo funciona con objetos de tabla de Excel. De hecho, los nombres de las tablas son enero, febrero y marzo, según corresponda. Verá estos nombres más adelante en Power Query.
Comencemos cargando los datos de enero:
- Haga clic en cualquier lugar dentro de la tabla en la hoja de enero.
- Haga clic en la pestaña Datos.
- En el grupo de datos Obtener y transformar, haga clic en Desde tabla/rango.
Eso es todo al respecto. Figura B muestra los datos en Power Query.
Figura B
En este punto, es posible que desee considerar agregar el mes a la tabla de Power Query. En Excel, el mes se identifica por cada pestaña. Sin embargo, ese nombre de pestaña no va junto con los datos. Si la persona que solicita los datos combinados desea un análisis por mes, deberá agregarlo. Entonces, hagámoslo a continuación:
- En Power Query, haga clic en Agregar columna.
- En la columna General, haga clic en Columna personalizada.
- En el cuadro de diálogo resultante, ingrese un nombre significativo. Usaremos este nombre nuevamente cuando carguemos los otros dos rangos de datos en Power Query. En la Fórmula de columna personalizada, ingrese «Enero» (Figura C). Debe ingresar las comillas dobles (» «) para indicar la entrada como una cadena.
Figura C
- Haga clic en Aceptar para ver la nueva columna (Figura D).
Figura D
Agregaremos el mes a cada conjunto de datos que agreguemos. Al agregar el campo Mes a cada nueva tabla, tenga cuidado de ingresar exactamente el mismo nombre; esta característica distingue entre mayúsculas y minúsculas. Si ingresa «MES», la segunda vez, terminará con dos columnas nuevas, no una.
Salga de Power Query y guarde su nueva tabla cuando se le solicite. Debe hacer esto entre la carga de datos para cada mes. Power Query cargará la tabla de Power Query en una hoja nueva. No te preocupes por esto por ahora.
Ahora, repita el primer conjunto de instrucciones anteriores para cargar los datos en la hoja de febrero. Luego, repita el segundo conjunto de instrucciones para agregar la columna Mes y llénela con febrero. Repita todo esto nuevamente, con los datos en la hoja de marzo, agregue la columna Mes y complétela con marzo.
Con las tres tablas en Power Query, está listo para agregarlas en una sola.
Cómo agregar las tablas en Excel
¿Recuerdas cuando mencioné que los nombres de las tablas de Excel eran enero, febrero y marzo? Los nombres de las hojas también son enero, febrero y marzo.
En este punto, los tres meses están en Power Query como tablas y cada tabla ahora tiene una columna adicional: el campo Mes identifica cada mes. Esas nuevas tablas de Power Query también están en Excel y se nombran en consecuencia: enero (2), febrero (2) y marzo (2). Ahora estamos listos para agregar las tres tablas de Power Query en Excel; no tiene que volver a Power Query.
Para agregar las tres tablas, haga clic dentro de la tabla de Power Query (2 de enero) y haga lo siguiente:
- En la lista de Consultas a la derecha, haga clic con el botón derecho en la consulta de enero; en este punto, Power Query las llama consultas.
- En la lista resultante, haga clic en Agregar (Figura E).
Figura E
- En el cuadro de diálogo Agregar, haga clic en la opción Tres o más tablas.
- Haga clic en la segunda tabla, febrero, en la lista Tablas disponibles (la primera tabla, enero, ya forma parte de la consulta) y haga clic en Agregar.
- Haga clic en la tercera y última tabla en la lista Tablas disponibles y haga clic en Agregar (Figura F).
Figura F
- Con las tres tablas en la lista Tablas para agregar, haga clic en Aceptar.
Como puedes ver en Figura F (al menos parcialmente), los tres conjuntos de datos, enero, febrero y marzo, ahora forman parte de la misma tabla (consulta) y cada registro lo identifica por mes. En el panel izquierdo, el nombre de esta consulta es Anexar 1. En este punto, puede hacer clic en Cerrar y cargar en el grupo Cerrar para guardar la nueva tabla en Excel. Power Query nombrará la nueva hoja Apéndice 1, que puede cambiar. Pero ahora tiene los tres meses de registros para ordenar, filtrar y analizar de la forma que desee, y solo tomó unos minutos combinar todos esos datos. Nuestras tablas de Excel eran simples, pero puede ver la ventaja cuando se trata de muchos datos.
