Forum Discussion
M-code for date dimension table
What is an M-code for date dimension table that automatically updates/adds (incrementally) the new date, with each new day?
Hi SK_KE , Could you try this please
let
StartDate = #date(2020, 1, 1), // Change this to your date
// Define the end date as today
EndDate = DateTime.LocalNow(),
// Calculate the number of days between start and end date
NumberOfDays = Duration.Days(Duration.From(EndDate - StartDate)),
DateList = List.Dates(StartDate, NumberOfDays + 1, #duration(1, 0, 0, 0)),
DateTable = Table.FromList(DateList, Splitter.SplitByNothing(), {"Date"}),// Add additional columns for year, month, day, etc.
ExpandedTable = Table.TransformColumns(DateTable, {{"Date", each DateTime.Date(_), type date}}),
DateWithColumns = Table.AddColumn(ExpandedTable, "Year", each Date.Year([Date]), Int64.Type),
DateWithMonth = Table.AddColumn(DateWithColumns, "Month", each Date.Month([Date]), Int64.Type),
DateWithDay = Table.AddColumn(DateWithMonth, "Day", each Date.Day([Date]), Int64.Type),
DateWithQuarter = Table.AddColumn(DateWithDay, "Quarter", each Date.QuarterOfYear([Date]), Int64.Type),
DateWithMonthName = Table.AddColumn(DateWithQuarter, "MonthName", each Date.MonthName([Date]), type text),
DateWithWeekDay = Table.AddColumn(DateWithMonthName, "DayName", each Date.DayOfWeekName([Date]), type text)
in
DateWithWeekDay
If this post helped please do give a kudos and accept this as a solutionThanks In Advance
9 Replies
Hi SK_KE
You may use the following code. Change the date_from from the date that you want to and it will show the incrementally add whenever you refresh the table.
let
date_from = #date(2025,1,1),
step_num = Duration.Days(Date.From(DateTime.LocalNow()) - date_from) + 1,
Source = Table.FromList(List.Dates(date_from,step_num,#duration(1,0,0,0)), Splitter.SplitByNothing(), {"Date"}) in SourceHope this helps, do give a kudos if it worked for you!
Thank you Samantha - is the shared code complete - I'm trying to use it in my query editor but is showing an error. SK
It is a complete one, may I know the error you are facing?
let
date_from = #date(2025,1,1),
step_num = Duration.Days(Date.From(DateTime.LocalNow()) - date_from) + 1,
Source = Table.FromList(List.Dates(date_from,step_num,#duration(1,0,0,0)), Splitter.SplitByNothing(),{"Date"})
in
Source
Hi SK_KE , Could you try this please
let
StartDate = #date(2020, 1, 1), // Change this to your date
// Define the end date as today
EndDate = DateTime.LocalNow(),
// Calculate the number of days between start and end date
NumberOfDays = Duration.Days(Duration.From(EndDate - StartDate)),
DateList = List.Dates(StartDate, NumberOfDays + 1, #duration(1, 0, 0, 0)),
DateTable = Table.FromList(DateList, Splitter.SplitByNothing(), {"Date"}),// Add additional columns for year, month, day, etc.
ExpandedTable = Table.TransformColumns(DateTable, {{"Date", each DateTime.Date(_), type date}}),
DateWithColumns = Table.AddColumn(ExpandedTable, "Year", each Date.Year([Date]), Int64.Type),
DateWithMonth = Table.AddColumn(DateWithColumns, "Month", each Date.Month([Date]), Int64.Type),
DateWithDay = Table.AddColumn(DateWithMonth, "Day", each Date.Day([Date]), Int64.Type),
DateWithQuarter = Table.AddColumn(DateWithDay, "Quarter", each Date.QuarterOfYear([Date]), Int64.Type),
DateWithMonthName = Table.AddColumn(DateWithQuarter, "MonthName", each Date.MonthName([Date]), type text),
DateWithWeekDay = Table.AddColumn(DateWithMonthName, "DayName", each Date.DayOfWeekName([Date]), type text)
in
DateWithWeekDay
If this post helped please do give a kudos and accept this as a solutionThanks In Advance
Hi SK_KE,
Thanks for reaching out to the Microsoft fabric community forum.
It looks like you are trying create an Incremental date dimension table in PowerQuery (M-Code). As Akash_Varuna already responded to your query, please go through it and if it solves your issue then please mark the helpful response as solution.
I would also take a moment to thank Akash_Varuna, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
If I misunderstand your needs or you still have problems on it, please feel free to let us know.
Best Regards,
Hammad.
Community Support TeamIf this post helps then please mark it as a solution, so that other members find it more quickly.
Thank you.
Dear Akash, Thank you - let me try your suggested solution and advise. I'll need to test by seeing if the table will refresh automatically tomorrow. SK
Hi SK_KE,
As we haven’t heard back from you, so just following up to our previous message. I'd like to confirm if you've successfully resolved this issue or if you need further help.
If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. If you find a reply particularly helpful to you, you can also mark it as a solution.
If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.
Thank you for your patience and look forward to hearing from you.Hi SK_KE,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution so that other community members can find it easily.
Thank you.Hi SK_KE,
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
