Mapas del tesoro 2

Esta es la segunda publicación de la serie Treasure Maps, donde analizamos varias formas de implementar tablas de mapeo. En la primera publicación , cubrimos SUMIFS. En esta publicación, hablaremos sobre Power Query.

Descripción general

Digamos que tenemos una lista de transacciones en un archivo CSV, como este:

Nos gustaría utilizar los datos para crear un informe como este:

Dado que las etiquetas de las transacciones (ID de cuenta 1000, 1001, 1002,…) y las etiquetas de los informes (efectivo y equivalentes de efectivo, inventario,…) son diferentes, necesitamos una forma de traducirlas. Entonces, creamos una tabla de mapeo, como esta:

Podríamos guardar la tabla de mapeo prácticamente en cualquier lugar, por ejemplo, un archivo CSV, un libro de Excel o una base de datos de Access. En este caso, simplificaremos las cosas y lo guardaremos en un archivo CSV.

Ahora, veamos cómo conectar los puntos con Power Query.

Video

Narrativo

Esto lo lograremos en tres pasos:

• Cargar las tablas
• Unir
• Construya el informe

Saltemos de inmediato.

Cargar tablas

Necesitamos cargar nuestras tablas de transacciones y mapeo en Power Query. Comenzaremos con la tabla de transacciones.

Actas

Lo primero que debemos hacer es incorporar las transacciones a Power Query. Dado que nuestras transacciones se almacenan en un archivo CSV, usaremos Datos Obtener datos Desde archivo Desde texto/CSV y luego buscaremos nuestro archivo de transacciones.

Nota: Si sus transacciones se realizan en otro lugar, deberá utilizar la opción Obtener datos correspondiente.

Podemos ver una vista previa como esta:

Dado que nuestros datos están limpios, no se necesitan transformaciones. Podemos expandir el botón Cargar haciendo clic en la flecha hacia abajo y seleccionando Cargar en . En el cuadro de diálogo resultante, seleccionamos Sólo Crear Conexión , así:

Nota: si es necesario limpiar sus datos, debe hacer clic en el botón Transformar y realizar las transformaciones necesarias antes de cargar la consulta en una consulta de solo conexión.

Como lo cargamos en una consulta de solo conexión, no vemos los resultados en una tabla de Excel… todavía. Lo cual es perfecto. Ahora que las transacciones están en Power Query, es hora de incorporar la tabla de asignación a Power Query.

Mapa

Básicamente realizamos los mismos pasos para recuperar la tabla de mapeo. Dado que nuestro mapa está almacenado en un CSV, podemos usar Obtener datos Desde archivo Desde texto/CSV y buscar el archivo del mapa. Vemos la vista previa y una vez más, Cargar en una consulta de solo conexión.

En este punto, deberíamos tener las consultas de datos y mapas en nuestro libro de trabajo. Deberíamos verlos en el panel Consultas y Conexión, así:

Con nuestras consultas de datos y mapas implementadas, es hora de combinarlas.

Unir

Para combinar estas dos consultas, utilizamos el comando Obtener datos Combinar Fusionar . En el cuadro de diálogo Combinar resultante, seleccionamos nuestra consulta de datos del menú desplegable superior y nuestra consulta de mapa del menú desplegable inferior, así:

Ahora necesitamos decirle a Power Query cómo se relacionan estas dos tablas. Si está familiarizado con BUSCARV, es como identificar el valor de búsqueda.

En nuestro caso, la columna de búsqueda es AcctID. Entonces seleccionamos ambas columnas AcctID, así:

Hacemos clic en Aceptar y vemos los resultados en el editor de Power Query, así:

Hacemos clic en el botón expandir en el lado derecho del encabezado de la columna del mapa y seleccionamos las columnas que queremos mostrar… en nuestro caso, la columna FS Line. Los resultados se ven así:

Una vez completado esto, podemos usar el comando Inicio Cerrar y cargar en y seleccionar nuestro destino en el cuadro de diálogo resultante. En nuestro caso, cargaremos los resultados en una tabla en una nueva hoja de trabajo:

Hacemos clic en Aceptar y la tabla resultante (en este caso, denominada Merge1 ) se muestra en nuestra hoja de trabajo:

Ahora sólo queda construir nuestro informe.

Informe

Tenemos opciones sobre qué tipo de informe utilizar para resumir los datos. En esta publicación, usaremos un informe basado en fórmulas y revisaremos la función SUMIFS analizada en la primera publicación de la serie.

Aquí está el diseño de nuestro informe:

Podemos escribir la siguiente fórmula en la celda G12 :

=SUMIFS(Fusionar1[Cantidad],Fusionar1[Línea FS],F12)

Y luego completa esa fórmula… bam:

Sí… ¡lo logramos!

Conclusión

Para actualizar el informe en períodos futuros, simplemente hacemos clic derecho en la tabla de resultados verde (por ejemplo, Merge1 ) y seleccionamos Actualizar . Power Query recuperará el archivo de transacciones actualizado, así como cualquier cambio realizado en el mapa. Siempre que no haya nuevas líneas FS y que se agreguen nuevas cuentas al mapa, estamos listos para comenzar.

Pero, ¿qué pasa si se agregan nuevas líneas FS al mapa? Con esta solución basada en fórmulas, necesitaríamos insertarlas manualmente en nuestro informe. Pero, en la próxima publicación, veremos cómo usar una tabla dinámica en lugar de fórmulas y abordaremos ese problema

Archivo de muestra

Mapasdeltesoro2Descargar

Nota: combiné el informe y los datos en un solo archivo para simplificar.