Calendar Lookup Table - Dates Disappearing in 'Table View'

[AdamHurleyEI]

I built a calendar lookup table using the following MCode:

let
// Start and end dates for the calendar table
StartDate = #date(2018, 1, 1),
EndDate = DateTime.Date(DateTime.LocalNow()),

// Number of days in the calendar table
NumberOfDays = Duration.Days(EndDate - StartDate) + 1,

// Generate a list of dates
DatesList = List.Dates(StartDate, NumberOfDays, #duration(1, 0, 0, 0)),

// Convert the list of dates to a table
CalendarTable = Table.FromList(DatesList, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(CalendarTable,{{"Date", type date}}),
#"Inserted Day" = Table.AddColumn(#"Changed Type", "Day", each Date.Day([Date]), Int64.Type),
#"Inserted Day of Week" = Table.AddColumn(#"Inserted Day", "Day of Week", each Date.DayOfWeek([Date]), Int64.Type),
#"Inserted Day Name" = Table.AddColumn(#"Inserted Day of Week", "Day Name", each Date.DayOfWeekName([Date]), type text),
#"Added Conditional Column" = Table.AddColumn(#"Inserted Day Name", "Time of Week", each if [Day of Week] < 5 then "Weekday" else "Weekend"),
#"Inserted Start of Week" = Table.AddColumn(#"Added Conditional Column", "Start of Week", each Date.StartOfWeek([Date]), type date),
#"Inserted Week of Year" = Table.AddColumn(#"Inserted Start of Week", "Week of Year", each Date.WeekOfYear([Date]), Int64.Type),
#"Inserted Quarter" = Table.AddColumn(#"Inserted Week of Year", "Quarter", each Date.QuarterOfYear([Date]), Int64.Type),
#"Added Conditional Column1" = Table.AddColumn(#"Inserted Quarter", "Quarter of Year", each if [Quarter] = 1 then "Q1" else if [Quarter] = 2 then "Q2" else if [Quarter] = 3 then "Q3" else if [Quarter] = 4 then "Q4" else null),
#"Inserted Month" = Table.AddColumn(#"Added Conditional Column1", "Month", each Date.Month([Date]), Int64.Type),
#"Inserted Start of Month" = Table.AddColumn(#"Inserted Month", "Start of Month", each Date.StartOfMonth([Date]), type date),
#"Inserted Month Name" = Table.AddColumn(#"Inserted Start of Month", "Month Name", each Date.MonthName([Date]), type text),
#"Inserted Year" = Table.AddColumn(#"Inserted Month Name", "Year", each Date.Year([Date]), Int64.Type)
in
#"Inserted Year"


and this works as expected in the Power Query:

When I close and apply and look at the table in 'Table View' it is removing half the dates and I have no idea why?

I have no relationships set up:

[Wilson_ · Accepted solution]

Hi Adam,

The loaded date table may just not be sorted. If you search the Date column for "02 January 2018" (or search for 1/2/2018 in the below), does it pop up?

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

P.S. Need a more in-depth consultation for your Power BI data modeling or DAX issues? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.

[AdamHurleyEI]

Good spot:

Of course it was something stupid lol! Thank you

[Wilson_]

Perfect. I love it when it's user error; those are usually the quickest fixes haha