How to Include "Time" in Date Hierarchy

[MJC1]

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

[Anonymous]

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"

[mechanix85]

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)

[MJC1]

Hi,

Thank you very much for your help.

I tried the mods you suggested and it works great.

Kind regards,

MJC 

[Anonymous]

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

[MJC1]

Hi,

Thank you very much for your help.

I have not had the change to implement your approach but I will.

I will keep you posted on this.

Kind regards,

MJC 

[OpenDataLab]

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.

[MJC1]

Hi OpenDataLab,

I like your idea very much.

I would like to try your approach out.

Do you have the sript of for the Date Table and for the Time Table that can be shared?

I look forward to hearing from you.

Cheers,

MJC

[OpenDataLab]

Here are links to a typical date dimension and time dimension:

One Drive - Date Dimension

One Drive - Time Dimension

In Power Query you will need to split your date time field into a date field and a time field you can use the parse function to do this:

[MJC1]

Hi OpenDataLab,

I am now trying this option but I am having some problems.

I can load both power queries as per your script below. (just FYI I select the dates from 01/01/2017 to 31/12/2017)

Now I am having problems when I load my date. Essentially my CSV file has two columns. Column 1 Header is "Date" and contains dates of the following format 01/01/2017 and Column 2 Header is "XYZ" and contains numbers as this is my dataset. My data set starts at 01/01/2017 and finishes 24/07/2017 and moves on a 30 min time step.  When I try to load my get i get a message sayin that there are problems with rows. Perhaps this has to do with the Parse thing you mentioned. I'd be greatful if you could clarify how I could solve this or consider the parse. I need a little more info than that on your figure. I am a real novice.

Could it also be because the dates on my data are for half year whereas the range i selected in the power query is for entire year.

Many thanks for your help in advance.

[OpenDataLab]

Try this.  

The other way to parse the data is to change the data type, but when you do this you need to specifiy the locale of the source data.  One way to do this is to choose the "Using locale.." option on the change data type drop down of the column.

Once you select this option you will be presented with the following screen.  Choose the options illustrated below.

Now Power Query nows what the format of your source data, it can interpret the data correctly and convert it.