Hello,
I'm building my energy dashboard, and have come a good way. Some background info:
- I have set up a date and time table as dimension table. Please find attached image of that, called "dateAndTime15min".
- The fact table "Detailed_energy_measurement" consists of energy consumption data reported hourly or every 15 minute from each site. That i have in the "timestamp" column. The problem with that data, is that it's not always reported exactly on the minute in some units, like 12:15:00. It can also be 11:12:25, and then exactly 15 minutes later11:17:25
- This gives me a problem when presenting the data in visualizations. I want to use my date table and the DateAndTIme15min as the x-axis to be able to compare each units with each other.
But when managing the relationships, it works only for the units where the time is exactly the same.
What I want, is to make sure that the data that is reported and summarized by 15 minute interval, even if the timestamp is not exacly 12:15:00. If it's for example 12:17:00, that will still be within the 12:15-12:30 window.
Is the problem the connection with the relations? Or is there a way in Power Query to keep the format, but to adjust to closest 15 minute?
There is a workaround, and that is to use the timestamp column as X-axis in the visualizations. But I want to keep as much information as possible in the dimension table since that's how it is supposed to be done..?
Thank you!
Hej Mattias!
Have you tried using the MROUND function? To round to nearest 15 minutes you simply use the multiple of 15/(60*24), so the formula should be
=MROUND(,15/(60*24))
/Anders
Or you can do it in Power Query using a formula like this:
=Time.From(Number.Round(Number.From(Time.From([timestamp]))*(24*60/15))/(24*60/15))
Mynda
Thank you both! I tried the Power query formula and it works for most values.
I added a column called "Rounded timestep". Then I merged the two columns "timestamp_key" and the "Rounde
However, for one unit the time is close to midnight I get an empty row once every day. Then I also get an empty raw when merging the two rows.
The code I put in when adding the column is a copy from Mynda's example.
= Table.AddColumn(#"Changed Type", "Rounded Timestamp", each Time.From(Number.Round(Number.From(Time.From([timestamp]))*(24*60/15))/(24*60/15)))
Hi Mattias,
You could try replacing Number.Round with Number.RoundDown. I suspect it doesn't know what to do when it rounds up because it technically should be rounding to the next day.
Mynda
Hello again,
That last tip takes away the empty rows. But for some reason it rounds down in a strange way twice a day. That adds five time steps in the hour 6 and 13, which leads to only three in the hour after. Please find attached example.
Hi Mattias,
Because time is represented as a decimal, I suspect there is a floating point rounding discrepancy between the timestamp and the rounded timestamp. That is, the timestamp is rounded up to display times at :00, :15, :30 but the rounded timestamp is rounding down.
You may need to look at the decimal representation of the timestamp to see whether you can round this before adding the rounded timestamp column.
Mynda
