Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
erlesorl
New Member

Making a matrix with data from multiple data tables

Hi!

 

I'm new to Power BI, and have been struggeling a lot with this. I am working on creating a table/matrix which can represent data generated automatically for different devices located in different locations.

 

What I have is 4 data tables. Each table has a location, a device number, and a custom column on the form (location-device number) in order to have a unique ID for each device. In addition each table also has a date column, containing the 1st date of the month that the data is generated for. Each table contains a variety of different data, which I have been tasked with presenting in a particular way (the file I'm creating is supposed to replace an old solution that the users are very pleased with, but it requires manual work). I believe the problem is the same for all the tables, and that if I can solve it with 2 of them, the others will also be solved. This is a representation of how 2 of the tables look:

table 1:

LocationDevice numberunique_idDatedata1data2
loc101loc1-0101.01.21XXXYYY
loc201loc2-0201.01.21XXYYYX

table 2: 

LocationDevice numberunique_idDatedata3data4
loc101loc1-0101.01.21ZZZAAA
loc201loc2-0201.01.21ZZYAAZ

 

I have been able to create the layout that i want the data to be presented in, where the columns are the months, and the rows are the locations, with a "drill down" into device numbers (I also used a switch under values, which swapped the data1, data2,... from columns to rows):

   Year2021  2022
Location   JanuaryFebruary...Jan...
loc101      
  data1 XXX...... 
  data2 YYY...... 
  data3 ZZZ...... 
  data4 YYY...... 
 02   ...... 
  data1  ...... 
 .................. 
loc201   ...... 
  data1 XXY...... 
  data2 YYX...... 
  data3 ZZY...... 
  data4 AAZ...... 
 .................. 

 

However, I'm encountering a problem where the fields in red are not correct. I set up the table by having "Date" from table 1 as columns, and "Location" and "Device number" from table 1 as rows. For values, I input data1 through data4. 

 

For the data originating in table1, data1 and data2, this works, and everything is as expected. However, data3 and data4, does not sort, and I ended up with the sum of all the data in the data-column it originates from. Meaning that for example, all fields in the data 3 rows in the table above contains the same value, which is the sum of all data3 entries. I figured that the issue was that the data in table 2 is not able to sort itself using the "Date", "Location" and "Device number" from table1. 

 

I attempted to try and create a filter by connecting the "Date" from table1 with the "Date" from table2 in the modelling tab and selecting "table1 filters table2", and similarily with the other columns in the 2 tables that are supposed to be equal. This did not work at all.

 

I then pulled a date-file from the web, which contained a single column with dates going back to 2004. I chose that it would filter the "Date" column in in both table1 and table2, and changed the column of the matrix to be "Date" from the date-file. This worked partially. The values in the data3 and data4 columns now change from the different months, but, is equal to the sum of all entries in the month. For example, data3 in January 2021, is equal for all locations and device numbers, the value is the sum of all the data3 entries for January 2021.

 

I'm not sure on how to proceed from here. I don't really understand why it makes a difference that is use the columns from table1 as metadata in the matrix, when the values are corresponding to unique values in table2. How do I fix it from here?

The only solutions I can think of is :

- To merge the 4 tables into 1 larger one, which I want to avoid as I believe this might become messy later. (I am making a multipage report)

- To create a file similar to the date-file I pulled from the web, and use that to filter the tables, and use as the rows in the matrix. I have no idea how to do this, so if this would work, I would greatly appreciate some pointers on how to do this. 

 

I don't know if any of these would work, so I'm open to any suggestions anyone might have. Is this even possible to do? I feel like it should be. I've been stuck on this for 2 days now, and I have no idea of where I should go from here.

 

Thanks you for your time:)

1 ACCEPTED SOLUTION
williamadams12
Resolver I
Resolver I

@erlesorl - You only have a few options here, but you really only have a few data transformation steps before being able to render the view you need in a matrix table in BI. Remember, if the data you're working with is consistent, then creating a series of applied transformation steps in PowerQuery can be easily replicated with new data, even if it's a separate table. 

 

If it turns out to be many tables, then I would probably reconsider the initial data sourcing and ETL process, i.e., write a SQL script or maintain a cloud based data source to more easily bring in data as it's updated or refreshed for reporting purposes. 

 

1. Unpivot only the two data columns in each table to render the previous columns headers as row values. 

Table 1:

williamadams12_1-1656439846297.png

Table 2:

williamadams12_2-1656439859803.png

 

2. Append the two data tables into one single table. This is the equivalent of a UNION ALL function in SQL, it just simply stacks the data tables on top of one another - ensuring, of course, that data types and field headers are consistent in both tables. 

williamadams12_3-1656439962840.png

 

3. Drop in the necessary fields into Rows and Columns to render the view you need. 

williamadams12_0-1656439823786.png

 

Remember, unpivoting or flattening data in PowerQuery - granted you don't really have any DAX functions to write, of course - will help you render much more dynamic table matrices in Power BI. 

 

If this is what you were trying to accomplish, please mark this as a solution for others to review. 

 

 

View solution in original post

2 REPLIES 2
williamadams12
Resolver I
Resolver I

@erlesorl - You only have a few options here, but you really only have a few data transformation steps before being able to render the view you need in a matrix table in BI. Remember, if the data you're working with is consistent, then creating a series of applied transformation steps in PowerQuery can be easily replicated with new data, even if it's a separate table. 

 

If it turns out to be many tables, then I would probably reconsider the initial data sourcing and ETL process, i.e., write a SQL script or maintain a cloud based data source to more easily bring in data as it's updated or refreshed for reporting purposes. 

 

1. Unpivot only the two data columns in each table to render the previous columns headers as row values. 

Table 1:

williamadams12_1-1656439846297.png

Table 2:

williamadams12_2-1656439859803.png

 

2. Append the two data tables into one single table. This is the equivalent of a UNION ALL function in SQL, it just simply stacks the data tables on top of one another - ensuring, of course, that data types and field headers are consistent in both tables. 

williamadams12_3-1656439962840.png

 

3. Drop in the necessary fields into Rows and Columns to render the view you need. 

williamadams12_0-1656439823786.png

 

Remember, unpivoting or flattening data in PowerQuery - granted you don't really have any DAX functions to write, of course - will help you render much more dynamic table matrices in Power BI. 

 

If this is what you were trying to accomplish, please mark this as a solution for others to review. 

 

 

This worked, thank you. It took me a bit of time before I was able to implement it, but it did the trick:)

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.