Excel matrix with many columns to Power BI

[ManuelEspitia]

Hello everyone:

I need to make some monitoring matrix control boards to public programs and projects. The info I have in an excel file that has only 8 rows but is distributed in 177 columns, as seen in the first image. Along the columns there are a total of 20 tables, each with its own columns, as seen in the second image, and in the tables there are calculated fields with excel formulas, as seen in the third image.

What is the right way to upload this info to power bi to make some boards of this matrix? Should I separate each of the 20 tables as independent? How can I relate them all to preserve the same structure? Can I do it in power BI or should I perform the transformation in excel?

I hope you can help me!

[AllisonKennedy · Accepted solution]

How do you currently get this data in Excel: what is the original raw data source like? I would recommend using the raw data format and extracting it into Power BI, and then using Power Query from Power BI to create the computed columns.

To relate tables in Power BI, you'll need a unique key to connect them: this can be done using an Add Column > Index editor in Power Query in Power Bi as one of the first steps when extracting data into Power BI the first time.

Let us know how the data enters Excel, however, as that will change the recommendations we make for the best method.

[PaulDBrown · Accepted solution]

@ManuelEspitia

I agree with @AllisonKennedy you can access the source data that the tables/design are creating, use it as a source.

If not, you would transform each of these table layouts into actual tables (select the rows and columns for each layout and do a ctrl + t to set each as an actual table). When you connect to the Excel file as the source, all created tables are displayed as separate tables in the import phase. Select them all and import them.
You can then start performing the necessary transformations on PQ, create the required dimension tables, and load into the model to create the relationships between dimension tables and fact tables.

[PaulDBrown · Accepted solution]

@ManuelEspitia

Okay, I've come as far as I can at this stage. The problem is that I have no way of knowing what the structure of the information is, since most tables have numeric columns but there are no identifiable category rows (I take what I was doing by adding an index, but given the structure of the tables, it seems to make sense).

Anyway, basically what I've done so far is append the various report tables per year to a corresponding reporting table (listed every year). I've also created a dimension table for the year.

There is still work to be done to involve:
1) Depivo of room columns, but I cannot provide a solution as I do not know the data structure and in particular which rows/categories are applicable.

2) Adding quarters to the year table to create a table of periods

3) Create dimension tables for common fields between tables.

Please take a look at the attachment

[PaulDBrown · Accepted solution]

@ManuelEspitia

I agree with @AllisonKennedy you can access the source data that the tables/design are creating, use it as a source.

If not, you would transform each of these table layouts into actual tables (select the rows and columns for each layout and do a ctrl + t to set each as an actual table). When you connect to the Excel file as the source, all created tables are displayed as separate tables in the import phase. Select them all and import them.
You can then start performing the necessary transformations on PQ, create the required dimension tables, and load into the model to create the relationships between dimension tables and fact tables.

[ManuelEspitia]

---

Hello Paul

There are no other data sources available, only these that I show here in excel. I already uploaded the tables and connected them with index columns, it worked very well, thank you.

Now I need to do data modeling by displaying goals, indicators and accumulated by quarter and year, but the excel does not have the data organized by date, it only has year tables and quarter columns, as seen in figure one (Full table in red, year in green and quarters in yellow). How can I organize that information to display bar or line charts with values organized chronologically by year and by quarter?

I also need to make comparisons between budget and execution tables, which have similar structure, see image 2 (tables and year in red). These are the first two tables, scheduled and executed in 2020, but there are more than 2021, 2022 and 2023, with the same structure. How can I organize them so that I can compare values and visualize them in chronological order?

Thanks for the help.

[PaulDBrown]

@ManuelEspitia 

Your request requires a considerable number of steps in Power Query, which I would venture is close to pointless without a sample of the tables involved. In other words, we need a sample of the tables to recreate the model you need. 
can you provide a sample PBIX file?(uploading it to a cloud service like Onedrive, Google Drive, Dropbox and sharing from there)

[ManuelEspitia]

Of course, this is the link in the folder where I have the files. Sharing is turned on to view files. Please let me know if you were able to access it.

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

The Excel is called "ATaco PDT Tracking Matrix".

[PaulDBrown · Accepted solution]

@ManuelEspitia

Okay, I've come as far as I can at this stage. The problem is that I have no way of knowing what the structure of the information is, since most tables have numeric columns but there are no identifiable category rows (I take what I was doing by adding an index, but given the structure of the tables, it seems to make sense).

Anyway, basically what I've done so far is append the various report tables per year to a corresponding reporting table (listed every year). I've also created a dimension table for the year.

There is still work to be done to involve:
1) Depivo of room columns, but I cannot provide a solution as I do not know the data structure and in particular which rows/categories are applicable.

2) Adding quarters to the year table to create a table of periods

3) Create dimension tables for common fields between tables.

Please take a look at the attachment

[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]

Hi Paul

Thanks a lot for your help! I think I´ll be able to do some dashboards despite of the poor quality of the data. As I'm learning to use power bi I decided to practice doing these transformations on my own. So I added a custom column "year" (año) on each table, but when I appended them I got an error (image 1). It seems like it's not recognizing one of the year columns and splits it in two different columns. Do you know why this happens?

Thanks for all this.

[PaulDBrown · Accepted solution]

@ManuelEspitia 

Can you check that the column name for Año in the table for 2022 has no spaces? The reason there is a split in the append phase is because the column names don't match (so Power Query takes them as different fields and splits them). So there is something in the column name which is different from names in the other tables (as I say, probably a space after Año)

[PaulDBrown]

@ManuelEspitia 

Got them, thanks

[AllisonKennedy · Accepted solution]

How do you currently get this data in Excel: what is the original raw data source like? I would recommend using the raw data format and extracting it into Power BI, and then using Power Query from Power BI to create the computed columns.

To relate tables in Power BI, you'll need a unique key to connect them: this can be done using an Add Column > Index editor in Power Query in Power Bi as one of the first steps when extracting data into Power BI the first time.

Let us know how the data enters Excel, however, as that will change the recommendations we make for the best method.

[ManuelEspitia]

Hola Alisson

There are no other data sources available, only these that I show here in excel. I already uploaded the tables and connected them with index columns, it worked very well, thank you.

Now I need to do data modeling by displaying goals, indicators and accumulated by quarter and year, but the excel does not have the data organized by date, it only has year tables and quarter columns, as seen in figure one (Full table in red, year in green and quarters in yellow). How can I organize that information to display bar or line charts with values organized chronologically by year and by quarter?

I also need to make comparisons between budget and execution tables, which have similar structure, see image 2 (tables and year in red). These are the first two tables, scheduled and executed in 2020, but there are more than 2021, 2022 and 2023, with the same structure. How can I organize them so that I can compare values and visualize them in chronological order?

Thanks for the help.