Calendar & Refresh - Can't find source of error

[Anonymous]

Hello, I have a report in which i created a table "Calendar" and a function "getISO8601Week" (code at the end) .

I created both using advanced editor and after that I clicked "Refresh all" on the power query editor, no errors appeared. 

After that i clicked "close & apply" and I get errors for all the rows but I does not specify where the erro is at or what the error is even.

When I check in Power Query, the table that kept the errors ir empty, what does this mean?

Code for the Calendar table:

let 

    P_Today =  DateTime.LocalNow(),
    P_StartDate = #date(2021, 1, 1) //Date.AddYears(#date(Date.Year(P_Today),Date.Month(P_Today),Date.Day(P_Today)),-1)
,
    P_EndDate  = #date(Date.Year(Date.EndOfYear(P_Today)),Date.Month(Date.EndOfYear(P_Today)),Date.Day(Date.EndOfYear(P_Today)))//= #date(Date.Year(P_Today),Date.Month(P_Today),Date.Day(P_Today))
//= #date(Date.Year(P_Today),Date.Month(P_Today),Date.Day(P_Today))
//= #date(Date.Year(P_Today),Date.Month(P_Today),Date.Day(P_Today))
,
    P_Culture = "pt-PT",
    P_FirstDayOfWeek = 5,
    P_IsCarnivalHoliday = true,
    Holiday = if P_Culture = "pt-PT" then "Feriado" else "Holiday",
    Quarter = if P_Culture = "pt-PT" then "T" else "Q",
    Week = if P_Culture = "pt-PT" then "S" else "W",
    Weekend = if P_Culture = "pt-PT" then "Fim de Semana" else "Weekend",
    WorkDay = if P_Culture = "pt-PT" then "Dia Útil" else "Work Day",
    DayCount = Duration.Days(Duration.From(P_EndDate - P_StartDate)) + 1,
    Source = List.Dates(P_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"}}),
    InsertId = Table.AddColumn(RenamedColumns, "DateId", each Date.Year([Date])*10000 + Date.Month([Date])*100 +Date.Day([Date])),
    InsertYear = Table.AddColumn(InsertId, "Year", each Date.Year([Date])),
    InsertQuarter = Table.AddColumn(InsertYear, "Quarter", each Date.QuarterOfYear([Date])),
    InsertSemester = Table.AddColumn(InsertQuarter, "Semester", each if [Quarter] < 3 then 1 else 2),
    InsertMonth = Table.AddColumn(InsertSemester, "Month (#)", each Date.Month([Date])),
    InsertWeek = Table.AddColumn(InsertMonth, "Week", each getISO8601Week([Date])),
    InsertStartOfWeek = Table.AddColumn(InsertWeek, "StartOfWeek", each Date.StartOfWeek([Date],1)),
    InsertStartOfCurrentWeek = Table.AddColumn(InsertStartOfWeek, "StartOfCurrentWeek", each Date.StartOfWeek(Date.From(DateTime.LocalNow()))),
    InsertDay = Table.AddColumn(InsertStartOfCurrentWeek, "Day", each Date.Day([Date])),
    InsertDayMonth = Table.AddColumn(InsertDay, "DayMonth", each [Date], type date),
    InsertMonthName = Table.AddColumn(InsertDayMonth, "Month", each Date.ToText([Date], "MMMM", P_Culture), type text),
    InsertShortMonthName = Table.AddColumn(InsertMonthName, "Month (Short)", each try(Text.Range([#"Month"],0,3)) otherwise [#"Month"]),
    InsertCalendarWeek = Table.AddColumn(InsertShortMonthName, "Week (Year)", each Week & Number.ToText([Week]) & " " & Text.End(Number.ToText([Year]),2)),
    InsertCalendarMonth = Table.AddColumn(InsertCalendarWeek, "Month (Year)", each [#"Month (Short)"] & " " & Text.End(Number.ToText([Year]),2)),
    InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "Quarter (Year)", each Quarter & Number.ToText([Quarter]) & " " & Number.ToText([Year])),    
    InsertCalendarSem = Table.AddColumn(InsertCalendarQtr, "Semester (Year)", each "S" & Number.ToText([Semester]) & " " & Number.ToText([Year])),    
    InsertDayWeek = Table.AddColumn(InsertCalendarSem , "Week Day (#)", each Date.DayOfWeek([Date], P_FirstDayOfWeek ) + 1),
    InsertDayName = Table.AddColumn(InsertDayWeek, "Week Day", each Date.ToText([Date], "dddd", P_Culture), type text),
    InsertWeekYear = Table.AddColumn(InsertDayName, "WeekYearId", each [Year] * 100 + [Week]),
    InsertMonthYear = Table.AddColumn(InsertWeekYear, "MonthYearId", each [Year] *100 + [#"Month (#)"]),
    //InsertStartWeek = Table.AddColumn(InsertWeekYear , "Start of Week", each Date.StartOfWeek([Date], P_FirstDayOfWeek), type date),
    //InsertEndWeek = Table.AddColumn(InsertStartWeek , "End of Week", each Date.EndOfWeek([Date], P_FirstDayOfWeek), type date),
    InsertQuarterYear = Table.AddColumn(InsertMonthYear, "QuarterYearId", each [Year] * 100 + [Quarter]),
    InsertSemesterYear = Table.AddColumn(InsertQuarterYear, "SemesterYearId", each [Year] * 100 + [Semester]),
    #"Capitalized Each Word" = Table.TransformColumns(InsertSemesterYear,{{"Month", Text.Proper}, {"Month (Short)", Text.Proper}, {"Month (Year)", Text.Proper}, {"Week Day", Text.Proper}}),
    #"Relative (Year)" = Table.AddColumn(#"Capitalized Each Word", "Year (Relative)", each [Year] - Date.Year(P_Today)),
    #"Relative (Month)" = Table.AddColumn(#"Relative (Year)", "Month (Relative)", each [#"Year (Relative)"] * 12 + ([#"Month (#)"] - Date.Month(P_Today))),
    #"Relative (Week)" = Table.AddColumn(#"Relative (Month)", "Week (Relative)", each Duration.Days([StartOfWeek]-[StartOfCurrentWeek])/7, Int64.Type),
    #"Relative (Day)" = Table.AddColumn(#"Relative (Week)", "Day (Relative)", each Duration.TotalDays([Date] - DateTime.Date(P_Today))),
    #"Date (Yesterday)" = Table.AddColumn(#"Relative (Day)", "Date (Yesterday)", each if [Date] = Date.AddDays(Date.From(DateTime.LocalNow()),-1)
 then 1 else 0),
    #"Month (Yesterday)" = Table.AddColumn(#"Date (Yesterday)", "Month (Yesterday)", each if [MonthYearId] = Date.Year(Date.AddDays(Date.From(DateTime.LocalNow()),-1)) *100 + Date.Month(
    Date.AddDays(Date.From(DateTime.LocalNow()),-1))
    then 1 else 0),
    #"WeekYear (Yesterday)" = Table.AddColumn(#"Month (Yesterday)", "WeekYear (Yesterday)", each if [WeekYearId] = Date.Year(Date.AddDays(Date.From(DateTime.LocalNow()),-1)) * 100 + getISO8601Week(Date.AddDays(Date.From(DateTime.LocalNow()),-1)) then 1 else 0),
    #"Sorted Rows" = Table.Sort(#"WeekYear (Yesterday)",{{"DateId", Order.Ascending}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Sorted Rows",{{"Date", type date}, {"Quarter", Int64.Type}, {"Year", Int64.Type}, {"Semester", Int64.Type}, {"Month (#)", Int64.Type}, {"Week", Int64.Type}, {"Day", Int64.Type}, {"Week Day (#)", Int64.Type}, {"WeekYearId", Int64.Type}, {"MonthYearId", Int64.Type}, {"QuarterYearId", Int64.Type}, {"SemesterYearId", Int64.Type}, {"Year (Relative)", Int64.Type}, {"Month (Relative)", Int64.Type}, {"Day (Relative)", Int64.Type}, {"WeekYear (Yesterday)", Int64.Type}, {"Month (Yesterday)", Int64.Type}, {"Date (Yesterday)", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Date", "Manufacturing Date"}}),
    #"Added Custom1" = Table.AddColumn(#"Renamed Columns", "All Calendar", each 1),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "FirstDayofMonth", each if [Manufacturing Date] = Date.StartOfMonth([Manufacturing Date]) then true else false)
in
    #"Added Custom2"

Code for the iso function

(someDate as date) =>
        let
            getDayOfWeek = (d as date) =>
                let
                    result = 1 + Date.DayOfWeek(d, Day.Monday)
                in
                    result,

            getNaiveWeek = (inDate as date) =>
                let
                    // monday = 1, sunday = 7
                    weekday = getDayOfWeek(inDate),

                    weekdayOfJan4th = getDayOfWeek(#date(Date.Year(inDate), 1, 4)),

                    ordinal = Date.DayOfYear(inDate),

                    naiveWeek = Number.RoundDown(
                        (ordinal - weekday + 10) / 7
                    )
                in
                    naiveWeek,

            thisYear = Date.Year(someDate),

            priorYear = thisYear - 1,

            nwn = getNaiveWeek(someDate),

            lastWeekOfPriorYear =
                getNaiveWeek(#date(priorYear, 12, 28)),

            // http://stackoverflow.com/a/34092382/2014893
            lastWeekOfThisYear =
                getNaiveWeek(#date(thisYear, 12, 28)),

            weekYear =
                if
                    nwn < 1
                then
                    priorYear
                else
                    if
                        nwn > lastWeekOfThisYear
                    then
                        thisYear + 1
                    else
                        thisYear,

            weekNumber =
                if
                    nwn < 1
                then
                    lastWeekOfPriorYear
                else
                    if
                        nwn > lastWeekOfThisYear
                    then
                        1
                    else
                        nwn,

            week_dateString =
                Text.PadStart(
                    Text.From(
                        Number.RoundDown(weekNumber)
                    ),
                    2,
                    "0"
                )
        in
            Number.FromText(week_dateString)

edit: The report published in power bi service refreshes and works fine. The error in refresh appears only in desktop version

[ghoshabhijeet]

@Anonymous  Thanks for your reply. You can add the file on any cloud drive and share the link here. Thanks!

[Anonymous · Accepted solution]

Thank you but I've found the solution meanwhile.. although it doesn't make total sense. I just changed the regional settings to English from the US. (I had as Portuguese (Portugal)) and now I don't get the error. 

[ghoshabhijeet]

@Anonymous  Thanks for your query. Please share the pbix file.

[Anonymous]

I can't add files here but it's literally just a pbix with those two queries