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.
Hello,
I know I can create a table using the Calendar function in DAX.
Is there a way that I can then use Power Query to add columns to this table or should I just use Power Query to create the Calendar?
Thanks,
Michael
Solved! Go to Solution.
Hi Michael,
I recommend you to create the table in Power Query.
Try the following script by creating a blank query. Pleas adjust the first two lines (StartDate and EndDate) to your needs directly in the query editor.
let
StartDate = #date(2020,1,1),
EndDate = #date(2023,12,31),
DateList = List.Dates(StartDate, Number.From(EndDate) - Number.From(StartDate), #duration(1, 0, 0, 0)),
DatesAsTable = Table.FromList(DateList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
RenamedColumnDate = Table.RenameColumns(DatesAsTable, {{"Column1", "PK_Date"}}),
ChangedTypeDate = Table.TransformColumnTypes(RenamedColumnDate,{{"PK_Date", type date}}),
Year = Table.AddColumn(ChangedTypeDate, "Year", each Date.Year([PK_Date])),
QuarterOfYear = Table.AddColumn(Year, "QuarterofYear", each Date.QuarterOfYear([PK_Date])),
QuarterNameOfYear = Table.AddColumn(QuarterOfYear, "QuarterNameOfYear", each "Q" & Number.ToText([QuarterofYear])),
QuarterWithYear = Table.AddColumn(QuarterNameOfYear, "QuarterWithYear", each Number.ToText([Year]) & "-" & [QuarterNameOfYear]),
MonthNum = Table.AddColumn(QuarterWithYear, "MonthNum", each Date.Month([PK_Date])),
MonthName = Table.AddColumn(MonthNum, "MonthName", each Date.ToText([PK_Date], "MMMM")),
MonthNameShort = Table.AddColumn(MonthName, "MonthNameShort", each Date.ToText([PK_Date], "MMM")),
MonthWIthYear = Table.AddColumn(MonthNameShort, "MonthWithYear", each Number.ToText([Year]) & "-" & [MonthNameShort]),
MonthNameSorting = Table.AddColumn(MonthWIthYear, "MonthNameSorting", each [Year] * 10 + [MonthNum]),
WeekNumOfYear = Table.AddColumn(MonthNameSorting, "WeekNumOfYear", each Date.WeekOfYear([PK_Date])),
WeekNameOfYear = Table.AddColumn(WeekNumOfYear, "WeekNameOfYear", each "KW" & Text.PadStart(Number.ToText([WeekNumOfYear]),2,"0")),
WeekWithYear = Table.AddColumn(WeekNameOfYear, "WeekWithYear", each Number.ToText([Year]) & "-" & [WeekNameOfYear]),
DayNumOfYear = Table.AddColumn(WeekWithYear, "DayNumOfWeek", each Date.DayOfWeek([PK_Date])+1),
DayNameOfWeek = Table.AddColumn(DayNumOfYear, "DayNameofWeek", each Text.Start(Date.DayOfWeekName([PK_Date]), 2)),
ChangeType = Table.TransformColumnTypes(DayNameOfWeek,{{"Year", Int64.Type}, {"QuarterofYear", Int64.Type}, {"MonthNum", Int64.Type}, {"WeekNumOfYear", Int64.Type}, {"QuarterNameOfYear", type text}, {"QuarterWithYear", type text}, {"MonthName", type text}, {"MonthNameShort", type text}, {"MonthWithYear", type text}, {"WeekNameOfYear", type text}, {"DayNameofWeek", type text}, {"DayNumOfWeek", Int64.Type}, {"MonthNameSorting", Int64.Type}})
in
ChangeType
creat blank query
adjust parameters
result
Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.
Thank you Mikelytics, I need dim_date and found your script!
In general, Rule of thumb is First Power Query, and then model, and then DAX based fields/measures.
Like you said, we can do Calendar table in power query and DAX. I prefer personally M Query and then model the data types and formats. There are lot of articles on the web on how to create both ways. It gets more interesting when you want to do Fiscal Calendar also.
To answer about adding columns,
you can do in power query
you can do in DAX
However, this is not possible, creating the DAX table first and then adding columns in Power Query. Other way works!
References for creating date tables:
https://learn.microsoft.com/en-us/power-bi/guidance/model-date-tables
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
https://www.sqlbi.com/articles/reference-date-table-in-dax-and-power-bi/
https://curbal.com/curbal-learning-portal/create-a-custom-calendar-in-power-bi-using-power-query
https://exceleratorbi.com.au/build-reusable-calendar-table-power-query/
https://gorilla.bi/power-query/date-table/
https://radacad.com/power-bi-date-or-calendar-table-best-method-dax-or-power-query
https://www.mssqltips.com/sqlservertip/6756/power-bi-calendar-table/
Hi Michael,
I recommend you to create the table in Power Query.
Try the following script by creating a blank query. Pleas adjust the first two lines (StartDate and EndDate) to your needs directly in the query editor.
let
StartDate = #date(2020,1,1),
EndDate = #date(2023,12,31),
DateList = List.Dates(StartDate, Number.From(EndDate) - Number.From(StartDate), #duration(1, 0, 0, 0)),
DatesAsTable = Table.FromList(DateList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
RenamedColumnDate = Table.RenameColumns(DatesAsTable, {{"Column1", "PK_Date"}}),
ChangedTypeDate = Table.TransformColumnTypes(RenamedColumnDate,{{"PK_Date", type date}}),
Year = Table.AddColumn(ChangedTypeDate, "Year", each Date.Year([PK_Date])),
QuarterOfYear = Table.AddColumn(Year, "QuarterofYear", each Date.QuarterOfYear([PK_Date])),
QuarterNameOfYear = Table.AddColumn(QuarterOfYear, "QuarterNameOfYear", each "Q" & Number.ToText([QuarterofYear])),
QuarterWithYear = Table.AddColumn(QuarterNameOfYear, "QuarterWithYear", each Number.ToText([Year]) & "-" & [QuarterNameOfYear]),
MonthNum = Table.AddColumn(QuarterWithYear, "MonthNum", each Date.Month([PK_Date])),
MonthName = Table.AddColumn(MonthNum, "MonthName", each Date.ToText([PK_Date], "MMMM")),
MonthNameShort = Table.AddColumn(MonthName, "MonthNameShort", each Date.ToText([PK_Date], "MMM")),
MonthWIthYear = Table.AddColumn(MonthNameShort, "MonthWithYear", each Number.ToText([Year]) & "-" & [MonthNameShort]),
MonthNameSorting = Table.AddColumn(MonthWIthYear, "MonthNameSorting", each [Year] * 10 + [MonthNum]),
WeekNumOfYear = Table.AddColumn(MonthNameSorting, "WeekNumOfYear", each Date.WeekOfYear([PK_Date])),
WeekNameOfYear = Table.AddColumn(WeekNumOfYear, "WeekNameOfYear", each "KW" & Text.PadStart(Number.ToText([WeekNumOfYear]),2,"0")),
WeekWithYear = Table.AddColumn(WeekNameOfYear, "WeekWithYear", each Number.ToText([Year]) & "-" & [WeekNameOfYear]),
DayNumOfYear = Table.AddColumn(WeekWithYear, "DayNumOfWeek", each Date.DayOfWeek([PK_Date])+1),
DayNameOfWeek = Table.AddColumn(DayNumOfYear, "DayNameofWeek", each Text.Start(Date.DayOfWeekName([PK_Date]), 2)),
ChangeType = Table.TransformColumnTypes(DayNameOfWeek,{{"Year", Int64.Type}, {"QuarterofYear", Int64.Type}, {"MonthNum", Int64.Type}, {"WeekNumOfYear", Int64.Type}, {"QuarterNameOfYear", type text}, {"QuarterWithYear", type text}, {"MonthName", type text}, {"MonthNameShort", type text}, {"MonthWithYear", type text}, {"WeekNameOfYear", type text}, {"DayNameofWeek", type text}, {"DayNumOfWeek", Int64.Type}, {"MonthNameSorting", Int64.Type}})
in
ChangeType
creat blank query
adjust parameters
result
Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.
User | Count |
---|---|
84 | |
80 | |
72 | |
71 | |
55 |
User | Count |
---|---|
108 | |
106 | |
93 | |
85 | |
67 |