Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MJC1
Frequent Visitor

Help Please: 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

3 REPLIES 3
vanessafvg
Super User
Super User

@MJC1 its better  practise to separate out your date and time table, do you need to have them together?

 

the amount of rows you will need combining them in a table will be huge, split out date and time , its also much better for performance and data compression





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Hi Vanessa,

 

Thank you very much for your prompt reply.

That's also an option. I found a sort of a Time Table on the web. I then loaded the Date Table and the Time Table into Power PI and tried to establish a relatioship between them but again I don't think I did it right as i couls not get the half hours from Time Table to align with Days of the Date Table. Since I am new to this, I strated getting confuse and then I though that it would be simpler for me understand time and date together in the same table. I am not planning to deal with more than a year of data at this stage.

 

Help in achieving the Date Table with the time dimension as in the initial post would be appreciated.

 

Kind regards,

MJC1 

MJC1
Frequent Visitor

Hi Everyone,

 

Any help in resolving this would be very much appreciated.

 

Thank you.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.