Help to insert Hour, Half-hour and Minute on Date Table

[MJC1]

Hello Power BI wizards,

I have been trying to insert Hour, HalfHour and Minute in the Date Table below so that I can then plot in BI and X-axis with hours or half-hour (30min step) of the day and on the Y-axis the data. However I have been unsuccessful. The code I have been trying to modify is the one below. Could I kindly ask you if you could give a hand with this. Would it be possible to modify the script below for me so that I can learn. Apologies as I am new to power BI. The idea is that I can drill down below day into hour and half-hours of that day. Thank you vey much in advance.



let CreateDateTable = (StartDate as date, EndDate as date, optional Culture as nullable text) as table =>
let
DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
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])),
InsertQuarter = Table.AddColumn(InsertYear, "QuarterOfYear", each Date.QuarterOfYear([Date])),
InsertMonth = Table.AddColumn(InsertQuarter, "MonthOfYear", each Date.Month([Date])),
InsertDay = Table.AddColumn(InsertMonth, "DayOfMonth", each Date.Day([Date])),
InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth]),
InsertMonthName = Table.AddColumn(InsertDayInt, "MonthName", each Date.ToText([Date], "MMMM", Culture), type text),
InsertCalendarMonth = Table.AddColumn(InsertMonthName, "MonthInCalendar", each (try(Text.Range([MonthName],0,3)) otherwise [MonthName]) & " " & Number.ToText([Year])),
InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "QuarterInCalendar", each "Q" & Number.ToText([QuarterOfYear]) & " " & Number.ToText([Year])),
InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayInWeek", each Date.DayOfWeek([Date])),
InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Date.ToText([Date], "dddd", Culture), type text),
InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEnding", each Date.EndOfWeek([Date]), type date)
in
InsertWeekEnding
in
CreateDateTable

[v-jiascu-msft]

Hi @MJC1,

Could you please tell me if your problem was resolved? Could you please share the answer or mark the proper answer as solution if it's convenient for you? That will be a help to the others.

Best Regards!
Dale

[OpenDataLab]

From a design point of view you should sperate out date from time.  Have one date dimension and one time dimension.

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

One Drive - Date Dimension

One Drive - Time Dimension

I answered a similar question here: https://community.powerbi.com/t5/Desktop/How-to-Include-quot-Time-quot-in-Date-Hierarchy/m-p/214093#...

[v-jiascu-msft]

Hi @MJC1,

How about using DAX? To be honest, I am not good at M. 

First, we need two tables, Hour and Minute. Why? Because "year", "month", etc. are the properties of a day. But time aren't. I think we need to combine day and time. (Maybe there are other good ways. ) Create two tables in Excel then import them. 0-23 for Hours and 0-59 for Minutes.

Second, create a new table with this formula.

Table =
CROSSJOIN (
    ADDCOLUMNS (
        CALENDAR ( DATE ( 2017, 1, 1 ), DATE ( 2017, 1, 1 ) ),
        "WeekNum", WEEKNUM ( [Date], 2 ),
        "Year", YEAR ( [Date] ),
        "Month", MONTH ( [Date] )
    ),
    'Hour',
    'Minute'
)

I didn't find a good way to display "half hour". Could you please share it?

Note: there are 1440 rows for one day. So it would be a big table if for a year or more.

Best Regards!

Dale

[MJC1]

Hi Dale,

Thanks a lot for this.

It looks a good idea and feels much simpler than the extensive Query script.

Ok, the query script is doing lots more but I don't need it at this stage.

QUESTION 1: 

How could I change the DAX formula to update the date automatically based on that date present in my CSV file?

Say that in my CSV my date column ranges from 01/01/2017 to 31/05/2017. Today I decided to update my CSV file and added dates up to 22/07/2017. Now I wish my DAX formula to dinamically update to cover all dates from 01/01/2017 to 22/07/2017. How could I update the formula you provided to account for this?

QUESTION 2:

Besides the hour and minute index, what I would like to see is the actual hour (e.g. 00:00:00, 01:00:00, etc.) and the half hours (e.g. 00:00:00, 00:30:00, 01:00:00, 01:30:00, etc.). How could I moddify the formula to incorporate these things. Note: I am not concerned about minute index and actual minute. We can loose those if it makes it easier.

Many thanks in advance for your help.

Kind regards,

Manuel.

[v-jiascu-msft]

@MJC1

Hi Manuel.,

I have tried @OpenDataLab's idea. It's wonderful. You could try it. To your question:

1. Your "csv" file will be imported as a table in Power BI. I suppose there is a date column. We could try this formula. The Date Table would update automatically.

DateTable =
CROSSJOIN (
    CALENDAR ( MIN ( 'CSV'[Date] ), MAX ( 'CSV'[Date] ) ),
    SELECTCOLUMNS (
        CROSSJOIN ( 'Hour', 'Minute' ),
        "Time", TIME ( [Hour], [Minute], 0 )
    )
)

2. We need to delete the other values in table "Minute" and keep "0" and "30". The formula above will work.

 

Best Regards!

Dale

[MJC1]

Hi Dale,

I had some troubles with the formula.

My original CSV file does have a column called data and this works well for CALENDAR ( MIN ( Book1[Date] ), MAX ( Book1[Date] ) ) part of the formula however for the other part of rest of the formula i get the following message "Cannot find table 'Hour'.". What am I missing? My CSV file only contains a column "Date" with the format: 01/01/2017. Do i need to include other columns like 'hour' and if so what does the format look like?

Thank you very much for your help.

Kind regards,

Manuel.

[v-jiascu-msft]

@MJC1

Hi Manuel,

We need to create two new tables. Please have a try.

>>First, we need two tables, Hour and Minute. Why? Because "year", "month", etc. are the properties of a day. But time aren't. I think we need to combine day and time. (Maybe there are other good ways. ) Create two tables in Excel then import them. 0-23 for Hours and 0-59 for Minutes.

Best Regards!

Dale

[MJC1]

Thank Dale and everyone else for the massive support.

I will try this one out and will get back you. It is likely to take few days due to limited availability...

Many Thanks.

[OpenDataLab]

Rather than create another table in your data model (that will take up more memory.  In tabular modelling less is more whe it comes to memory), rather just filter the time table in Power Query where minute is 0 or 30.