let
Source = #"Intermediary Flights",
#"Merged queries" = Table.NestedJoin(Source, {"FlightID"}, #"Flights Info", {"FlightID"}, "Flights Info", JoinKind.LeftOuter),
#"Merged queries 1" = Table.NestedJoin(#"Merged queries", {"AircraftID"}, Aircraft, {"AircraftID"}, "Aircraft", JoinKind.LeftOuter),
#"Merged queries 2" = Table.NestedJoin(#"Merged queries 1", {"AircraftTypeID"}, #"Aircraft Types", {"AircraftTypeID"}, "Aircraft Types", JoinKind.LeftOuter),
#"Merged queries 3" = Table.NestedJoin(#"Merged queries 2", {"FlightID"}, #"Flights Ext", {"FlightID"}, "Flights Ext", JoinKind.LeftOuter),
#"Merged queries 4" = Table.NestedJoin(#"Merged queries 3", {"FlightID"}, Legs, {"FlightID"}, "Legs", JoinKind.LeftOuter),
#"Expanded Flights Info" = Table.ExpandTableColumn(#"Merged queries 4", "Flights Info", {"FlightTask", "PrimaryTripNumber", "PrimaryAccount", "OrigAirportICAOCode", "OrigStateAbbreviation", "OrigCountryName", "OrigLatitude", "OrigLongitude", "OrigTzStandardAbbreviation", "DestAirportICAOCode", "DestStateAbbreviation", "DestCountryName", "DestLatitude", "DestLongitude", "DestTzStandardAbbreviation", "TaxiTime", "Contracts.ContractName", "Contracts.tl_Lookups.LookupValue", "Contracts.tl_AircraftTypes.Model"}, {"Flights Info.FlightTask", "Flights Info.PrimaryTripNumber", "Flights Info.PrimaryAccount", "Flights Info.OrigAirportICAOCode", "Flights Info.OrigStateAbbreviation", "Flights Info.OrigCountryName", "Flights Info.OrigLatitude", "Flights Info.OrigLongitude", "Flights Info.OrigTzStandardAbbreviation", "Flights Info.DestAirportICAOCode", "Flights Info.DestStateAbbreviation", "Flights Info.DestCountryName", "Flights Info.DestLatitude", "Flights Info.DestLongitude", "Flights Info.DestTzStandardAbbreviation", "Flights Info.TaxiTime", "Flights Info.Contracts.ContractName", "Flights Info.Contracts.tl_Lookups.LookupValue", "Flights Info.Contracts.tl_AircraftTypes.Model"}),
#"Expanded Aircraft" = Table.ExpandTableColumn(#"Expanded Flights Info", "Aircraft", {"Registration", "SerialNumber"}, {"Aircraft.Registration", "Aircraft.SerialNumber"}),
#"Expanded Aircraft Types" = Table.ExpandTableColumn(#"Expanded Aircraft", "Aircraft Types", {"Model"}, {"Aircraft Types.Model"}),
#"Expanded Flights Ext" = Table.ExpandTableColumn(#"Expanded Aircraft Types", "Flights Ext", {"CalculatedFlightTime"}, {"Flights Ext.CalculatedFlightTime"}),
#"Expanded Legs" = Table.ExpandTableColumn(#"Expanded Flights Ext", "Legs", {"DateCreated", "Aircraft Types.Model"}, {"Legs.DateCreated", "Legs.Aircraft Types.Model"}),
#"Duplicated column" = Table.DuplicateColumn(#"Expanded Legs", "TimeOut", "TimeOut - Copy"),
#"Duplicated column 1" = Table.DuplicateColumn(#"Duplicated column", "TimeOut", "TimeOut - Copy.1"),
#"Duplicated column 2" = Table.DuplicateColumn(#"Duplicated column 1", "TimeOff", "TimeOff - Copy"),
#"Duplicated column 3" = Table.DuplicateColumn(#"Duplicated column 2", "TimeOff", "TimeOff - Copy.1"),
#"Added custom" = Table.AddColumn(#"Duplicated column 3", "Dep Latitude Rad", each ([Flights Info.OrigLatitude] * (Number.PI/180 ))),
#"Added custom 1" = Table.AddColumn(#"Added custom", "Arr Latitude Rad", each ([Flights Info.DestLatitude] * (Number.PI/180 ))),
#"Added custom 2" = Table.AddColumn(#"Added custom 1", "Dep Longitude Rad", each ([Flights Info.DestLongitude] * (Number.PI/180 ))),
#"Added custom 3" = Table.AddColumn(#"Added custom 2", "Arr Longitude Rad", each ([Flights Info.OrigLongitude] * (Number.PI/180 ))),
#"Added custom 4" = Table.AddColumn(#"Added custom 3", "Scheduled Flight Time", each [Flights Ext.CalculatedFlightTime] / 60),
#"Added custom 5" = Table.AddColumn(#"Added custom 4", "Raw Distance (sm)", each Number.Acos(Number.Sin([Dep Latitude Rad])*Number.Sin([Arr Latitude Rad])+Number.Cos([Dep Latitude Rad])*Number.Cos([Arr Latitude Rad])*Number.Cos([Arr Longitude Rad] - [Dep Longitude Rad]))*3963),
#"Added custom 6" = Table.AddColumn(#"Added custom 5", "Dep Region", each if [Flights Info.OrigStateAbbreviation] = "AL" then "Southeast" else if [Flights Info.OrigStateAbbreviation] = "AR" then "Gulf" else if [Flights Info.OrigStateAbbreviation] = "AZ" then "Southwest" else if [Flights Info.OrigStateAbbreviation] = "CA" then "Southwest" else if [Flights Info.OrigStateAbbreviation] = "CO" then "Mountain" else if [Flights Info.OrigStateAbbreviation] = "CT" then "Northeast" else if [Flights Info.OrigStateAbbreviation] = "DC" then "Mid-Atlantic" else if [Flights Info.OrigStateAbbreviation] = "DE" then "Mid-Atlantic" else if [Flights Info.OrigStateAbbreviation] = "FL" then "Southeast" else if [Flights Info.OrigStateAbbreviation] = "GA" then "Southeast" else if [Flights Info.OrigStateAbbreviation] = "IA" then "Plains" else if [Flights Info.OrigStateAbbreviation] = "ID" then "Mountain" else if [Flights Info.OrigStateAbbreviation] = "IL" then "Great Lakes" else if [Flights Info.OrigStateAbbreviation] = "IN" then "Great Lakes" else if [Flights Info.OrigStateAbbreviation] = "KS" then "Plains" else if [Flights Info.OrigStateAbbreviation] = "KY" then "Great Lakes" else if [Flights Info.OrigStateAbbreviation] = "LA" then "Gulf" else if [Flights Info.OrigStateAbbreviation] = "MA" then "Northeast" else if [Flights Info.OrigStateAbbreviation] = "MD" then "Mid-Atlantic" else if [Flights Info.OrigStateAbbreviation] = "ME" then "Northeast" else if [Flights Info.OrigStateAbbreviation] = "MI" then "Great Lakes" else if [Flights Info.OrigStateAbbreviation] = "MN" then "Great Lakes" else if [Flights Info.OrigStateAbbreviation] = "MO" then "Plains" else if [Flights Info.OrigStateAbbreviation] = "MS" then "Gulf" else if [Flights Info.OrigStateAbbreviation] = "MT" then "Mountain" else if [Flights Info.OrigStateAbbreviation] = "NC" then "Mid-Atlantic" else if [Flights Info.OrigStateAbbreviation] = "ND" then "Plains" else if [Flights Info.OrigStateAbbreviation] = "NE" then "Plains" else if [Flights Info.OrigStateAbbreviation] = "NH" then "Northeast" else if [Flights Info.OrigStateAbbreviation] = "NJ" then "Northeast" else if [Flights Info.OrigStateAbbreviation] = "NM" then "Southwest" else if [Flights Info.OrigStateAbbreviation] = "NV" then "Southwest" else if [Flights Info.OrigStateAbbreviation] = "NY" then "Northeast" else if [Flights Info.OrigStateAbbreviation] = "OH" then "Great Lakes" else if [Flights Info.OrigStateAbbreviation] = "OK" then "Gulf" else if [Flights Info.OrigStateAbbreviation] = "OR" then "Northwest" else if [Flights Info.OrigStateAbbreviation] = "PA" then "Mid-Atlantic" else if [Flights Info.OrigStateAbbreviation] = "RI" then "Northeast" else if [Flights Info.OrigStateAbbreviation] = "SC" then "Southeast" else if [Flights Info.OrigStateAbbreviation] = "SD" then "Plains" else if [Flights Info.OrigStateAbbreviation] = "TN" then "Southeast" else if [Flights Info.OrigStateAbbreviation] = "TX" then "Gulf" else if [Flights Info.OrigStateAbbreviation] = "UT" then "Mountain" else if [Flights Info.OrigStateAbbreviation] = "VA" then "Mid-Atlantic" else if [Flights Info.OrigStateAbbreviation] = "VT" then "Northeast" else if [Flights Info.OrigStateAbbreviation] = "WA" then "Northwest" else if [Flights Info.OrigStateAbbreviation] = "WI" then "Great Lakes" else if [Flights Info.OrigStateAbbreviation] = "WV" then "Mid-Atlantic" else if [Flights Info.OrigStateAbbreviation] = "WY" then "Mountain" else if [Flights Info.OrigCountryName] = "Canada" then "Canada" else if [Flights Info.OrigCountryName] = "Bahamas" then "Bahamas" else if [Flights Info.OrigCountryName] = "Caribbean" then "Caribbean" else if [Flights Info.OrigCountryName] = "Costa Rica" then "Central America" else if [Flights Info.OrigCountryName] = "Bermuda" then "Bermuda" else if [Flights Info.OrigCountryName] = "Mexico" then "Mexico" else if [Flights Info.OrigCountryName] = "Cuba" then "Cuba" else if [Flights Info.OrigCountryName] = "Central America" then "Central America" else if [Flights Info.OrigCountryName] = "Cayman Islands" then "Cayman Islands" else if [Flights Info.OrigCountryName] = "Jamaica" then "Caribbean" else null),
#"Added custom 7" = Table.AddColumn(#"Added custom 6", "Arr Region", each if [Flights Info.DestStateAbbreviation] = "AL" then "Southeast" else if [Flights Info.DestStateAbbreviation] = "AR" then "Gulf" else if [Flights Info.DestStateAbbreviation] = "AZ" then "Southwest" else if [Flights Info.DestStateAbbreviation] = "CA" then "Southwest" else if [Flights Info.DestStateAbbreviation] = "CO" then "Mountain" else if [Flights Info.DestStateAbbreviation] = "CT" then "Northeast" else if [Flights Info.DestStateAbbreviation] = "DC" then "Mid-Atlantic" else if [Flights Info.DestStateAbbreviation] = "DE" then "Mid-Atlantic" else if [Flights Info.DestStateAbbreviation] = "FL" then "Southeast" else if [Flights Info.DestStateAbbreviation] = "GA" then "Southeast" else if [Flights Info.DestStateAbbreviation] = "IA" then "Plains" else if [Flights Info.DestStateAbbreviation] = "ID" then "Mountain" else if [Flights Info.DestStateAbbreviation] = "IL" then "Great Lakes" else if [Flights Info.DestStateAbbreviation] = "IN" then "Great Lakes" else if [Flights Info.DestStateAbbreviation] = "KS" then "Plains" else if [Flights Info.DestStateAbbreviation] = "KY" then "Great Lakes" else if [Flights Info.DestStateAbbreviation] = "LA" then "Gulf" else if [Flights Info.DestStateAbbreviation] = "MA" then "Northeast" else if [Flights Info.DestStateAbbreviation] = "MD" then "Mid-Atlantic" else if [Flights Info.DestStateAbbreviation] = "ME" then "Northeast" else if [Flights Info.DestStateAbbreviation] = "MI" then "Great Lakes" else if [Flights Info.DestStateAbbreviation] = "MN" then "Great Lakes" else if [Flights Info.DestStateAbbreviation] = "MO" then "Plains" else if [Flights Info.DestStateAbbreviation] = "MS" then "Gulf" else if [Flights Info.DestStateAbbreviation] = "MT" then "Mountain" else if [Flights Info.DestStateAbbreviation] = "NC" then "Mid-Atlantic" else if [Flights Info.DestStateAbbreviation] = "ND" then "Plains" else if [Flights Info.DestStateAbbreviation] = "NE" then "Plains" else if [Flights Info.DestStateAbbreviation] = "NH" then "Northeast" else if [Flights Info.DestStateAbbreviation] = "NJ" then "Northeast" else if [Flights Info.DestStateAbbreviation] = "NM" then "Southwest" else if [Flights Info.DestStateAbbreviation] = "NV" then "Southwest" else if [Flights Info.DestStateAbbreviation] = "NY" then "Northeast" else if [Flights Info.DestStateAbbreviation] = "OH" then "Great Lakes" else if [Flights Info.DestStateAbbreviation] = "OK" then "Gulf" else if [Flights Info.DestStateAbbreviation] = "OR" then "Northwest" else if [Flights Info.DestStateAbbreviation] = "PA" then "Mid-Atlantic" else if [Flights Info.DestStateAbbreviation] = "RI" then "Northeast" else if [Flights Info.DestStateAbbreviation] = "SC" then "Southeast" else if [Flights Info.DestStateAbbreviation] = "SD" then "Plains" else if [Flights Info.DestStateAbbreviation] = "TN" then "Southeast" else if [Flights Info.DestStateAbbreviation] = "TX" then "Gulf" else if [Flights Info.DestStateAbbreviation] = "UT" then "Mountain" else if [Flights Info.DestStateAbbreviation] = "VA" then "Mid-Atlantic" else if [Flights Info.DestStateAbbreviation] = "VT" then "Northeast" else if [Flights Info.DestStateAbbreviation] = "WA" then "Northwest" else if [Flights Info.DestStateAbbreviation] = "WI" then "Great Lakes" else if [Flights Info.DestStateAbbreviation] = "WV" then "Mid-Atlantic" else if [Flights Info.DestStateAbbreviation] = "WY" then "Mountain" else if [Flights Info.DestCountryName] = "Canada" then "Canada" else if [Flights Info.DestCountryName] = "Bahamas" then "Bahamas" else if [Flights Info.DestCountryName] = "Caribbean" then "Caribbean" else if [Flights Info.DestCountryName] = "Costa Rica" then "Central America" else if [Flights Info.DestCountryName] = "Bermuda" then "Bermuda" else if [Flights Info.DestCountryName] = "Mexico" then "Mexico" else if [Flights Info.DestCountryName] = "Cuba" then "Cuba" else if [Flights Info.DestCountryName] = "Central America" then "Central America" else if [Flights Info.DestCountryName] = "Cayman Islands" then "Cayman Islands" else if [Flights Info.DestCountryName] = "Jamaica" then "Caribbean" else null),
#"Added custom 8" = Table.AddColumn(#"Added custom 7", "Edited Leg Code", each if Text.Start([Flights Info.FlightTask], 1) = "1"
and Text.Start([Changed Type2.FlightTask],1) <> "1"
and [Tail]=[Changed Type2.FlightTask]
then "1-Repo" & [Changed Type2.FlightTask]
else [Flights Info.FlightTask]),
#"Added custom 9" = Table.AddColumn(#"Added custom 8", "Filter 1", each if [Edited Leg Code] = [Flights Info.FlightTask] then 0 else 1),
#"Added custom 10" = Table.AddColumn(#"Added custom 9", "Filter 2", each if [Flights Info.DestAirportICAOCode] = "KPSM"
and [Edited Leg Code]<>"1-Repo"
and [Flights Info.FlightTask]<>"1-Repo"
and [Flights Info.FlightTask]<>"Managed"
then 1
else 0),
#"Added custom 11" = Table.AddColumn(#"Added custom 10", "Filter 3", each if [Flights Info.FlightTask] = "Owner"
or Text.Start([Flights Info.Contracts.tl_Lookups.LookupValue],2)="PC"
and [Flights Info.FlightTask]<>"Owner"
and Text.Start([Flights Info.Contracts.tl_Lookups.LookupValue], 2)="PC"
then 1
else 0),
#"Added custom 12" = Table.AddColumn(#"Added custom 11", "Filter 4", each if Text.Start([Flights Info.Contracts.tl_Lookups.LookupValue], 2) = "Co"
and [Flights Info.FlightTask]<>"Charter"
and [Flights Info.FlightTask]<>"Demo"
and [Flights Info.FlightTask]<>"Transition"
or Text.Start([Flights Info.Contracts.tl_Lookups.LookupValue], 2) <> "Co"
and [Flights Info.FlightTask] = "Charter"
or [Flights Info.FlightTask] = "Demo"
or [Flights Info.FlightTask] = "Transition"
then 1
else 0),
#"Added custom 13" = Table.AddColumn(#"Added custom 12", "Filter 5", each if [Flights Info.FlightTask]= "Admin"
and [PaxCount] = 0
then 1
else 0),
#"Added custom 14" = Table.AddColumn(#"Added custom 13", "Filter 6 Time Calc", each Duration.From([TimeOff]-[TimeOff])),
#"Changed column type" = Table.TransformColumnTypes(#"Added custom 14", {{"Filter 6 Time Calc", type number}}),
#"Added custom 15" = Table.AddColumn(#"Changed column type", "Filter 6 True/False", each [Filter 6 Time Calc] < (20.5/24)),
#"Added custom 16" = Table.AddColumn(#"Added custom 15", "Filter 6", each if [Flights Info.FlightTask] = "Currency"
and [#"Filter 6 True/False"] = "True"
then 1
else 0),
#"Added custom 17" = Table.AddColumn(#"Added custom 16", "Filter 7", each if [Flights Info.FlightTask] = "External Customer" then 1 else 0),
#"Removed columns" = Table.RemoveColumns(#"Added custom 17", {"FlightID", "AircraftID", "AircraftTypeID", "TimeOut", "TimeOff", "OrigETD", "Flights Info.OrigLatitude", "Flights Info.OrigLongitude", "Flights Info.DestLatitude", "Flights Info.DestLongitude", "Flights Ext.CalculatedFlightTime", "Dep Latitude Rad", "Arr Latitude Rad", "Dep Longitude Rad", "Arr Longitude Rad"}),
#"Renamed columns" = Table.RenameColumns(#"Removed columns", {{"FlightNumber", "Flight Number"}, {"FlightTime", "Actual Flight Time"}, {"FOBSU", "Fuel Burn"}, {"PaxCount", "Pax Count"}, {"Flights Info.FlightTask", "Leg Code"}, {"Flights Info.PrimaryTripNumber", "Trip Number"}, {"Flights Info.PrimaryAccount", "Account Name"}, {"Flights Info.OrigAirportICAOCode", "Dep ICAO"}, {"Flights Info.OrigStateAbbreviation", "Dep State"}, {"Flights Info.OrigCountryName", "Dep Country"}, {"Flights Info.OrigTzStandardAbbreviation", "Dep Time Zone"}, {"Flights Info.DestAirportICAOCode", "Arr ICAO"}, {"Flights Info.DestStateAbbreviation", "Arr State"}, {"Flights Info.DestCountryName", "Arr Country"}, {"Flights Info.DestTzStandardAbbreviation", "Arr Time Zone"}, {"Flights Info.TaxiTime", "Taxi Time"}, {"Flights Info.Contracts.ContractName", "Contract Name"}, {"Flights Info.Contracts.tl_Lookups.LookupValue", "Program Type"}, {"Flights Info.Contracts.tl_AircraftTypes.Model", "Contract A/C"}, {"Aircraft.Registration", "Tail"}, {"Aircraft.SerialNumber", "Serial Number"}, {"Aircraft Types.Model", "Actual A/C"}, {"Legs.DateCreated", "Initially Entered"}, {"Legs.Aircraft Types.Model", "Requested A/C"}, {"TimeOut - Copy", "Date Out"}, {"TimeOut - Copy.1", "Time Out"}, {"TimeOff - Copy", "Date Off"}, {"TimeOff - Copy.1", "Time Off"}})
in
#"Renamed columns"