Forum Discussion

shekhar_shres's avatar
shekhar_shres
Icon for Advocate II rankAdvocate II
5 years ago

Date Table with only date column

Hi all,

When we create a Date table via blank query and invoke query with relevant start and end date we get a lot of dimensions for dates. 

I would like to create a date table with only Dates column. 

How can I achieve this. I tried playing with the m-script code we use to create dates table for this but it is not working. Any response will be highly regarded

Thank you

Need Help

1 Reply

  • TomMartens's avatar
    TomMartens
    Icon for Super User rankSuper User
    5 years ago

    Hey shekhar_shres ,

     

    I have to admit that I do not fully understand the challenge you are facing, as I miss an example of how your query does look like (the M code).
    For this reason, the M script creates a simple table with a single column of data type "Date" called "Date":

    let
    DateStart = #date(2021 , 1 , 1 )
    , DateEnd = #date(2021 , 12, 31 )
    , Source = List.Dates( DateStart , Number.From(DateEnd) - Number.From(DateStart) + 1 , #duration( 1 , 0 , 0 , 0 ) ),
    
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}})
in
    #"Changed Type"

    As it is also a best practice do not forget to disable the creation of auto date/time tables in the options of the current file:

    If you are not sure about this best practice, I recommend reading this article: Automatic time intelligence in Power BI - SQLBI

     

    Hopefully, this helps to tackle your challenge.

     

    Regards,
    Tom