Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
Hi,
You may download my PBI file from here.
Hope this helps.
You are welcome.
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 @
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |