Making a matrix with data from multiple data tables

[erlesorl]

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:

Location	Device number	unique_id	Date	data1	data2
loc1	01	loc1-01	01.01.21	XXX	YYY
loc2	01	loc2-02	01.01.21	XXY	YYX

table 2: 

Location	Device number	unique_id	Date	data3	data4
loc1	01	loc1-01	01.01.21	ZZZ	AAA
loc2	01	loc2-02	01.01.21	ZZY	AAZ

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):

			Year	2021			2022
Location				January	February	...	Jan...
loc1	01
		data1		XXX	...	...
		data2		YYY	...	...
		data3		ZZZ	...	...
		data4		YYY	...	...
	02				...	...
		data1			...	...
	...	...	...	...	...	...
loc2	01				...	...
		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:)

[williamadams12 · Accepted solution]

@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:

Table 2:

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. 

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

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. 

[erlesorl]

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