Create a date table where the week will be considered from Mon to Sat irrespective of any date

[Anonymous]

Hello All

I want to create a Date Table in Power bi Desktop where I want a week column in which Mon will be the first day and Sat will be the last day irrespective of any dates. In the week column, I want Week1, Week2... Week4

[pi_eye · Accepted solution]

Hi @Anonymous, I believe this is what you are looking for:

You can use M Query to calculate the dates using the formula Date.WeekOfYear([Dates],1)

[Dates] = your date field

1 = starting day of week.

I created the calendar table using the guidance from this blog mssqltips.com/sqlservertip/6756/power-bi-calendar-table/ then used the dates it calculated to add the week columns.

Full code below - to use it, create a new blank query, and paste this into the advanced editor (option is in home -> query)

HTH, 

Pi

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjAwMDpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [StartDate = _t]),
#"Added Custom" = Table.AddColumn(Source, "EndDate", each Date.From(DateTime.LocalNow())),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom",{{"EndDate", type date}}),
#"Changed Type" = Table.TransformColumnTypes(#"Changed Type2",{{"StartDate", type date}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type", "Dates", each {Number.From([StartDate])..Number.From([EndDate])}),
#"Expanded Dates" = Table.ExpandListColumn(#"Added Custom1", "Dates"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Dates",{{"Dates", type date}}),
#"Removed Columns1" = Table.RemoveColumns(#"Changed Type1",{"StartDate", "EndDate"}),
#"Added Custom2" = Table.AddColumn(#"Removed Columns1", "Year", each Date.Year([Dates])),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Month", each Date.Month([Dates])),
#"Added Custom4" = Table.AddColumn(#"Added Custom3", "MonthName", each Date.MonthName([Dates])),
#"Added Custom5" = Table.AddColumn(#"Added Custom4", "ShortMonthName", each Text.Start([MonthName],3)),
#"Added Custom6" = Table.AddColumn(#"Added Custom5", "Quarter", each Date.QuarterOfYear([Dates])),
#"Changed Type3" = Table.TransformColumnTypes(#"Added Custom6",{{"Quarter", type text}}),
#"Added Custom7" = Table.AddColumn(#"Changed Type3", "QtrText", each "Qtr "& [Quarter]),
#"Added Custom8" = Table.AddColumn(#"Added Custom7", "WeekDayName", each Date.DayOfWeekName([Dates])),
#"Added Custom9" = Table.AddColumn(#"Added Custom8", "WeekDay", each Date.DayOfWeek([Dates])),
#"Added Custom10" = Table.AddColumn(#"Added Custom9", "Week #", each Date.WeekOfYear([Dates],1)),
#"Added Custom11" = Table.AddColumn(#"Added Custom10", "Custom", each "Week " & Number.ToText([#"Week #"]))
in
#"Added Custom11"