BUG?::POWER QUERY:column data type changes depending on M code statement order

[Element115]

ISSUE:

After changing the order of one statement in the M script, the column data type of a few columns changes and ignores the explicit Table.TransformColumnTypes() depending where it is located in the script.

If this code:

let
  Source = PowerPlatform.Dataflows([]),
  #"Navigation 1" = Source{[Id = "Workspaces"]}[Data],
  #"Navigation 2" = #"Navigation 1"{[workspaceId = ""]}[Data],
  Navigation = #"Navigation 2"{[dataflowId = ""]}[Data],
  #"Navigation 3" = Navigation{[entity = "FACT_RWS", version = ""]}[Data],
  #"Filtered rows" = Table.SelectRows(#"Navigation 3", each [INDEX] > IncrementalIndex),  
  #"Changed column type"  = Table.TransformColumnTypes(
                                                        #"Filtered rows", 
                                                        {
                                                          {"DATE", type date},
                                                          {"TIME", type datetime}, 
                                                          {"HOUR", type datetime},
                                                          {"M15", type datetime},
                                                          {"ATMOSPHERIC_PRESSURE", Int64.Type}, 
                                                          {"BATTERY_STATUS", Int64.Type}, 
                                                          {"PAVEMENT_CHEMICAL_CONCENTRATION", Int64.Type}, 
                                                          {"PAVEMENT_BLOCK", Int64.Type}, 
                                                          {"PRECIPITATION_SITUATION", Int64.Type}, 
                                                          // {"RELATIVE_HUMIDITY", Percentage.Type},  // do not cast, random INT64 overflow error ensues, WTF!?
                                                          {"VISIBILITY", Int64.Type}, 
                                                          {"WIND_GUST_DIRECTION", Int64.Type}, 
                                                          {"WIND_GUST_SPEED", Int64.Type}, 
                                                          {"SOLAR_RADIATION", Int64.Type}
                                                        }
                                                      )
in
  #"Changed column type"
  // #"Filtered rows"

the column types look like this and are correct:

But this code, in which the #"Filtered rows" statement is put at the very end of the M script,

let
  Source = PowerPlatform.Dataflows([]),
  #"Navigation 1" = Source{[Id = "Workspaces"]}[Data],
  #"Navigation 2" = #"Navigation 1"{[workspaceId = "142e0ce2-c040-436d-8eb8-58aea822ed45"]}[Data],
  Navigation = #"Navigation 2"{[dataflowId = "750bf43a-f676-4b77-a763-eea54958a1b6"]}[Data],
  #"Navigation 3" = Navigation{[entity = "FACT_RWS", version = ""]}[Data],
  #"Changed column type"  = Table.TransformColumnTypes(
                                                        #"Navigation 3", 
                                                        {
                                                          {"DATE", type date},
                                                          {"TIME", type datetime}, 
                                                          {"HOUR", type datetime},
                                                          {"M15", type datetime},
                                                          {"ATMOSPHERIC_PRESSURE", Int64.Type}, 
                                                          {"BATTERY_STATUS", Int64.Type}, 
                                                          {"PAVEMENT_CHEMICAL_CONCENTRATION", Int64.Type}, 
                                                          {"PAVEMENT_BLOCK", Int64.Type}, 
                                                          {"PRECIPITATION_SITUATION", Int64.Type}, 
                                                          // {"RELATIVE_HUMIDITY", Percentage.Type},  // do not cast, random INT64 overflow error ensues, WTF!?
                                                          {"VISIBILITY", Int64.Type}, 
                                                          {"WIND_GUST_DIRECTION", Int64.Type}, 
                                                          {"WIND_GUST_SPEED", Int64.Type}, 
                                                          {"SOLAR_RADIATION", Int64.Type}
                                                        }
                                                      )
  #"Filtered rows" = Table.SelectRows(#"Navigation 3", each [INDEX] > IncrementalIndex)
in
  //#"Changed column type"
  #"Filtered rows"

causes the column types to be incorrect insofar that they now ignore the specific Table.TransformColumnTypes() command:

Finally, IncrementalIndex is another query:

let
  Source = Lakehouse.Contents(null){[workspaceId = ""]}[Data]{[lakehouseId = ""]}[Data],
  #"Navigation 1" = Source{[Id = "FACT_RWS", ItemKind = "Table"]}[Data],
  #"Drill down" = #"Navigation 1"[INDEX],
  #"Calculated maximum" = List.Max(#"Drill down")
in
  #"Calculated maximum"

Why?  Am I missing something or...?

[Element115]

Interestingly, with the statements of the M script re-ordered, I now get this error message:

df_fact_RWS_raw_WriteToDataDestination: Mashup Exception Expression Error Couldn't refresh the entity because of an issue with the mashup document MashupException.Error: Expression.Error: We cannot apply operator < to types Table and Number. Details: Reason = Expression.Error;Operator = <;Left = error "Microsoft.Mashup.Engine1.Runtime.ValueException: [Expression.Error] Value was not specified.#(cr)#(lf)   at Microsoft.Mashup.Engine1.Language.ValueCreator.CreateValueForThrow(IThrowExpression throwExpr)#(cr)#(lf)   at Microsoft.Mashup.Engine1.Language.ValueCreator.<>c__DisplayClass23_0.<CreateValueForRecord>b__0(Int32 index)#(cr)#(lf)   at Microsoft.Mashup.Engine1.Runtime.RecordValue.DemandRecordValue.get_Item(Int32 index)#(cr)#(lf)   at Microsoft.Data.Mashup.ProviderCommon.MashupResource.TryGetValue(Func`1 getValue, IValue& value, String& errorMessage)#(cr)#(lf)Record";Right = 689008;Microsoft.Data.Mashup.Error.Context = User

To initially load the data without the incremental index trick, all worked fine.  And 2 weeks ago or more, the IncrementalIndex query didn't cause any of the issues reported here.  Yet again, something changed on the backend.

[Element115]

I found what caused this error.  There are 2 queries in the DF.  One returns a single numeric value, so not a table, and had staging enabled. When I disabled staging on that particular query, the DF does not blow up at refresh time and the error goes away.  

You can close the issue.

[Anonymous]

Hi  @Element115 

I am so glad that you have fixed this issue . I will change the status to "Delivered"  .

Best Regards,
Community Support Team _ Ailsa Tao