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
tondeli
Advocate I
Advocate I

Has anyone created datetime table using QueryEditor or DAX?

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...

1 ACCEPTED SOLUTION
MarcelBeug
Community Champion
Community Champion

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))}))

 

Specializing in Power Query Formula Language (M)

View solution in original post

4 REPLIES 4
MarcelBeug
Community Champion
Community Champion

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))}))

 

Specializing in Power Query Formula Language (M)

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"

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).

Specializing in Power Query Formula Language (M)

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.

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.