Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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
User | Count |
---|---|
93 | |
86 | |
68 | |
62 | |
58 |
User | Count |
---|---|
149 | |
113 | |
95 | |
80 | |
72 |