Expressio.Error help when Pivoting columns

[kbachova]

Hello community, I am having issues with the error in the Query Editor:

Expression.Error: We cannot convert the value null to type Logical.

Details:

Value=

Type=[Type]

My suspicion is that after step "Added Custom11" the query starts to break. The weird thing is that it worked well until 5/2/2020 and since then I am getting this error (I did not touch the report and the reason I found out is that my scheduled refreshes started to fail).

Is anyone else experiencing similar problem and knows how to fix it? Or is this a bug that has been reported with a new release and I am just unaware of an easy fix?

Please help!

Thank you!

Here is my query:

let

Source = SharePoint.Tables("***********", [ApiVersion = 15]),

#"$$$$$$$$$$$$$$$$$$$" = Source{[Id="$$$$$$$$$$$$$$$"]}[Items],

#"Renamed Columns" = Table.RenameColumns(#"$$$$$$$$$$$$$$$$$",{{"ID", "ID.1"}}),

#"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns",{"Id", "Title", "EmployeeName", "StartDateTime", "DateTimeEnd", "Location", "Client"}),

#"Changed Type1" = Table.TransformColumnTypes(#"Removed Other Columns",{ {"StartDateTime", type datetimezone}, {"DateTimeEnd", type datetimezone}, {"Id", Int64.Type}}),

#"Added Custom4" = Table.AddColumn(#"Changed Type1", "Custom", each DateTimeZone.SwitchZone([StartDateTime], +9)),

#"Removed Columns2" = Table.RemoveColumns(#"Added Custom4",{"StartDateTime"}),

#"Renamed Columns4" = Table.RenameColumns(#"Removed Columns2",{{"Custom", "StartDateTime"}}),

#"Changed Type11" = Table.TransformColumnTypes(#"Renamed Columns4",{{"StartDateTime", type datetimezone}, {"DateTimeEnd", type datetimezone}}),

#"Added Custom5" = Table.AddColumn(#"Changed Type11", "Custom", each DateTimeZone.SwitchZone([DateTimeEnd], +9)),

#"Removed Columns3" = Table.RemoveColumns(#"Added Custom5",{"DateTimeEnd"}),

#"Renamed Columns5" = Table.RenameColumns(#"Removed Columns3",{{"Custom", "DateTimeEnd"}}),

#"Changed Type10" = Table.TransformColumnTypes(#"Renamed Columns5",{{"DateTimeEnd", type datetimezone}}),

#"Inserted Date" = Table.AddColumn(#"Changed Type10", "Date", each DateTime.Date([StartDateTime]), type date),

#"Inserted Time" = Table.AddColumn(#"Inserted Date", "S_Time", each DateTime.Time([StartDateTime]), type time),

#"Inserted Minute" = Table.AddColumn(#"Inserted Time", "S_Minute", each Time.Minute([StartDateTime]), Int64.Type),

#"Changed Type" = Table.TransformColumnTypes(#"Inserted Minute",{{"S_Minute", type text}}),

#"Split Column by Position" = Table.SplitColumn(#"Changed Type", "S_Minute", Splitter.SplitTextByPositions({0, 1}, true), {"S_Minute.1", "S_Minute.2"}),

#"Removed Columns" = Table.RemoveColumns(#"Split Column by Position",{"S_Minute.1"}),

#"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"S_Minute.2", "S_Minute"}}),

#"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns1",{{"S_Minute", Int64.Type}}),

#"Added Conditional Column" = Table.AddColumn(#"Changed Type2", "S_Minute_rounding", each if [S_Minute] > 0 and [S_Minute]<10 then (10-[S_Minute]) else 0),

#"Changed Type3" = Table.TransformColumnTypes(#"Added Conditional Column",{{"S_Minute_rounding", Int64.Type}}),

#"Added Conditional Column1" = Table.AddColumn(#"Changed Type3", "Custom1", each if [S_Minute_rounding] > 0 then [S_Time] + #duration (0,0,[S_Minute_rounding],0) else [S_Time]),

#"Changed Type4" = Table.TransformColumnTypes(#"Added Conditional Column1",{{"Custom1", type time}}),

#"Renamed Columns2" = Table.RenameColumns(#"Changed Type4",{{"Custom1", "S_Time_rounded"}}),

#"Added Custom9" = Table.AddColumn(#"Renamed Columns2", "Custom", each #time(Time.Hour([S_Time_rounded]),Time.Minute([S_Time_rounded]),0), type time),

#"Removed Columns5" = Table.RemoveColumns(#"Added Custom9",{"S_Time_rounded"}),

#"Renamed Columns7" = Table.RenameColumns(#"Removed Columns5",{{"Custom", "S_Time_rounded"}}),

#"Inserted Time1" = Table.AddColumn(#"Renamed Columns7", "E_Time", each DateTime.Time([DateTimeEnd]), type time),

#"Inserted Minute1" = Table.AddColumn(#"Inserted Time1", "E_Minute", each Time.Minute([E_Time]), Int64.Type),

#"Changed Type5" = Table.TransformColumnTypes(#"Inserted Minute1",{{"E_Minute", type text}}),

#"Split Column by Position1" = Table.SplitColumn(#"Changed Type5", "E_Minute", Splitter.SplitTextByPositions({0, 1}, true), {"E_Minute.1", "E_Minute.2"}),

#"Changed Type6" = Table.TransformColumnTypes(#"Split Column by Position1",{{"E_Minute.1", Int64.Type}, {"E_Minute.2", Int64.Type}}),

#"Removed Columns1" = Table.RemoveColumns(#"Changed Type6",{"E_Minute.1"}),

#"Renamed Columns3" = Table.RenameColumns(#"Removed Columns1",{{"E_Minute.2", "E_Minute"}}),

#"Added Custom" = Table.AddColumn(#"Renamed Columns3", "E_Minute_rounding", each if [E_Minute] > 0 and [E_Minute]<10 then 10 else 0),

#"Changed Type7" = Table.TransformColumnTypes(#"Added Custom",{{"E_Minute_rounding", Int64.Type}}),

#"Added Custom1" = Table.AddColumn(#"Changed Type7", "E_Time_rounded", each if [E_Minute_rounding] = 10 then [E_Time] - #duration (0,0,[E_Minute],0) else [E_Time]),

#"Changed Type8" = Table.TransformColumnTypes(#"Added Custom1",{{"E_Time_rounded", type time}}),

#"Added Custom3" = Table.AddColumn(#"Changed Type8", "Custom", each #time(Time.Hour([E_Time_rounded]),Time.Minute([E_Time_rounded]),0) , type time),

#"Removed Columns4" = Table.RemoveColumns(#"Added Custom3",{"E_Time_rounded"}),

#"Renamed Columns6" = Table.RenameColumns(#"Removed Columns4",{{"Custom", "E_Time_rounded"}}),

#"Inserted Merged Date and Time" = Table.AddColumn(#"Renamed Columns6", "S_datetime_rounded", each [Date] & [S_Time_rounded], type datetime),

#"Inserted Date1" = Table.AddColumn(#"Inserted Merged Date and Time", "Date.1", each DateTime.Date([DateTimeEnd]), type date),

#"Added Custom Column" = Table.AddColumn(#"Inserted Date1", "E_datetime_rounded", each [Date.1] & [E_Time_rounded]),

#"Changed Type9" = Table.TransformColumnTypes(#"Added Custom Column",{{"E_datetime_rounded", type datetime}}),

#"Removed Other Columns1" = Table.SelectColumns(#"Changed Type9",{"Id", "Title", "EmployeeName", "DateTimeEnd", "Location", "Client", "StartDateTime", "Date", "S_datetime_rounded", "E_datetime_rounded"}),

#"Added Custom Column1" = Table.AddColumn(#"Removed Other Columns1", "merge_id", each let splitEmployeeName = Splitter.SplitTextByDelimiter(" ", QuoteStyle.None)([EmployeeName]) in Text.Combine({Text.Combine(splitEmployeeName), Date.ToText([Date], "dd"), "0", Date.ToText([Date], "%M"), Text.Middle(Date.ToText([Date], "yyyy"), 2)}), type text),

#"Merged Queries" = Table.NestedJoin(#"Added Custom Column1", {"merge_id"}, #"Staff_Planning (2)", {"merge_id"}, "Staff_Planning (2)", JoinKind.LeftOuter),

#"Expanded Staff_Planning (2)" = Table.ExpandTableColumn(#"Merged Queries", "Staff_Planning (2)", {"START", "END"}, {"START", "END"}),

#"Added Custom2" = Table.AddColumn(#"Expanded Staff_Planning (2)", "DURATION_rounded_both", each [E_datetime_rounded]-[S_datetime_rounded]),

#"Added Custom6" = Table.AddColumn(#"Added Custom2", "DURATION_rounded_start", each (DateTimeZone.RemoveZone([DateTimeEnd])-[S_datetime_rounded])),

#"Added Custom7" = Table.AddColumn(#"Added Custom6", "DURATION_rounded_end", each ([E_datetime_rounded]-DateTimeZone.RemoveZone([StartDateTime]))),

#"Added Custom8" = Table.AddColumn(#"Added Custom7", "DURATION_normal", each [DateTimeEnd]-[StartDateTime]),

#"Added Custom13" = Table.AddColumn(#"Added Custom8", "DURATION", each if Time.From([StartDateTime]) = Time.From([START]) and Time.From([DateTimeEnd]) = Time.From([END]) then [DURATION_rounded_both] else if Time.From([StartDateTime]) = Time.From([START]) and Time.From([DateTimeEnd]) <> Time.From([END]) then [DURATION_rounded_start] else if Time.From([StartDateTime]) <> Time.From([START]) and Time.From([DateTimeEnd]) = Time.From([END]) then [DURATION_rounded_end] else [DURATION_normal]),

#"Inserted Total Hours" = Table.AddColumn(#"Added Custom13", "TOTAL HOURS", each Duration.TotalHours([DURATION]), type number),

#"Merged Queries1" = Table.NestedJoin(#"Inserted Total Hours", {"Date"}, #"Holidays and Observances in Japan 2020", {"Date"}, "Holidays and Observances in Japan 2020", JoinKind.LeftOuter),

#"Expanded Holidays and Observances in Japan 2020" = Table.ExpandTableColumn(#"Merged Queries1", "Holidays and Observances in Japan 2020", {"ISHOLIDAY"}, {"ISHOLIDAY"}),

#"Inserted Day of Week" = Table.AddColumn(#"Expanded Holidays and Observances in Japan 2020", "Day of Week", each Date.DayOfWeek([Date]), Int64.Type),

#"Added Conditional Column3" = Table.AddColumn(#"Inserted Day of Week", "ISWEEKEND", each if [Day of Week] = 5 then 1 else if [Day of Week] = 6 then 1 else null, Int64.Type),

#"Added Conditional Column4" = Table.AddColumn(#"Added Conditional Column3", "StartDateTime_tobeused", each if [S_datetime_rounded] = [START] then [START] else DateTimeZone.RemoveZone([StartDateTime]), type datetime),

#"Added Custom12" = Table.AddColumn(#"Added Conditional Column4", "EndDateTime_tobeused", each if [E_datetime_rounded] = [END] then [END] else DateTimeZone.RemoveZone([DateTimeEnd]), type datetime),

#"Inserted Start of Hour" = Table.AddColumn(#"Added Custom12", "start_hour_tobeused", each Time.StartOfHour([StartDateTime_tobeused]), type datetime),

#"Changed Type12" = Table.TransformColumnTypes(#"Inserted Start of Hour",{{"start_hour_tobeused", type time}}),

#"Inserted Start of Hour1" = Table.AddColumn(#"Changed Type12", "end_hour_tobeused", each Time.StartOfHour([EndDateTime_tobeused]), type datetime),

#"Changed Type13" = Table.TransformColumnTypes(#"Inserted Start of Hour1",{{"end_hour_tobeused", type time}}),

#"Removed Other Columns2" = Table.SelectColumns(#"Changed Type13",{"Id", "Title", "EmployeeName", "Date", "merge_id", "StartDateTime_tobeused", "EndDateTime_tobeused", "start_hour_tobeused", "end_hour_tobeused"}),

#"Added Custom10" = Table.AddColumn(#"Removed Other Columns2", "Custom", each WINDOWS),

#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom10", "Custom", {"Hour of Day", "Window"}, {"Hour of Day", "Window"}),

#"Removed Errors" = Table.RemoveRowsWithErrors(#"Expanded Custom", {"Window"}),

#"Changed Type14" = Table.TransformColumnTypes(#"Removed Errors",{{"Hour of Day", Int64.Type}}),

#"Added Custom11" = Table.AddColumn(#"Changed Type14", "IsApplicable", each if Time.Hour([start_hour_tobeused])<=[Hour of Day] and Time.Hour([end_hour_tobeused])>=[Hour of Day] then 1 else null),

#"Filtered Rows" = Table.SelectRows(#"Added Custom11", each [IsApplicable] = 1),

#"Added Custom14" = Table.AddColumn(#"Filtered Rows", "duration in hour (in minutes)", each if (Time.Hour([StartDateTime_tobeused] ) = Time.Hour([EndDateTime_tobeused])) then (Time.Minute( [EndDateTime_tobeused] ) - Time.Minute([StartDateTime_tobeused]))

else

if (Time.Hour([StartDateTime_tobeused]) = [Hour of Day]) then (60 - Time.Minute([StartDateTime_tobeused]))

else

if ( Time.Hour([EndDateTime_tobeused]) = [Hour of Day]) then Time.Minute([EndDateTime_tobeused])

else 60),

#"Changed Type15" = Table.TransformColumnTypes(#"Added Custom14",{{"duration in hour (in minutes)", Int64.Type}}),

#"Removed Other Columns3" = Table.SelectColumns(#"Changed Type15",{"Id", "merge_id", "Window", "duration in hour (in minutes)"}),

#"Pivoted Column" = Table.Pivot(#"Removed Other Columns3", List.Distinct(#"Removed Other Columns3"[Window]), "Window", "duration in hour (in minutes)", List.Sum)

in

#"Pivoted Column"

[kbachova · Accepted solution]

Found out, that the data source was corrupted and enabled null value entry to one of the dates I was using.

The inconvenience of this error however is, that it starts showing very late in the query - the issue was in the datasource, so there should be a way to point to the place that is causing the error to appear.

Advice for anyone experiencing a similar issue, also check the data source for unexpected null values.

Thanks for all hints anyway, they were useful as they helped me to find out the solution.

[mussaenda]

Hi @kbachova,

It happens when we are declaring wrong types for  the column.

From your query below, I saw that you added lots of custom column.

Therefore I cannot point which is causing the error.

But please double check your applied steps especially the #Changed Type

I also encounter this error sometimes and it is because of the auto change type right after split or add some columns.

[kbachova]

@mussaenda thank you for your response. Can you recall what types were causing you troubles and what did you changed them into to make the error disappear?

Thank you!

[amitchandak]

Have you created any custom column in query editor?  A boolean field or condition is getting null value. It can be because field was directly used as condition, expected to have true or false value and there is null value now

[kbachova]

@amitchandak thank you for your response. I do have indeed a few customs columns created, and so far I have not managed to find any null value or an incorrect data type applied. My concern is that the report with this code worked until 5/2/2020 and I only have seen it breaking since then. So i was wondering if there was maybe something in February update that would be causing this? Are you aware?

Thank you!