Formula Not Working After first ~1450 Rows

yaney · April 17, 2020, 2:46am

Hello, first time post!

I’ve used Power Query to create a customized 445 calendar. Similar to the functionality that EnterpriseDNA offers in the DateDimension table, I’ve used the logic to created a function for a customized calendar, with a start and end date.

When I input a start and end date with only 2-4 years, the query works fine without any errors. However, I created the start and end date with inputs of 1/1/2016 - 12/31/2046 and after about 11/22/2022, the formula/logic no longer works and returns a value of “0” while every other row in this column worked flawlessly.

The formula (Power Query M) where I have an error is:

AddedClose = Table.AddColumn(AddedDate, “Close”,
each if Date.DayOfWeek([Date]) = 1 then Date.AddDays([1st of Year],25)
else if Date.DayOfWeek([Date]) = 2 then Date.AddDays([1st of Year],24)
else if Date.DayOfWeek([Date]) = 3 then Date.AddDays([1st of Year],23)
else if Date.DayOfWeek([Date]) = 4 then Date.AddDays([1st of Year],29)
else if Date.DayOfWeek([Date]) = 5 then Date.AddDays([1st of Year],28)
else if Date.DayOfWeek([Date]) = 6 then Date.AddDays([1st of Year],27)
else if Date.DayOfWeek([Date]) = 7 then Date.AddDays([1st of Year],26)
else 0, type date)

The preceding line where “Date” is created is:

AddedDate = Table.AddColumn(InsertDayName, “Date”, each Date.StartOfYear([DateOriginal]), type date),

Again, no syntax errors and when I only have a couple years and load (Close & Apply), I have absolutely no errors. However, after the 4th year, I receive a returned value of “0” instead of the date for all years afterwards.

Hope this makes sense! I am curious if there is a limitation within PowerBI. All of the other fields seem to work fine even though the years expand to 2046.

Thanks for any thoughts!

ctvv3010 · April 17, 2020, 7:45am

I recommend simplifying your formula using SWITCH logic. It will make your syntax simpler.

Sometimes you can try changing the way you write formula and it’ll work. It occurred to me several times.

Melissa · April 17, 2020, 9:18am

Hi @yaney,

Welcome to the Forum!

Now I’m not sure if this will help but have you tried adding a Buffer step? From what I understand your M code it should look something like this:

AddedDate = Table.AddColumn(InsertDayName, "Date", each Date.StartOfYear([DateOriginal]), type date),
AddBufferStep = Table.Buffer( AddedDate ),
AddedClose = Table.AddColumn(AddBufferStep, "Close",
each if Date.DayOfWeek([Date]) = 1 then Date.AddDays([1st of Year],25)
else if Date.DayOfWeek([Date]) = 2 then Date.AddDays([1st of Year],24)
else if Date.DayOfWeek([Date]) = 3 then Date.AddDays([1st of Year],23)
else if Date.DayOfWeek([Date]) = 4 then Date.AddDays([1st of Year],29)
else if Date.DayOfWeek([Date]) = 5 then Date.AddDays([1st of Year],28)
else if Date.DayOfWeek([Date]) = 6 then Date.AddDays([1st of Year],27)
else if Date.DayOfWeek([Date]) = 7 then Date.AddDays([1st of Year],26)
else 0, type date)

.
I hope this is helpful.

yaney · April 17, 2020, 11:34am

Thank you so much for the suggestion! Unfortunately, I’m using M and not DAX, so don’t think SWITCH functionality will work. I’ll try to think about whether I can simplify the formula as well.

Melissa · April 17, 2020, 2:20pm

Have you tested adding a Table.Buffer step to your Query?

yaney · April 17, 2020, 5:42pm

Hi @Melissa, thanks so much for the response. I was not aware of the Table.Buffer functionality.

A quick update: After incorporating Table.Buffer between the two, the formula works for the first 1400 rows, doesn’t work for the next year (i.e. 365 rows), and then works for the remainder of the rows. It’s strange. I am trying to tweak the M-code with the Table.Buffer and will provide another update but wanted to provide you the status as of now. powerBIqueryeditor_Mquery_withbuffer_365dayserror

Attached is an image that shows you that everything worked fine except for one year.

Melissa · April 17, 2020, 6:40pm

Two questions

Why does your Date table need to run until the end of 2046?
Can you provide the full M function you are using, maybe I can assist.

yaney · April 17, 2020, 6:48pm

Hey @Melissa,

I asked the same questions but I guess some people feel the need to create things that are way far into the future. Completely not right (I know!) and there may be something suspicious going on, but there are actual documents that some individuals created that date to 2046.

I’ll share with you the original M-script (rather than the one where I’ve added the Table.Buffer).

Please don’t judge me for horrible scripting! I’m new to M and had to figure this out in the last few weeks. It’s a custom 445 calendar with weeks ending on Fridays (new week begins Saturday) and with 1/1 and 12/31 as being the start and end of each year.

let fnDateTable = (StartDate as date, EndDate as date) as table =>
let
DayCount = Duration.Days(Duration.From(Date.AddDays(EndDate,1) - StartDate)),
Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
ChangedType = Table.TransformColumnTypes(TableFromList,{{“Column1”, type date}}),
RenamedColumns = Table.RenameColumns(ChangedType,{{“Column1”, “Date”}}),
InsertYear = Table.AddColumn(RenamedColumns, “Year”, each Date.Year([Date])),
InsertCalendarMonth = Table.AddColumn(InsertYear, “CalendarMonthofYear”, each Date.Month([Date])),
InsertCalendarDay = Table.AddColumn(InsertCalendarMonth, “CalendarDayofMonth”, each Date.Day([Date])),
InsertDateInt = Table.AddColumn(InsertCalendarDay, “DateInt”, each [Year] * 10000 + [CalendarMonthofYear] * 100 + [CalendarDayofMonth]),
InsertDayWeek = Table.AddColumn(InsertDateInt, “DayInWeek”, each Date.DayOfWeek([Date])),
InsertDayName = Table.AddColumn(InsertDayWeek, “DayOfWeekName”, each Date.ToText([Date], “dddd”)),
Added1stofYear = Table.AddColumn(InsertDayName, “1st of Year”, each Date.StartOfYear([Date]), type date),
AddedP01Close = Table.AddColumn(Added1stofYear, “P01 Close”,
each if Date.DayOfWeek([1st of Year]) = 1 then Date.AddDays([1st of Year],25)
else if Date.DayOfWeek([1st of Year]) = 2 then Date.AddDays([1st of Year],24)
else if Date.DayOfWeek([1st of Year]) = 3 then Date.AddDays([1st of Year],23)
else if Date.DayOfWeek([1st of Year]) = 4 then Date.AddDays([1st of Year],29)
else if Date.DayOfWeek([1st of Year]) = 5 then Date.AddDays([1st of Year],28)
else if Date.DayOfWeek([1st of Year]) = 6 then Date.AddDays([1st of Year],27)
else if Date.DayOfWeek([1st of Year]) = 7 then Date.AddDays([1st of Year],26)
else 0, type date),
AddedP02Close = Table.AddColumn(AddedP01Close, “P02 Close”, each Date.AddDays([P01 Close],28), type date),
AddedP03Close = Table.AddColumn(AddedP02Close, “P03 Close”, each Date.AddDays([P02 Close],35), type date),
AddedP04Close = Table.AddColumn(AddedP03Close, “P04 Close”, each Date.AddDays([P03 Close],28), type date),
AddedP05Close = Table.AddColumn(AddedP04Close, “P05 Close”, each Date.AddDays([P04 Close],28), type date),
AddedP06Close = Table.AddColumn(AddedP05Close, “P06 Close”, each Date.AddDays([P05 Close],35), type date),
AddedP07Close = Table.AddColumn(AddedP06Close, “P07 Close”, each Date.AddDays([P06 Close],28), type date),
AddedP08Close = Table.AddColumn(AddedP07Close, “P08 Close”, each Date.AddDays([P07 Close],28), type date),
AddedP09Close = Table.AddColumn(AddedP08Close, “P09 Close”, each Date.AddDays([P08 Close],35), type date),
AddedP10Close = Table.AddColumn(AddedP09Close, “P10 Close”, each Date.AddDays([P09 Close],28), type date),
AddedP11Close = Table.AddColumn(AddedP10Close, “P11 Close”, each Date.AddDays([P10 Close],28), type date),
AddedP12Close = Table.AddColumn(AddedP11Close, “P12 Close”, each Date.EndOfYear([Date]), type date),
AddedCurrentPeriodClose = Table.AddColumn(AddedP12Close, “Current Period Close”, each if [Date] <=[P01 Close] then [P01 Close]
else if [Date] <=[P02 Close] and [Date] > [P01 Close] then [P02 Close]
else if [Date] <=[P03 Close] and [Date] > [P02 Close] then [P03 Close]
else if [Date] <=[P04 Close] and [Date] > [P03 Close] then [P04 Close]
else if [Date] <=[P05 Close] and [Date] > [P04 Close] then [P05 Close]
else if [Date] <=[P06 Close] and [Date] > [P05 Close] then [P06 Close]
else if [Date] <=[P07 Close] and [Date] > [P06 Close] then [P07 Close]
else if [Date] <=[P08 Close] and [Date] > [P07 Close] then [P08 Close]
else if [Date] <=[P09 Close] and [Date] > [P08 Close] then [P09 Close]
else if [Date] <=[P10 Close] and [Date] > [P09 Close] then [P10 Close]
else if [Date] <=[P11 Close] and [Date] > [P10 Close] then [P11 Close]
else if [Date] <=[P12 Close] and [Date] > [P11 Close] then [P12 Close]
else 0, type date),
InsertPostingPeriod = Table.AddColumn(AddedCurrentPeriodClose, “Posting Period”, each if [Date] <=[P01 Close] then “01”
else if [Date] <=[P02 Close] and [Date] > [P01 Close] then “02”
else if [Date] <=[P03 Close] and [Date] > [P02 Close] then “03”
else if [Date] <=[P04 Close] and [Date] > [P03 Close] then “04”
else if [Date] <=[P05 Close] and [Date] > [P04 Close] then “05”
else if [Date] <=[P06 Close] and [Date] > [P05 Close] then “06”
else if [Date] <=[P07 Close] and [Date] > [P06 Close] then “07”
else if [Date] <=[P08 Close] and [Date] > [P07 Close] then “08”
else if [Date] <=[P09 Close] and [Date] > [P08 Close] then “09”
else if [Date] <=[P10 Close] and [Date] > [P09 Close] then “10”
else if [Date] <=[P11 Close] and [Date] > [P10 Close] then “11”
else if [Date] <=[P12 Close] and [Date] > [P11 Close] then “12”
else 0),
InsertReportingDate = Table.AddColumn(InsertPostingPeriod, “Reporting Date”, each [Posting Period]&"/"&“01”&"/"& Text.From(Date.Year([Date]))),
ChangedType1 = Table.TransformColumnTypes(InsertReportingDate,{{“Reporting Date”, type date}}),
RemovedColumns = Table.RemoveColumns(InsertReportingDate,{“1st of Year”, “P01 Close”, “P02 Close”, “P03 Close”, “P04 Close”, “P05 Close”, “P06 Close”, “P07 Close”, “P08 Close”, “P09 Close”, “P10 Close”, “P11 Close”, “P12 Close”}),
//InsertCloseYear = Table.AddColumn(RemovedColumns, “CloseYear”, each Date.Year([Current Period Close]),type text),
InsertYearNumber = Table.AddColumn(RemovedColumns, “YearNumber”, each Date.Year([Current Period Close])),
InsertShortYear = Table.AddColumn(InsertYearNumber, “ShortYear”, each Text.End(Text.From(Date.Year([Current Period Close])),2)),
InsertQuarterInt = Table.AddColumn(InsertShortYear, “QuarterInt”, each if Date.Month([Current Period Close]) < 4 then 1
else if Date.Month([Current Period Close]) > 3 and Date.Month([Current Period Close]) <7 then 2
else if Date.Month([Current Period Close]) >6 and Date.Month([Current Period Close]) < 10 then 3
else 4),
InsertQuarterofYear = Table.AddColumn(InsertQuarterInt, “QuarterofYear”, each if Date.Month([Current Period Close]) = 1 then “Q1”
else if Date.Month([Current Period Close]) = 2 then “Q1”
else if Date.Month([Current Period Close]) = 3 then “Q1”
else if Date.Month([Current Period Close]) = 4 then “Q2”
else if Date.Month([Current Period Close]) = 5 then “Q2”
else if Date.Month([Current Period Close]) = 6 then “Q2”
else if Date.Month([Current Period Close]) = 7 then “Q3”
else if Date.Month([Current Period Close]) = 8 then “Q3”
else if Date.Month([Current Period Close]) = 9 then “Q3”
else “Q4”),
InsertQuarterYear = Table.AddColumn(InsertQuarterofYear, “QuarterYear”, each [QuarterofYear] & “-” & Text.From(Date.Year([Current Period Close]))),
InsertQuarterYearInt = Table.AddColumn(InsertQuarterYear, “QuarterYearInt”, each [YearNumber] *10000 + [QuarterInt] * 100),
InsertMonthofYear = Table.AddColumn(InsertQuarterYearInt, “MonthCloseofYear”, each Date.Month([Current Period Close])),
InsertMonthYearInt = Table.AddColumn(InsertMonthofYear, “MonthCloseYearInt”, each [YearNumber] * 10000 + [MonthCloseofYear] *100),
InsertMonthName = Table.AddColumn(InsertMonthYearInt,“MonthCloseName”, each Date.ToText([Current Period Close], “MMMM”)),
InsertYearMonth =Table.AddColumn(InsertMonthName, “YearMonthClose”, each Text.From(Date.Year([Current Period Close])) & “/” & Text.From(Date.Month([Current Period Close]))),
InsertWeekCal = Table.AddColumn(InsertYearMonth, “WeekEndStaging”, each if [DayOfWeekName] = “Saturday” then 6
else if [DayOfWeekName] = “Sunday” then 5
else if [DayOfWeekName] = “Monday” then 4
else if [DayOfWeekName] = “Tuesday” then 3
else if [DayOfWeekName] = “Wednesday” then 2
else if [DayOfWeekName] = “Thursday” then 1
else 0),
InsertWeekEnding1 = Table.AddColumn(InsertWeekCal, “WeekEnding1”, each Date.AddDays([Date], [WeekEndStaging])),
InsertWeekEnding2 = Table.AddColumn(InsertWeekEnding1,“WeekEnding”,each if Duration.Days(#date(Number.From([Year]),12,31) - [Date]) < 6 and Date.Year([WeekEnding1]) <> [YearNumber] then #date(Number.From([Year]),12,31) else [WeekEnding1], type date),
InsertWeekEndingNumber = Table.AddColumn(InsertWeekEnding2, “WeekEndingNumber”, each (Number.From(Date.Year([WeekEnding])) * 10000) + (Number.From(Date.Month([WeekEnding])) * 100) + Number.From(Date.Day([WeekEnding])), Int64.Type),
InsertWeekofYear = Table.AddColumn(InsertWeekEndingNumber,“WeekofYear”, each Date.WeekOfYear([Date], Day.Saturday)),
RemovedColumns1 = Table.RemoveColumns(InsertWeekofYear,{“WeekEndStaging”, “WeekEnding1”}),
ChangedType2 = Table.TransformColumnTypes(RemovedColumns1,{{“CalendarDayofMonth”, Int64.Type},{“DateInt”, Int64.Type},{“MonthCloseYearInt”, Int64.Type}, {“QuarterYearInt”, Int64.Type}, {“QuarterInt”, Int64.Type}, {“YearNumber”, Int64.Type}})
in
ChangedType2
in
fnDateTable

Melissa · April 17, 2020, 7:05pm

Thanks @yaney I’ll take a look and will get back to you.

yaney · April 17, 2020, 8:11pm

Thanks @Melissa!

I did try to simplify the formula by creating some complex & dynamic M code, but it was taking me way too long and another colleague suggested I just build it step-by-step. So this is where I ended.

Melissa · April 17, 2020, 8:42pm

Hi @yaney

So here is an issue. Power Query is zero based the Date.DayOfWeek([Date]) column in your table (1), therefore returns values from 0-6 (2) but in your logic you check for values running from 1-7 (3)
That means that Date.DayOfWeek([1st of Year]) = 1 referres to a Tuesday AND Date.DayOfWeek([1st of Year]) = 7 will never pass. So I expect that the Year with 0 values starts on a Monday…

yaney · April 17, 2020, 8:55pm

Hi @Melissa, I completely understand when you stated that Power Query is a zero-based for Date.DayofWeke([Date]). My original query actually had 0-6 as the references; however, my actual results came out incorrect! While I expected 0 = Monday, 1 = Tuesday, etc., the returning value (and day of the week text) was completely different than the expected 0 = Monday, etc. Because of that, I changed my logic and made the 1-7 reference because 0-6 didn’t work. It’s strange because when I changed it to represent 1-7, the logic and actual dates worked out perfectly.

Let me try and update these again back to zero-based and see if the values I’m looking for come out properly.

yaney · April 17, 2020, 9:23pm

Hi @Melissa,

I ended up changing everything so that it references 0-6 and removed my 1-7. This resulted in 0-6 changing completely. You can see that your image displays 0=Monday (which is what I expected as well, but remembering that I used 1-7 convention); however, in this image, you can see that now 0=Sunday.
powerBIqueryeditor_Mquery_dayofweek0-6

It seems the errors that I was running into all disappeared, so I do not have any more errors having removed the references from 1-7 to 0-6 and also changing all Date.AddDays to the requirements.

THANK YOU so much!
WoW - you ARE an expert. Thanks so much for that. I honestly, don’t know how the 1-7 references worked. Strange. Thanks again so much!

yaney · April 17, 2020, 9:24pm

@Melissa, hopefully I marked the solution as being solved properly. If not, let me know! THANKS again!

Melissa · April 17, 2020, 9:28pm

Excellent! Thanks for that.

Nick_M · April 17, 2020, 11:20pm

You can actually set what the first day of the week is using Day.“DayName”

awesome stuff @Melissa!

yaney · April 18, 2020, 12:06am

Thanks @Nick_M for the reminder!