Has anyone created datetime table using QueryEditor or DAX?

[tondeli]

I have to create calendar table that contains dates with hours and minutes (each date will generate 1440 rows). I have created calendar table using DAX with CALENDAR-function, minDATE and maxDATE before. Now my data set requires calendar table with hours and minutes to be created for each date.

Any help will be appreciated (links, ideas etc.)!!

I have done this using SQL but I have no idea how to do this with PBI...

[MarcelBeug · Accepted solution]

Query Editor, suppose you have a table with Dates (Query Dates Dec 1-31,2017) and you want the datetime table from the minimum through the maximum date:

Query Dates:

#table(type table[Date = date],List.Zip({List.Dates(#date(2017,12,1),31,#duration(1,0,0,0))}))

Query DateTimes:

#table(
    type table[DateTime = datetime],
        List.Zip(
            {List.DateTimes(
                List.Min(Dates[Date]) & #time(0,0,0),
                1440 * (1 + Duration.Days(
                    List.Max(
                        Dates[Date])-
                    List.Min(
                        Dates[Date]))),
                #duration(0,0,1,0))}))

[tondeli]

Thx Marcel! I modified your code a bit and created dynamic date-table. Here are all 3 tables that I created in Query Editor.

Dates (Fact)

let
    Dates = List.Zip({List.Dates(#date(2017,12,1),31,#duration(1,0,0,0))}),
    #"Converted to Table" = Table.FromList(Dates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandListColumn(#"Converted to Table", "Column1"),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"Column1", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}})
in
    #"Renamed Columns"

Dynamic Date-table

let
    Source =    List.Dates(List.Min(Dates[Date]),
                Duration.Days(List.Max(Dates[Date])-List.Min(Dates[Date])),
                #duration(1,0,0,0)),

    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}})
in
    #"Renamed Columns"

DateTime-table based on dynamic date-table

let
    DateTimes = List.Zip(
            {List.DateTimes(
                                List.Min(DynamicDates[Date]) & #time(0,0,0),
                                1440 * (1 + Duration.Days(
                                List.Max(
                                    DynamicDates[Date])-
                                List.Min(
                                    DynamicDates[Date]))),
                                #duration(0,0,1,0))}),

    #"Converted to Table" = Table.FromList(DateTimes, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandListColumn(#"Converted to Table", "Column1"),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"Column1", type datetime}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "DateTime"}})
in
    #"Renamed Columns"

[MarcelBeug]

Hi Tondeli,

I very much appreciate your effort to understand my solution, and share your approach, rather than just copy/paste something without understanding it.

Otherwise I don't see much added value:

Your Dates query (5 lines) gives exactly the same result as mine (1 line).

Your DynamicDates query gives the same result as the Dates query, except you loose December 31.

And your final query gives the same result as my DateTimes query.

Anyhow if you feel more comfortable with your modifications, then of course you should use your versions (after correction for Decmber 31).

[tondeli]

You are correct. I just wanted to share my solution aswell. I just feel more comfortable to use code this way, sorry. Only difference is that I used dynamic dates creating date-table.