Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
smcalister
Helper I
Helper I

Unable to Sort Dataflow Column

I am trying to recreate a Dataset as a Dataflow.  In the Dataset, I needed to sort by two columns and then add an Index.  Unfortunately, I'm receiving an error when trying to sort in my Dataflow.

 

I keep receiving the following error:

 

Microsoft SQL: An invalid floating point operation occurred.  

 

I don't know why I was able to sort in my Dataset but cannot sort in my Dataflow?

 

Thank you!

9 REPLIES 9
lbendlin
Super User
Super User

Maybe show a sanitized version of your Power Query code?

Was providing the Power Query code helpful?

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"

I think it was rather alarming.  That is quite a wall of code.

 

Anyway, your error happens before all that, either in #"Intermediary Flights"  or even before. Check for code that tries to do math with text, for example.

Sorry, I needed to add a region field that didn't exist in the DB.

 

How are you able to see that the error occurs before all that?  I don't get the error until I try to sort a column that has not had any math applied to it as my last step?

You are right, I did not consider query folding.  For the step 

 

#"Added custom 15" = Table.AddColumn(#"Changed column type", "Filter 6 True/False", each [Filter 6 Time Calc] < (20.5/24)),

 

check if Native Query is still enabled, and if yes, check which query is being produced.

Everything I'm finding says that Native Queries/Query Folding isn't available in Dataflow yet?

Right.  I think I should stop talking. I clearly have no idea what the issue could be.

 

What's the reason for going dataflows?

Oh crap, thanks for trying!  

 

I'm attempting to convert some SSRS reports that a colleague of mine created.  We're hoping to deprecate SSRS before it's deprecated on us and we're left without a solution.  Since the Report Builder requires Premium Access I have been using his queries to recreate.  I've found that trying to make little changes in Power Query forces import which I have found essentially useless.  It takes so long to import tables that I freeze the SQL server.  This is problematic since my Operations team is still in need of pulling reports off the server.

 

Dataflows allows me to use the query, make little adjustments here and there, and then refresh whenever I choose.  

 

I was able to accomplish everything I'm trying in Power Query (dataset) but the loading of tables was causing problems which is why I opted to go the Dataflow route with this particular report.  I don't understand why I receive that error on a sort of a column that comes directly from the DB?

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors