Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
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