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"