Hi there,
I am getting “OLE DB or ODBC error: [Expression.Error] We cannot convert the value null to type Logical” error. I am not sure where I am making mistake. Attached file contains the code. Any help would be really appreciated.
Below is my code:
let
Source = Sql.Database("pgissql01aenz.database.windows.net", "GIS_ANALYSIS"),
sde_support_structure_crossarm_evw = Source{[Schema="sde",Item="support_structure_crossarm_evw"]}[Data],
#"Filtered Rows" = Table.SelectRows(sde_support_structure_crossarm_evw, each ([STATUS] <> null)),
#"Filtered Rows4" = Table.SelectRows(#"Filtered Rows", each ([STATUS] = "In Service")),
#"Merged Queries" = Table.NestedJoin(#"Filtered Rows4", {"SITE_NUMBER"}, Poles, {"SITE_NUMBER"}, "sde support_structure_evw", JoinKind.LeftOuter),
#"Expanded sde support_structure_evw" = Table.ExpandTableColumn(#"Merged Queries", "sde support_structure_evw", {"INSTALLATIONDATE", "FACILITYID", "Criticality zone"}, {"sde support_structure_evw.INSTALLATIONDATE", "sde support_structure_evw.FACILITYID", "sde support_structure_evw.Criticality zone"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded sde support_structure_evw",{{"sde support_structure_evw.INSTALLATIONDATE", "POLE_INSTALLATIONDATE"}, {"sde support_structure_evw.FACILITYID", "POLE_FACILITYID"}, {"sde support_structure_evw.Criticality zone", "Criticality zone"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "INST_DATE", each if [INSTALLATIONDATE] = null and [POLE_INSTALLATIONDATE] <> null then [POLE_INSTALLATIONDATE]
else
[INSTALLATIONDATE]),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"INST_DATE", type datetime}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type", "FINAL_FACILITYID", each if [FACILITYID] = null and [POLE_FACILITYID] <> null then [POLE_FACILITYID]
else
[FACILITYID]),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"FINAL_FACILITYID", type text}}),
#"Filtered Rows1" = Table.SelectRows(#"Changed Type1", each ([INST_DATE] <> null)),
#"Added Custom2" = Table.AddColumn(#"Filtered Rows1", "Age", each DateTime.LocalNow() - [INST_DATE]),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Total Years", each Duration.TotalDays([Age]) / 365),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom3",{"Age"}),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"Total Years", "Age"}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Age", type number}}),
#"Added Custom4" = Table.AddColumn(#"Changed Type2", "Health Category", each let material = if (Text.Contains([CROSSARMMATERIAL],"W") or Text.Contains([CROSSARMMATERIAL],"M")) and [CROSSARMMATERIAL] <> "UNKNOWN" then true else false
in
if material = true and [Age] >= (55-1) then "H1"
else
if material = true and ([Age]< (55-1) and [Age] >= (55-3) ) then "H2"
else
if material = true and ([Age]< (55-3) and [Age] >= (55-10) ) then "H3"
else
if material = true and ([Age]< (55-10) and [Age] >= (55-20) ) then "H4"
else
if material = true and [Age]< (55-20) then "H5"
else
if [CROSSARMMATERIAL] = "STEEL" and [Age] > (75-1) then "H1"
else
if [CROSSARMMATERIAL] = "STEEL" and ([Age]< (75-1 )and [Age] >= (75-3) ) then "H2"
else
if [CROSSARMMATERIAL] = "STEEL" and ([Age]< (75-3) and [Age] >= (75-10) ) then "H3"
else
if [CROSSARMMATERIAL] = "STEEL" and ([Age]< (75-10) and [Age] >= (75-20) ) then "H4"
else
if [CROSSARMMATERIAL] = "STEEL" and [Age] < (75-20) then "H5"
else
if [Age] > (65-1) then "H1"
else
if ([Age]< (65-1 )and [Age] >= (65-3) ) then "H2"
else
if([Age]< (65-3) and [Age] >= (65-10) ) then "H3"
else
if ([Age]< (65-10) and [Age] >= (65-20) ) then "H4"
else
if [Age] < (65-20) then "H5" else null),
#"Filtered Rows3" = Table.SelectRows(#"Added Custom4", each ([Health Category] <> null)),
#"Added Conditional Column" = Table.AddColumn(#"Filtered Rows3", "Likelihood", each if [Health Category] = "H5" then "Rare" else if [Health Category] = "H2" then "Likely" else if [Health Category] = "H3" then "Possible" else if [Health Category] = "H4" then "Unlikely" else "Almost Certain"),
#"Changed Type3" = Table.TransformColumnTypes(#"Added Conditional Column",{{"Likelihood", type text}}),
#"Added Conditional Column1" = Table.AddColumn(#"Changed Type3", "Impact", each if [Criticality zone] = "C0" then "Catastrophic" else if [Criticality zone] = "C1" then "Major" else if [Criticality zone] = "C2" then "Moderate" else if [Criticality zone] = "C3" then "Minor" else if ([Criticality zone] = "C4" or [Criticality zone] = "C5") then "Insignificant" else ""),
#"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column1", "Critical Zone Order", each if ([Criticality zone] = "C4" or [Criticality zone] = "C5") then 1 else if [Criticality zone] = "C3" then 2 else if [Criticality zone] = "C2" then 3 else if [Criticality zone] = "C1" then 4 else if [Criticality zone] = "C0" then 5 else 1),
#"Changed Type4" = Table.TransformColumnTypes(#"Added Conditional Column2",{{"Critical Zone Order", type text}}),
#"Added Conditional Column3" = Table.AddColumn(#"Changed Type4", "AHI SCORE", each if [Likelihood] = "Almost Certain" then 1 else if [Likelihood] = "Likely" then 2 else if [Likelihood] = "Possible" then 3 else if [Likelihood] = "Unlikely" then 4 else 5),
#"Changed Type5" = Table.TransformColumnTypes(#"Added Conditional Column3",{{"AHI SCORE", Int64.Type}}),
#"Rounded Down" = Table.TransformColumns(#"Changed Type5",{{"Age", Number.RoundDown, Int64.Type}}),
#"Removed Columns1" = Table.RemoveColumns(#"Rounded Down",{"ABANDONDATE", "CONDITIONDATE", "ABANDONREASON", "DATECREATED", "DATEMODIFIED", "GlobalID", "POLE_INSTALLATIONDATE", "SDE_STATE_ID"})
in
#"Removed Columns1"
