Matriz excel con muchas columnas a Power BI

[ManuelEspitia]

Hola a todos:

Necesito realizar unos tableros de control de matrices de seguimiento a programas y proyectos públicos. La info la tengo en un archivo de excel que tiene solo 8 filas pero está distribuida en 177 columnas, como se ve en la primera imagen. A lo largo de las columnas hay un total de 20 tablas, cada una con sus propias columnas, como se ve en la segunda imagen, y en las tablas hay campos calculados con fórmulas de excel, como se ve en la tercera imagen.

Cuál es la forma correcta de subir esta info a power bi para hacer unos tableros de esta matriz? Debo separar cada una de las 20 tablas como independiente? Cómo las puedo relacionar a todas para que conserven la misma estructura? Lo puedo hacer en power BI o debo realizar la transformación en excel?

Espero me puedan ayudar!

[AllisonKennedy · Accepted solution]

¿Cómo obtiene actualmente estos datos en Excel: ¿cómo es el origen de datos sin procesar original? Recomendaría usar el formato de datos sin procesar y extraerlo en Power BI y, a continuación, usar Power Query de Power BI para crear las columnas calculadas.

Para relacionar las tablas en Power BI, necesitará una clave única para conectarlas: esto se puede hacer mediante un editor Agregar columna > índice en Power Query en Power Bi como uno de los primeros pasos al extraer los datos en Power BI la primera vez.

Háganos saber cómo los datos entran en Excel, sin embargo, ya que eso cambiará las recomendaciones que hacemos para el mejor método.

[PaulDBrown · Accepted solution]

@ManuelEspitia

Estoy de acuerdo con @AllisonKennedy Si puede acceder a los datos de origen que están creando las tablas/diseño, ústelo como origen.

Si no es así, transformaría cada uno de estos diseños de tablas en tablas reales (seleccione las filas y columnas para cada diseño y haría una ctrl + t para establecer cada una como una tabla real). Cuando se conecta al archivo de Excel como origen, todas las tablas creadas se mostrarán como tablas independientes en la fase de importación. Selecciónelos todos e impórtelos.
A continuación, puede comenzar a realizar las transformaciones necesarias en PQ, crear las tablas de dimensiones necesarias y cargar en el modelo para crear las relaciones entre las tablas de dimensiones y las tablas de hechos.

[PaulDBrown · Accepted solution]

@ManuelEspitia

Ok, he llegado tan lejos como puedo en esta etapa. El problema es que no tengo forma de saber cuál es la estructura de la información, ya que la mayoría de las tablas tienen columnas numéricas pero no hay filas de categoría identificables (lo tomo que era lo que estaba haciendo agregando un índice, pero dada la estructura de las tablas, parece que tiene sentido).

De todos modos, básicamente lo que he hecho hasta ahora es anexar las diversas tablas de informes por año en una tabla de informes correspondiente (listando todos los años). También he creado una tabla de dimensiones para el año.

Todavía queda trabajo por hacer que involucre:
1) despivo de las columnas de cuartos, pero no puedo proporcionar una solución ya que no conozco la estructura de datos y en particular qué filas / categorías son aplicables.

2) Adición de los trimestres a la tabla de años para crear una tabla de períodos

3) Crear tablas de dimensiones para campos comunes entre tablas.

Por favor, echa un vistazo al archivo adjunto

[PaulDBrown · Accepted solution]

@ManuelEspitia

Estoy de acuerdo con @AllisonKennedy Si puede acceder a los datos de origen que están creando las tablas/diseño, ústelo como origen.

Si no es así, transformaría cada uno de estos diseños de tablas en tablas reales (seleccione las filas y columnas para cada diseño y haría una ctrl + t para establecer cada una como una tabla real). Cuando se conecta al archivo de Excel como origen, todas las tablas creadas se mostrarán como tablas independientes en la fase de importación. Selecciónelos todos e impórtelos.
A continuación, puede comenzar a realizar las transformaciones necesarias en PQ, crear las tablas de dimensiones necesarias y cargar en el modelo para crear las relaciones entre las tablas de dimensiones y las tablas de hechos.

[ManuelEspitia]

---

Hola Paul

No hay otras fuentes de datos disponibles, solo estos que muestro acá en excel. Ya subí las tablas y las conecté con columnas de índice, funcionó muy bien, gracias.

Ahora necesito hacer modelado de datos visualizando metas, indicadores y acumulados por trimestre y año, pero el excel no tiene los datos organizados por fecha, solo tiene tablas de año y columnas de trimestre, como se ve en la imagen uno (Tabla completa en rojo, año en verde y trimestres en amarillo). De qué forma puedo organizar esa información para mostrar gráficas de barra o de línea con valores organizados cronológicamente por año y por trimestre?

También necesito hacer comparaciones entre tablas de presupuesto y de ejecución, que tienen estructura similar, ver imagen 2 (tablas y año en rojo). Estas son las dos primeras tablas, de programado y ejecutado en 2020, pero hay más de 2021, 2022 y 2023, con la misma estructura. Cómo puedo organizarlas de forma que pueda comparar valores y visualizarlos en orden cronológico?

Gracias por la ayuda.

[PaulDBrown]

@ManuelEspitia

Su solicitud requiere un número considerable de pasos en Power Query, que me atrevería a no tener sentido sin una muestra de las tablas implicadas. En otras palabras, necesitamos una muestra de las tablas para volver a crear el modelo que necesita.
puede proporcionar un archivo PBIX de ejemplo? (subirlo a un servicio en la nube como Onedrive, Google Drive, Dropbox y compartirlo desde allí)

[ManuelEspitia]

Claro que si, este es el link de la carpeta donde tengo los archivos. El uso compartido está activado para ver los archivos. Favor déjeme saber si pudo acceder. 

https://drive.google.com/drive/folders/120Y9-_deYQn9mOQG9d6Fuf-zI90dnEl_

El Excel se llama "Matriz seguimiento PDT Ataco". 

[PaulDBrown · Accepted solution]

@ManuelEspitia

Ok, he llegado tan lejos como puedo en esta etapa. El problema es que no tengo forma de saber cuál es la estructura de la información, ya que la mayoría de las tablas tienen columnas numéricas pero no hay filas de categoría identificables (lo tomo que era lo que estaba haciendo agregando un índice, pero dada la estructura de las tablas, parece que tiene sentido).

De todos modos, básicamente lo que he hecho hasta ahora es anexar las diversas tablas de informes por año en una tabla de informes correspondiente (listando todos los años). También he creado una tabla de dimensiones para el año.

Todavía queda trabajo por hacer que involucre:
1) despivo de las columnas de cuartos, pero no puedo proporcionar una solución ya que no conozco la estructura de datos y en particular qué filas / categorías son aplicables.

2) Adición de los trimestres a la tabla de años para crear una tabla de períodos

3) Crear tablas de dimensiones para campos comunes entre tablas.

Por favor, echa un vistazo al archivo adjunto

[ManuelEspitia]

@PaulDBrown

I did the unpivot of the timestres (quarters) columns in the "Programación Ejecución Metas" table (image 1) . How can I organize them and relate them to the Año (Year) table, so I can visualize them chronologically? Both tables, Año/Year and "Programación Ejecución Metas" are related by the Año column (image 2, in red).

[ManuelEspitia]

Hola Paul

¡Muchas gracias por tu ayuda! Creo que podré hacer algunos paneles a pesar de la mala calidad de los datos. Mientras estoy aprendiendo a usar power bi decidí practicar haciendo estas transformaciones por mi cuenta. Así que agregué una columna personalizada "año" (año) en cada tabla, pero cuando los anexé recibí un error (imagen 1). Parece que no está reconociendo una de las columnas del año y la divide en dos columnas diferentes. ¿Sabes por qué pasa esto?

Gracias por todo esto.

[PaulDBrown · Accepted solution]

@ManuelEspitia

¿Puede comprobar que el nombre de columna de Año en la tabla para 2022 no tiene espacios? La razón por la que hay una división en la fase de anexar es porque los nombres de columna no coinciden (por lo que Power Query los toma como campos diferentes y los divide). Así que hay algo en el nombre de la columna que es diferente de los nombres en las otras tablas (como digo, probablemente un espacio después de Año)

[PaulDBrown]

@ManuelEspitia

Los tengo, gracias.

[AllisonKennedy · Accepted solution]

¿Cómo obtiene actualmente estos datos en Excel: ¿cómo es el origen de datos sin procesar original? Recomendaría usar el formato de datos sin procesar y extraerlo en Power BI y, a continuación, usar Power Query de Power BI para crear las columnas calculadas.

Para relacionar las tablas en Power BI, necesitará una clave única para conectarlas: esto se puede hacer mediante un editor Agregar columna > índice en Power Query en Power Bi como uno de los primeros pasos al extraer los datos en Power BI la primera vez.

Háganos saber cómo los datos entran en Excel, sin embargo, ya que eso cambiará las recomendaciones que hacemos para el mejor método.

[ManuelEspitia]

Hola Alisson

No hay otras fuentes de datos disponibles, solo estos que muestro acá en excel. Ya subí las tablas y las conecté con columnas de índice, funcionó muy bien, gracias.

Ahora necesito hacer modelado de datos visualizando metas, indicadores y acumulados por trimestre y año, pero el excel no tiene los datos organizados por fecha, solo tiene tablas de año y columnas de trimestre, como se ve en la imagen uno (Tabla completa en rojo, año en verde y trimestres en amarillo). De qué forma puedo organizar esa información para mostrar gráficas de barra o de línea con valores organizados cronológicamente por año y por trimestre?

También necesito hacer comparaciones entre tablas de presupuesto y de ejecución, que tienen estructura similar, ver imagen 2 (tablas y año en rojo). Estas son las dos primeras tablas, de programado y ejecutado en 2020, pero hay más de 2021, 2022 y 2023, con la misma estructura. Cómo puedo organizarlas de forma que pueda comparar valores y visualizarlos en orden cronológico?

Gracias por la ayuda.