Forum Discussion
How to Include "Time" in Date Hierarchy
Hi Power BI wizards,
I am new to Power BI and I am trying to build some reports based on energy data from smart meters.
The data is pretty much a time series with a timestamp of the following format "01/01/2017 15:30:00".
For each timestamp there will be a corresponding value of energy. My data in on a half-hour basis, thus for an annual time series there will be 17520 timestamps.
I would like to build a report in Power BI that includes the following chart: X-axis: Half-hour (for a single day of the year. i will insert a slicer so that the user can choose the day year of interest) and Y-axis: energy.
For having various date dimensions in power BI I am using the the standard script below on a Power BI query. The script is standard appears everywhere on the web. (https://pastebin.com/vxRc4AnU)
I have trying to modify this script to include the Time dimension (i.e. hour, hal-hour, minute) into this Date Table but I am struggling to do so. I am not getting it right. This is because I am fairly inexperienced with this.
I would like to kindly ask you whether you could give a quick hand in solving this. What code lines do I need to add in the code below to include the Time dimension (i.e. hour, hal-hour, minute) into this Date Table? Grateful if you could help me on this one. Thank you very much in advance.
let fnDateTable = (StartDate as date, EndDate as date, optional Culture as nullable text) as table =>
let
DayCount = Duration.Days(Duration.From(EndDate - StartDate))+1,
Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date]),type text),
InsertQuarterNum = Table.AddColumn(InsertYear, "Quarter Num", each Date.QuarterOfYear([Date])),
InsertQuarter = Table.AddColumn(InsertQuarterNum, "Quarter", each "Q" & Number.ToText([Quarter Num])),
InsertMonth = Table.AddColumn(InsertQuarter, "Month Num", each Date.Month([Date]), type text),
InsertStartOfMonth = Table.AddColumn(InsertMonth, "StartOfMonth", each Date.StartOfMonth([Date]), type date),
InsertEndOfMonth = Table.AddColumn(InsertStartOfMonth, "EndOfMonth", each Date.EndOfMonth([Date]), type date),
InsertDay = Table.AddColumn(InsertEndOfMonth, "DayOfMonth", each Date.Day([Date])),
InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each [Year]*10000 + [Month Num]*100 + [DayOfMonth]),
InsertMonthName = Table.AddColumn(InsertDayInt, "Month", each Date.ToText([Date], "MMMM", Culture), type text),
InsertShortMonthName = Table.AddColumn(InsertMonthName, "Month short", each Date.ToText([Date], "MMM", Culture), type text),
InsertCalendarMonth = Table.AddColumn(InsertShortMonthName, "Month Year", each [Month short]& " " & Number.ToText([Year]),type text),
InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "Quarter Year", each "Q" & Number.ToText([Quarter Num]) & " " & Number.ToText([Year]), type text),
InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "Weekday Num", each Date.DayOfWeek([Date])),
InsertDayName = Table.AddColumn(InsertDayWeek, "Weekday", each Date.ToText([Date], "dddd", Culture), type text),
InsertShortDayName = Table.AddColumn(InsertDayName, "Weekday short", each Date.ToText([Date], "ddd", Culture), type text),
InsertWeekEnding = Table.AddColumn(InsertShortDayName , "EndOfWeek", each Date.EndOfWeek([Date]), type date),
InsertWeekNumber= Table.AddColumn(InsertWeekEnding, "Week Num", each Date.WeekOfYear([Date])),
InsertMonthWeekNumber= Table.AddColumn(InsertWeekNumber, "WeekOfMonth Num", each Date.WeekOfMonth([Date])),
InsertMonthnYear = Table.AddColumn(InsertMonthWeekNumber,"Month-YearOrder", each [Year]*10000 + [Month Num]*100),
InsertQuarternYear = Table.AddColumn(InsertMonthnYear,"Quarter-YearOrder", each [Year]*10000 + [Quarter Num]*100),
ChangedType1 = Table.TransformColumnTypes(InsertQuarternYear,{{"Quarter-YearOrder", Int64.Type},{"Week Num", Int64.Type},{"WeekOfMonth Num", Int64.Type},{"Quarter", type text},{"Year", type text},{"Month-YearOrder", Int64.Type}, {"DateInt", Int64.Type}, {"DayOfMonth", Int64.Type}, {"Month Num", Int64.Type}, {"Quarter Num", Int64.Type}, {"Weekday Num", Int64.Type}})
in
ChangedType1
in
fnDateTable
10 Replies
mechanix85Helper I
StartDate and EndDate as DateTime
StartDate = #datetime(2016,1,1,0,0,0),
EndDate = #datetime(2016,12,31,23,59,59),Replace red to green
DayCount = Duration.Days(Duration.From(EndDate - StartDate))+1
Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
Source = List.DateTimes(StartDate, DayCount , #duration (0,0,1,0))
DayCount = Duration.TotalMinutes(Duration.From (EndDate - StartDate))+1,
#duration( days as number, hours as number, minutes as number, seconds as number)
Yep, mechanix85 has the right idea here. Way easier than what I posted.
Keep in mind that you'll need to do a hotswap on that date column in subsequent lines that try to run Date-based functions, because at this point, it's a DateTime (alternatively, use a new column called Date generated from the Date part of the DateTime). From there, you can isolate your date and time into new columns if you want, or just leave it as-is.
Using mechanix85's suggestion, the final formula could look something like this:
let fnDateTable = (StartDate as datetime, EndDate as datetime, optional Culture as nullable text) as table => let Source = List.DateTimes(StartDate, DayCount , #duration (0,0,30,0)), DayCount = Duration.TotalMinutes(Duration.From (EndDate - StartDate))+1,TableFromList = Table.FromList(Source, Splitter.SplitByNothing()), ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type datetime}}), RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "DateTime"}}), InsertDate = Table.AddColumn(RenamedColumns, "Date", each Date.From([DateTime]), type date), InsertTime = Table.AddColumn(InsertDate, "Time", each Time.From([DateTime]), type time), InsertYear = Table.AddColumn(InsertTime , "Year", each Date.Year([Date]),type text), InsertQuarterNum = Table.AddColumn(InsertYear, "Quarter Num", each Date.QuarterOfYear([Date])), InsertQuarter = Table.AddColumn(InsertQuarterNum, "Quarter", each "Q" & Number.ToText([Quarter Num])), InsertMonth = Table.AddColumn(InsertQuarter, "Month Num", each Date.Month([Date]), type text), InsertStartOfMonth = Table.AddColumn(InsertMonth, "StartOfMonth", each Date.StartOfMonth([Date]), type date), InsertEndOfMonth = Table.AddColumn(InsertStartOfMonth, "EndOfMonth", each Date.EndOfMonth([Date]), type date), InsertDay = Table.AddColumn(InsertEndOfMonth, "DayOfMonth", each Date.Day([Date])), InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each [Year]*10000 + [Month Num]*100 + [DayOfMonth]), InsertMonthName = Table.AddColumn(InsertDayInt, "Month", each Date.ToText([Date], "MMMM", Culture), type text), InsertShortMonthName = Table.AddColumn(InsertMonthName, "Month short", each Date.ToText([Date], "MMM", Culture), type text), InsertCalendarMonth = Table.AddColumn(InsertShortMonthName, "Month Year", each [Month short]& " " & Number.ToText([Year]),type text), InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "Quarter Year", each "Q" & Number.ToText([Quarter Num]) & " " & Number.ToText([Year]), type text), InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "Weekday Num", each Date.DayOfWeek([Date])), InsertDayName = Table.AddColumn(InsertDayWeek, "Weekday", each Date.ToText([Date], "dddd", Culture), type text), InsertShortDayName = Table.AddColumn(InsertDayName, "Weekday short", each Date.ToText([Date], "ddd", Culture), type text), InsertWeekEnding = Table.AddColumn(InsertShortDayName , "EndOfWeek", each Date.EndOfWeek([Date]), type date), InsertWeekNumber= Table.AddColumn(InsertWeekEnding, "Week Num", each Date.WeekOfYear([Date])), InsertMonthWeekNumber= Table.AddColumn(InsertWeekNumber, "WeekOfMonth Num", each Date.WeekOfMonth([Date])), InsertMonthnYear = Table.AddColumn(InsertMonthWeekNumber,"Month-YearOrder", each [Year]*10000 + [Month Num]*100), InsertQuarternYear = Table.AddColumn(InsertMonthnYear,"Quarter-YearOrder", each [Year]*10000 + [Quarter Num]*100), ChangedType1 = Table.TransformColumnTypes(InsertQuarternYear,{{"Quarter-YearOrder", Int64.Type},{"Week Num", Int64.Type},{"WeekOfMonth Num", Int64.Type},{"Quarter", type text},{"Year", type text},{"Month-YearOrder", Int64.Type}, {"DateInt", Int64.Type}, {"DayOfMonth", Int64.Type}, {"Month Num", Int64.Type}, {"Quarter Num", Int64.Type}, {"Weekday Num", Int64.Type}}) in ChangedType1 in fnDateTable- OpenDataLab
Helper II
From a design point of view I would have gone with 2 seperate tables, a date table and a seperate time table.
You will end up with the same result from a reporting point of view and your data model will be smaller and simplier.
Hi,
Thank you very much for your help.
I tried the mods you suggested and it works great.
Kind regards,
MJC
I was able to do it, but this is a pretty horrible hacky solution compared to how you probably are supposed to do it.
I started by creating a time table for every 30-minute increment in the day. To do this, I abused a list of integers with Number.Mod():
List.Transform({1..48}, each #time(Number.RoundDown((_ * 30) / 60), Number.Mod(_ * 30, 60), 0)),Basically, there's 48 30-minute increments in a day. For each increment, multiply it by 30 and convert those total minutes into hours and minutes, then use that in a time token (or whatever those things are called).
At this point, we have a list of 30-minute increments in a day:
Then, I used the code you posted to construct the date dimension. From there, we want a cross-product of our dates and times. Unfortunately, I didn't see an easy way for M to do this. So I went with the nuclear option: create a column on both tables containing only the value "1", then do an inner join on both the list of times and the dates.
After you do that, you expand that out and you end up with that same date dimension, except there's now 48 30-minute timeslots in an additional column for each date:
This can probably done with a single trivial change to your existing code, though, so take this all with a grain of salt.
Final full M queries:
Time table:
let Source = List.Transform({1..48}, each #time(Number.RoundDown((_ * 30) / 60), Number.Mod(_ * 30, 60), 0)), #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type time}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Times"}}), #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Joiner", each 1) in #"Added Custom"Date table:
let Source = DateTableGenerator(#date(2017, 1, 1), #date(2017, 6, 30), null), #"Added Custom" = Table.AddColumn(Source, "Joiner", each 1), #"Merged Queries" = Table.NestedJoin(#"Added Custom",{"Joiner"},Times,{"Joiner"},"Times",JoinKind.Inner), #"Expanded Times" = Table.ExpandTableColumn(#"Merged Queries", "Times", {"Times"}, {"Times.1"}), #"Renamed Columns" = Table.RenameColumns(#"Expanded Times",{{"Times.1", "Times"}}) in #"Renamed Columns"
