//Book: Power Pivot and Power BI - by Rob Collie and Avi Singh //More Info at http://www.powerpivotpro.com/the-book/ //Calendar M Query let ChangedType = Table.TransformColumnTypes(Bookings,{{"Date", type date}}), //Rename Bookings, Date as needed for your dataset MaxDate = Record.Field(Table.Max(ChangedType, "Date"),"Date"), //Rename Date as needed for your dataset MinDate = Record.Field(Table.Min(ChangedType, "Date"),"Date"), //Rename Date as needed for your dataset DaysElapsed = Number.From(MaxDate-MinDate), DatesList = List.Dates(MinDate, DaysElapsed+1,Duration.From(1)), RawDatesTable = Table.FromList(DatesList, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error), ChangedType1 = Table.TransformColumnTypes(RawDatesTable,{{"Date", type date}}), InsertedDay = Table.AddColumn(ChangedType1, "Day", each Date.Day([Date]), type number), InsertedMonth = Table.AddColumn(InsertedDay, "Month", each Date.Month([Date]), type number), InsertedYear = Table.AddColumn(InsertedMonth, "Year", each Date.Year([Date]), type number), #"Inserted Year" = Table.AddColumn(InsertedYear, "Year.1", each Date.Year([Date]), type number), #"Removed Columns" = Table.RemoveColumns(#"Inserted Year",{"Year.1"}), #"Inserted Day of Week" = Table.AddColumn(#"Removed Columns", "DayOfWeek", each Date.DayOfWeek([Date]), type number) in #"Inserted Day of Week"