Forum Discussion
New table from based on existing (Sensor data and time data to average per hour).
Hi everyone!
I'm new to Power BI, and I have a question that might be straightforward, but I haven't been able to figure it out yet.
I have a dataset with temperature measurements and their corresponding timestamps, where data is collected several times per hour. In Power Query, I want to create a new table based on the original one, where the temperature is averaged for each hour. I've managed to do this in the Power BI "Report View" (see media attached), but I want to create a new table in Power Query so I can use this averaged data for further analysis before visualizing it in Power BI.
Can anyone guide me on how to achieve this in Power Query?
I have shared my Power BI File in the following link (Here)
It will be a 'Group By' on date and time with Average of the temperature. It's done via the interface on the 'Home' tab.
Hi Meinhardt
HotChilli has pointed out the correct direction. Here is a document for your reference: How to GROUP BY or summarize rows - Power Query As you already have Dato and Klokke (Time) columns in table Rum-Labratorie, you can duplicate or reference this table, then in the new table, group by Dato and Klokke (Time) columns to have the average of Temperatur in a new column.
In addition, it seems the current TidsTabel's Klokke (Time) column is probably not correct. I feel like creating it with below code will be better.
Time.StartOfHour([#"Klokke (minut)"])Best Regards,
Jing
6 Replies
- HotChilli
Community Champion
It will be a 'Group By' on date and time with Average of the temperature. It's done via the interface on the 'Home' tab.
Hi Meinhardt
HotChilli has pointed out the correct direction. Here is a document for your reference: How to GROUP BY or summarize rows - Power Query As you already have Dato and Klokke (Time) columns in table Rum-Labratorie, you can duplicate or reference this table, then in the new table, group by Dato and Klokke (Time) columns to have the average of Temperatur in a new column.
In addition, it seems the current TidsTabel's Klokke (Time) column is probably not correct. I feel like creating it with below code will be better.
Time.StartOfHour([#"Klokke (minut)"])Best Regards,
JingThanks for the indept explaination! and the link too! that realy gave me a better understanding. 🙂
Furthermore, thanks for giving me a heads-up on the error.
Rearding the error. When i open the file i dont have the same error? can it be some kind different setup?
