Tablas de autorreferencia en Power Query

He tenido esta idea en mi cabeza durante más de un año, y hoy fue el día en que probé algunos escenarios hasta que obtuve una solución funcional. Permítanme comenzar con una descripción del problema y luego mi solución.

Agregar comentarios a un extracto bancario

El problema que estaba tratando de resolver fue cuando descargué una copia digital de mi extracto bancario, lo cargué en Excel usando Power Query y luego quise agregar algunos comentarios a algunas de las transacciones. Si solo agrega los comentarios al archivo fuente, entonces todo es fácil. Pero a menudo no es práctico agregar los comentarios directamente al archivo fuente. por un número de razones. Esto podría deberse a que obtiene una nueva copia de los datos de origen cada vez que actualiza (p. Ej., Obtiene un archivo CSV que reemplaza el archivo anterior y el archivo aumenta de tamaño cada mes), o podría ser si está utilizando un » Combine la técnica de varios archivos ”y solo querrá tratar con una versión resumida y limpia de los datos finales en lugar de tratar directamente con los archivos de origen.

Una vez que haya cargado la tabla en Power Query, es posible agregar una nueva columna a la tabla resultante (que se muestra a continuación). El problema con este enfoque es que los comentarios no están vinculados lógicamente a las filas de datos en la tabla. Tome el siguiente ejemplo. Los datos se cargan desde la tabla 1 usando Power Query, en la tabla 2 a la derecha. Luego agregué manualmente una nueva columna de comentarios y agregué algunos comentarios (como se muestra a continuación).

El problema que puede ocurrir se demuestra a continuación. Si el orden de clasificación de la tabla fuente cambios y entonces refrescas la consulta, los comentarios ya no se alinean con los datos originales. Y esto a pesar de tener una columna de ID única en la tabla original.

El problema es que la nueva columna que agregué manualmente a la tabla de Power Query a la derecha no está unido lógicamente a la tabla real, y por lo tanto los comentarios están en realidad en una columna siguiente a la mesa en lugar de parte de las filas de la tabla principal.

Entrar en tablas de autorreferencia

Yo leo esta publicación de blog de Imke hace bastante tiempo, y eso fue lo que me dio la idea de cómo podría solucionar este problema. La idea es cargar la tabla 2 anterior por segunda vez (después de agregar los comentarios) y luego unirla a sí misma., por lo tanto, unir lógicamente los comentarios agregados manualmente a las filas de la tabla principal.

Nota: Antes de completar los pasos de autorreferencia a continuación, utilicé Power Query para crear la segunda tabla a partir de la tabla de origen. Este fue mi punto de partida.

Luego Cargué la tabla resultante 2 por segunda vez como sigue:

1. Seleccionar en la segunda tabla
2. Vaya al menú de Power Query
3. Cree una nueva consulta usando «desde la tabla».

Cambié el nombre de esta nueva consulta a «Comentarios» y seleccioné «Cerrar y cargar en» para que solo creara una conexión pero no cargara la nueva tabla en la hoja de trabajo.

Lo siguiente que debe hacer es fusionar la consulta original con la nueva consulta. Para hacer esto,

1. Regrese y edite la consulta original para la tabla 2
2. Agregue un nuevo paso a la consulta usando «Fusionar consulta»
3. Combine la segunda consulta «Comentarios» con la consulta original uniéndose en la columna de ID como se muestra a continuación.

Esto le dará una nueva columna en su consulta, y la columna contiene una tabla (como se muestra a continuación). Luego puede expandir esta nueva columna para extraer los comentarios.

Solo extraje la columna de comentarios y deseleccioné la última opción como se muestra a continuación. Tenga en cuenta que es importante que anule la selección de «Usar el nombre de la columna original como prefijo». para que la nueva columna tenga el mismo nombre que la columna de origen original.

Haga clic en Aceptar.

Editar mayo de 2020. Power Query siempre está cambiando. Parece que ha habido un cambio que introduce la necesidad de dar otro paso en esta etapa. Después de expandir la nueva columna, verá que Power Query cambia el nombre de la nueva columna y la llama Comentarios.1 (que se muestra a continuación en 1).

Debe editar el código que se muestra en 1 y eliminar el .1 para que la nueva columna tenga exactamente el mismo nombre que la tabla original que cargó.
Después de haber realizado este cambio, puede cerrar y cargar.

Cuando miras la tabla ahora, todo se ve igual que antes (ver más abajo), pero hay una diferencia importante. La última columna de la tabla Power Query es ahora parte de la tabla y no una columna agregada manualmente. Ahora tiene una tabla de autorreferencia. EDITAR 2017. Si obtiene una nueva columna llamada Comentario2, simplemente elimínela. Todo lo demás funcionará. Hubo un cambio en la forma en que funciona Power Query en algún momento y ahora aparece esta columna duplicada con «2» adjunto. Pero puede (y debe) eliminarlo, entonces todo funcionará.

Para probar esto, completé la misma prueba de clasificación que antes. Primero ordeno la tabla de origen, luego actualizo la tabla de Power Query; puede ver los resultados a continuación. Tenga en cuenta que esta vez los comentarios se quedan en la fila de la tabla: ¡dulce!

Agregar comentarios de forma incremental

Ahora que todas las consultas están configuradas, puede agregar comentarios de forma incremental a la tabla de Power Query cuando lo desee. Y también puede agregar nuevos datos a los datos de origen; todo funcionará como es de esperar, como se muestra a continuación.

Datos de la vida real

La demostración anterior es, por supuesto, datos de prueba. Un escenario de la vida real más realista sería descargar un archivo CSV de su banco y utilizar una de las muchas técnicas de «combinar varios archivos» para importar los datos. Es probable que sus datos de origen estén desordenados y contengan muchas columnas que no necesita. En este escenario de la vida real, no es fácil agregar comentarios a los datos de origen a menos que abra cada archivo y agregue los comentarios allí; esto no es lo ideal. Otro problema podría ser que su fuente de datos podría ser un archivo CSV que utiliza un método de «eliminar y reemplazar con el nuevo archivo más grande que contiene los últimos registros». En ambos escenarios, deberá tener sus comentarios agregados manualmente ubicados en otro lugar que no sea la tabla de origen. La solución proporcionada aquí es una forma simple e intuitiva de administrar esto para el usuario (aunque primero con un poco de configuración).

El ejemplo que se muestra en esta publicación funcionará bien siempre que se mantengan 2 condiciones.

1. Tienes una identificación única para cada registro
2. No carga datos duplicados accidentalmente, ¡así que no haga eso!

Pero, ¿qué pasa si no tengo una columna de identificación?

El siguiente problema lógico que puede tener es un escenario en el que no tiene una columna de ID. Probé bastantes escenarios, incluida la adición de una columna de ID dentro del primer paso de Power Query (agregar columna de índice). No he podido encontrar una solución que funcione el 100% del tiempo, pero hay una que puede funcionar para la mayoría de las personas. Si sus datos son tales que cada fila de la tabla es única (por ejemplo, fecha, tal vez una columna de tiempo si tiene la suerte de tener esto, monto de la transacción, saldo restante, etc.) podría crear una ID única concatenando estas columnas en una única columna única. Esto debería resolver el problema para la mayoría de las personas en escenarios no comerciales (es más probable que aquellos en escenarios comerciales tengan una identificación de referencia de todos modos). Incluso podría crear una consulta de auditoría que verifique si hay duplicados y le advierta si esto ocurre, pero eso suena como otra publicación de blog.

Espero que este artículo le resulte útil y que le inspire a pensar en sus propias formas innovadoras de utilizar Power Query.

Aquí hay un artículo que muestra cómo usar un enfoque similar con filas en lugar de columnas, creando efectivamente una actualización incremental de datos. https://blog.jamesbayley.com/2018/04/23/power-query-how-to-load-only-fresh-rows-and-create-an-excel-history-table/

Si desea una lección completa sobre cómo usar Power Query, consulte mi curso de capacitación aquí https://exceleratorbi.com.au/product/power-query-excel-power-bi-online-training/