Hello,
I'm trying to greate a Power BI desktop where I use different types of sources of energy data. For example, I have separate files for:
- Energy consumpion
- Solar PV production
- Outside Airtemperature
- Energy price
I have the date for every hour, in the format YYYY-MM-DD HH:MM in the separate files. There might be some missing rows since it's automated data being read from all differens systems. Some I download from webpages, some from my own house's PV system.
What I need (I think...) is a Date dimension tabel in Power BI, which I can use to connect in the Model. The column DateAndTime will be the column that combines the tables in the separate files.
How do I create I Date dimension table where every row is one hour? I'm attaching two of my files:
- Energyexport.xlsx - this is the energy consumption and generation.
- Temperature.xlsx - This is the outdoor air temperature.
What I need is a tabel that combines them (And other tables). Also, it need to work even if there are som missing dates, but that I guess I can handle with the "one to many" when doing the connections?
Thank you so much!
Kind regards
Mattias
Thank you!
Hi Mattias,
Correct, you do need a date dimension table to connect your fact tables to. Power BI can automatically create date tables based on your data, but it won't include time. Therefore, you need to use Power Query to generate your date-time table. You can use the List.DateTimes function.
Hope that points you in the right direction.
Mynda
Hi Mynda,
Thanks for that input, I got it to work. I now have a date dimension table. However, I can't mark it as date table. I created a new column with only dates. But that means that I now have 24 rows with the same date for every day.
Will that be a problem? Should I make a separe table with only dates, with one row per day?
I have made some visualizations and they seem to work. I had some problem with the X-axis, but then realized that I have to change from "Date Hierarchy" to "dateandtime" (The name of my column with dates and time) to be able to have the hours of every day on the X-axis. Is that the way to do it?
Thanks
Mattias
Marking it as the date table is only important if you want to use the Time Intelligence DAX functions. If you want to do that, then you'll need a separate date table with a column containing unique dates.
Correct, you don't want a date hierarchy in your X axis if you want to see it at the hour level of detail.
Mynda
Hello,
I'm restarting this topic, since I need to use some DAX functions with Time intelligence.
I'm having problem with where to connect the date table, and what to put in the date table contra the table with hourly and sub-hourly rows.
What I have is:
- Fact table "detailed_energy_mea..." - Sub-hourly energy data, in format YYYY-MM-DD HH:MM:SS. It not always even minutes, is can be YYYY-MM-DD 12:11:55
- Dimension table "DateAndTime15min_2" created myself where every row is YYYY-MM-DD HH:MM:SS, where every row adds 15 minutes, always even: YYYY-MM-DD 12:15:00.
Since there are 4*24 rows every day, I can’t use it as date table.
Therefore I created a date table where every row is one day, and connected with the dimension table with 15 minute rows.
I need to be able so present my data by day, month and also fiscal year which is not the same as calendar year in this case.
On which table should I add columns like "month", Fiscal year and holidays and such? Is it to the date table (defined in Power BI), or in my table with 15 minute rows? And where should I connect the date table?
I need to go from second granularity data, to hourly (to be able to present energy use per hour, like the heat map in a separate question), to daily, monthly and then by fiscal year.
The way it's set up now, most things work except for the DAX functions since i don't get the date table doesn't seem to be in the right place. Also I want to make sure that I add new columns in the correct table when adding personal requirements like Fiscal year.
Also, should I round the time in the fact table, or will Power Bi understand the connection even when the time is not in even 15 minute format? To me it doesn't look like it.
I'm sorry I can't send the complete Power BI file. Attached are a few pictures of the connections and tables. Hope that explains it enough.
Thanks
Mattias
Hi Mattias,
You should put your fiscal periods in your date table without the times. You may also need this information in your date table with times if you expect to drill up/down all the way from year to times.
You should round your fact table times to the 15 minute intervals in your date-time table so you can create the relationships accordingly.
I hope that clarifies things.
Mynda
Hi,
Yes, that's clarifies that I should round my time in the fact table, and add Fiscal year to my date table, thank you.
But where should I connect the date table? Should I connect it to the DateAndTime-table that I have created for every 15 minutes? Or should I connect the energy and temperature fact tables with both the 15 minute table AND the date table?
And should I create new columns like "Name of day" and "Weekk of year" also to the DateTable?
Thanks
Mattias
Hi Mattias,
The Dayorder columns should be in your Date table along with name of day and week of year. You can then connect your DateAndTime15Min table to the Date table.
Mynda
