Linking data in groups of columns

[sprinklerguy]

Looking for some help here - I'm fairly new to Power BI.

I would like to link two sets of data - I've managed before on other projects but this one is proving tricky.

The project relates to thermocouples in different positions taking temperature measurements.

One set of data (Positions) is as follows:

Thermocouple	Section	Depth (mm)
Thermocouple 1	1	5
Thermocouple 2	1	10
Thermocouple 3	1	20
Thermocouple 4	1	30
Thermocouple 5	1	40
Thermocouple 6	1	50
Thermocouple 7	2	5
Thermocouple 8	2	10
Thermocouple 9	2	20
Thermocouple 10	2	30
Thermocouple 11	2	40
Thermocouple 12	2	50
Thermocouple 13	3	5
Thermocouple 14	3	10
Thermocouple 15	3	20
Thermocouple 16	3	30
Thermocouple 17	3	40
Thermocouple 18	3	50

The second set of data has the temperature values in time steps for each of these thermocouple (with nearly 700 rows) - a sample of which is below:

Time (s)	Thermocouple 1	Thermocouple 2	Thermocouple 3
0	24.8	24.8	24.8
0.17	24.8	24.8	24.8
0.33	24.8	24.8	24.8
0.5	24.9	24.8	24.8
0.67	24.8	24.8	24.8
0.83	24.9	24.8	24.8
1	24.9	24.9	24.8
1.17	24.9	24.9	24.8
1.33	24.9	24.9	24.8

When trying to Manage Relationships I want to link the Thermocouple column in the Position data with the group of numbered Thermocouple columns in the temperature data, but I only seem to have the option to link to one specific Thermocouple rather than all of them.

The intention is to have a line chart showing all the Thermocouples (already achieved) with a slicer or two to filter by position (Position and Depth).

I'm sure I'm missing something fairly obvious such as a transformation or some sort of grouping option but I'm drawing a blank at the moment - any help appreciated.

Thanks in advance.

[amitchandak · Accepted solution]

@sprinklerguy , better you unpivot the second data

https://radacad.com/pivot-and-unpivot-with-power-bi
Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/

Then you can join or have a common table Thermocouple and analyze data together.

https://www.seerinteractive.com/blog/join-many-many-power-bi/

https://www.dropbox.com/s/op9lb78w9utdonz/Distinct%20from%20two%20Tables.pbix?dl=0

[sprinklerguy]

Thanks @amitchandak - thanks working really well now.

I originally thought some of my data had been lost but Power BI was being too clever and had turned on "High Density Sampling" - once I turned that off I can see all my lines in the graph and can filter as needed with the slicers.

Thanks!

[sprinklerguy]

Thanks for the quick reply @amitchandak - I'll try that out and let you know how I get on.