Need help manipulating rows of half hourly sensor data from multiple sensors into one graph

[Ptown]

I have a spreadsheet with approx. 150,000 rows of half hourly sensor data.

There is a Sensor Hardware ID column, a date column. Then 48 columns representing each 30 minute period in a day, which contain values for the sensor reading during that period.

Below example data table of dummy data that I hope explains the structure. I have only put data for 2 sensors for 2 days, rather than pasting 150,000 rows. I have only put data from 00:00 to 02:30 (6 data columns rather than all 48). 

The output that I would like to get can be seen in the sketch below. I would like a graph that has sensor reading on the Y axis and time of day on the X axis. The reading values of the graph would be the average (at each time of day) for the days selected by a filter. Each sensor would be a different coloured line on the legend.

Please could someone help me understand how I need to manipulate my raw data into a form that will get me my desired result? Sorry if I'm asking in the wrong place or my question isn't clear. Or if the answer is already out there somewhere, I've tried searching for a solution but have had a hard time knowing what to search for.

Example data table

Sensor Hardware ID	DATE	00:00	00:30	01:00	01:30	02:00	02:30
ID-0001	01/01/2019	33	6.94	13.88	20.81	27.75	34.69
ID-0001	02/01/2019	22	4.63	9.25	13.88	18.50	23.13
ID-0002	01/01/2019	19	0.02	0.04	0.06	0.08	0.10
ID-0002	02/01/2019	23	0.04	0.08	0.13	0.17	0.21

Graph:

https://www.imageupload.net/image/jZHDb

https://imgur.com/a/nLdl2GH

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Ptown]

Thanks @Ashish_Mathur !

[Ashish_Mathur]

You are welcome.

[amitchandak]

First, you need to unpivot the data https://radacad.com/pivot-and-unpivot-with-power-bi

You need to have date and time dimension to analyze your data better

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Appreciate your Kudos. mark me with @

[Ptown]

Thanks @amitchandak !