Forum Discussion
Has anyone created datetime table using QueryEditor or DAX?
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))}))
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))}))
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).
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.