HOW-TO::AVOID::Error in replacing table's content with new data in a version

[Element115]

ERROR:

dataflow_name_WriteToDataDestination: Mashup Exception Expression Error Couldn't refresh the entity because of an issue with the mashup document MashupException.Error: Expression.Error: Error in replacing table's content with new data in a version: #{0}., InnerException: We cannot apply operator < to types Table and Number., Underlying error: We cannot apply operator < to types Table and Number. Details: Reason = Expression.Error;Message = We cannot apply operator < to types Table and Number.;Detail = [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];Message.Format = We cannot apply operator #{0} to types #{1} and #{2}.;Message.Parameters = {"<", "Table", "Number"};Microsoft.Data.Mashup.Error.Context = User

HOW-TO AVOID:

Apparently there is a type of M query that should NEVER have staging enabled.  It is a query that uses a drilldown with a statistical method applied to it and returns only one value, a number. Let me show you what I mean.  First, the code.  I have 2 queries in a DF.

query1

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(#"Changed column type", each [INDEX] > IncrementalIndex)
in
  #"Filtered rows"

The important point to note here is the last statement #"Filtered rows".  The use of the comparison operator '>' blows up the whole DF if the following query has staging enabled:

query2

 let
  Source = Lakehouse.Contents(null){[workspaceId = "142e0ce2-c040-436d-8eb8-58aea822ed45"]}[Data]{[lakehouseId = "7bf44645-8991-4130-a10e-cf91bcbcd618"]}[Data],
  #"Navigation 1" = Source{[Id = "FACT_RWS", ItemKind = "Table"]}[Data],
  #"Drill down" = #"Navigation 1"[INDEX],
  #"Calculated maximum" = List.Max(#"Drill down")
in
  #"Calculated maximum"

The last 2 lines are the result of using the GUI to do a drill down on a column names INDEX.  If this type of query, which returns only one numerical value, has staging enabled, the error above ensues. 

I'm gonna guess that the error message is saying that this last query, because of staging enabled, is being treated as a table and you can't compare a table to a single numerical value.  

Aaahhh details, details...

[miguel · Accepted solution]

Your conclusion is correct. In order to stage a query it has to be treated as a table. In this case your query that results in a scalar value will be treated as a table with a single column and a single value. You could stage the numeric value as a table, and then drill down to it in another query and use it as a value for your Table.SelectRows query

[Element115]

Thanks @miguel for confirming.  There is a case# for this issue:  2403050040002140.

I also created a new Idea for some sort of GUI feedback mechanism warning users before publishing that this type of scalar returning value queries could lead to a failed refresh.  I think it would be more user friendly to find out this way rather that getting the error message I quoted above.  This is also with bearing in mind, that if most users learned how to use Power Query and M not by attending some well structured class, but by just opening the M function reference page and trying sh*t out, learning as you go, then there are fundamental concepts you might not keep in mind quite yet when using PQ, hence the need for more user friendly GUI feedback preempting the generation of cryptic error messages and causing users to initiate even more support tickets.

[miguel]

I'm the product manager working on the feature that will tell you if its something wrong before you publish (or after you publish) 😀

Hoping that such feature will come out in the next few months, but we're working on the full framework and UI components that do not feel intrussive or get in your way.

[v-cboorla-msft]

Hi @Element115 

Thanks for using Microsoft Fabric Community.
At this time, we are reaching out to the internal team to get some help on this .
We will update you once we hear back from them.

Appreciate your patience.

Thanks.