Calendar Function not working with new version of power BI

[vani]

 Hello ,

I have a function that calculates a calendar.

It was working well, but i've installed the newest version and suddenly it stoped working.

Anyone with this problem ? 

Thanks

[ankitpatira]

@vani Can you provide more details on the problem and function.

[vani]

Hi @ankitpatira

The function to produce the calendar is the following one:

//Create Date Dimension
(StartDate as date, EndDate as date)=>
let
//Capture the date range from the parameters
StartDate = #date(Date.Year(StartDate), Date.Month(StartDate),
Date.Day(StartDate)),
EndDate = #date(Date.Year(EndDate), Date.Month(EndDate),
Date.Day(EndDate)),
//Get the number of dates that will be required for the table
GetDateCount = Duration.Days(EndDate - StartDate)+1,
//Take the count of dates and turn it into a list of dates
GetDateList = List.Dates(StartDate, GetDateCount,
#duration(1,0,0,0)),
//Convert the list into a table
DateListToTable = Table.FromList(GetDateList,
Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
//Create various date attributes from the date column
//Add Year Column
YearNumber = Table.AddColumn(DateListToTable, "Ano",
each Date.Year([Date])),
//Add Quarter Column
QuarterNumber = Table.AddColumn(YearNumber , "Trimestre",
each "T" & Number.ToText(Date.QuarterOfYear([Date]))),
//Add Week Number Column
WeekNumber= Table.AddColumn(QuarterNumber , "Semana",
each Date.WeekOfYear([Date])),
//Add Month Number Column
MonthNumber = Table.AddColumn(WeekNumber, "Nr Mês",
each Date.Month([Date])),
//Add Month Name Column
MonthName = Table.AddColumn(MonthNumber , "Mês",
each Date.ToText([Date],"MMMM")),
//Add Day of Week Column
DayOfWeek = Table.AddColumn(MonthName , "Dia da semana",
each Date.ToText([Date],"dddd")),
//Add Date as Integer Column
DateAsInteger = Table.AddColumn(DayOfWeek , "Ano/Mês/Dia",
each Date.ToText([Date],"YYYYMMDD")),
//Add Year Month Column
YearMonth= Table.AddColumn(DateAsInteger , "Ano/Mês",
each Number.ToText(Date.Year([Date])) &"/"& Date.ToText([Date],"MMM")),
//Add Day Integer Column
Day = Table.AddColumn(YearMonth, "Dia",
each Date.Day([Date]))

in
Day

It was working well on all previous Power BI desktop versions, but yesterday, i've installed the newest version, and suddenly it stoped working.

I think it is a bug with this new PBI version, because when i call the function " manually " it works ( when i click invoke function).

 But when i call it with parameters PBI gives me this error:

Unexpected error: Operation is not valid due to the current state of the object.
Details:
Microsoft.Mashup.Evaluator.Interface.ErrorException: Operation is not valid due to the current state of the object. ---> Microsoft.Mashup.Evaluator.Interface.ErrorException: Operation is not valid due to the current state of the object. ---> Microsoft.Mashup.Evaluator.Interface.ErrorException: Operation is not valid due to the current state of the object. ---> Microsoft.Mashup.Evaluator.Interface.ErrorException: Operation is not valid due to the current state of the object. ---> Microsoft.Mashup.Evaluator.Interface.ErrorException: Operation is not valid due to the current state of the object. ---> System.InvalidOperationException: Operation is not valid due to the current state of the object. ---> System.InvalidOperationException: Operation is not valid due to the current state of the object.

Hope you @ankitpatira could help me 

[v-micsh-msft]

Hi vani,

Could you please share more details about how you “call it with parameters” here?

I tested this from my side with the function, and it works manually also.

For parameter, I did the following test:

1. Create two parameters named with StartDate and EndDate, with both data type set to Date;
2. Click Enter Data to generate a new table, open its advanced Editor and replace the code with the following:    Source = Function-table-name(StartDate, EndDate)   Source
3. in
4. let

And it also works at my side as below.

Please share more details about the steps you tried which ended up with the errors, we will take a further investigation on that part.

Regards

[vani · Accepted solution]

Hello @v-micsh-msft

Well, the code that is not working is the following one:

let
Source = Gastos,
#"Removed Columns" = Table.RemoveColumns(Source,{"Descricao", "Valor"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", let earliest = List.Min(#"Removed Columns"[Data]) in each [Data] = earliest),
#"Grouped Rows" = Table.Group(#"Filtered Rows", {"Data"}, {{"Count", each Table.RowCount(_), type number}}),
#"Removed Columns1" = Table.RemoveColumns(#"Grouped Rows",{"Count"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns1", "calendar", each MakeCalendar(([Data]),DateTime.Date(DateTime.LocalNow()))),
#"Expanded calendar" = Table.ExpandTableColumn(#"Added Custom", "calendar", {"Date", "Ano", "Trimestre", "Semana", "Nr Mês", "Mês", "Dia da semana", "Ano/Mês/Dia", "Ano/Mês", "Dia"}, {"Date", "Ano", "Trimestre", "Semana", "Nr Mês", "Mês", "Dia da semana", "Ano/Mês/Dia", "Ano/Mês", "Dia"}),
#"Removed Columns2" = Table.RemoveColumns(#"Expanded calendar",{"Data"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns2",{{"Dia", Int64.Type}, {"Ano/Mês/Dia", Int64.Type}, {"Nr Mês", Int64.Type}, {"Semana", Int64.Type}, {"Ano", Int64.Type}, {"Date", type date}})
in
#"Changed Type"

I've founded the error ( red marked ) , for some reason ( on other version was working fine ) PBI is not accpeting the [Data] parameter(it is Date type) that is the earliest data of my sales table.

After some time, i made this change:

DateTime.Date([Data])

And now, it is working fine