Forum Discussion

efilipe's avatar
efilipe
Icon for Helper IV rankHelper IV
9 years ago
Solved

Date Query

Hi Guys,   I have used a video explaining how to make a query to have a date table. Worked pretty well, until I found out my client uses dates in the future. I need to change the code to include al...
Need Help
  • MarcelBeug's avatar
    MarcelBeug
    9 years ago

    Remark: your code is incomplete as there is nothing after "in" and a comma is preceding "in".
    It doesn't matter though as the adjustment will be in the first part of the code.

     

    One way, closest to your solution, adjust the second step to:

    #"Invoke dates" = Source(#date(2017, 1, 1), Duration.Days(#date(2017,12,31)- #date(2017, 1, 1))+1, #duration(1, 0, 0, 0)),

     

    Another way (my preference): fist create a list with numbers representing dates, after conversion to table, adjust the data type to date.

     

    let
    Source = {Number.From(#date(2017,1,1))..Number.From(#date(2017,12,31))},
    #"List to table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"List to table",{{"Column1", type date}}),
    Date = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}}),

    No further changes to the code.

     

MarcelBeug's avatar
MarcelBeug
Icon for Community Champion rankCommunity Champion
9 years ago

Remark: your code is incomplete as there is nothing after "in" and a comma is preceding "in".
It doesn't matter though as the adjustment will be in the first part of the code.

 

One way, closest to your solution, adjust the second step to:

#"Invoke dates" = Source(#date(2017, 1, 1), Duration.Days(#date(2017,12,31)- #date(2017, 1, 1))+1, #duration(1, 0, 0, 0)),

 

Another way (my preference): fist create a list with numbers representing dates, after conversion to table, adjust the data type to date.

 

let
    Source = {Number.From(#date(2017,1,1))..Number.From(#date(2017,12,31))},
    #"List to table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"List to table",{{"Column1", type date}}),
    Date = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}}),

No further changes to the code.

 

efilipe's avatar
efilipe
Icon for Helper IV rankHelper IV
9 years ago

Thanks Marcel! Worked! I'll try the other way when I learn a little more!! :-) Thanks!!