Column not found when refreshing dataflow from web services but works fine in editor

[molegris]

Hi,

I need help!  I've been trying for 2 days to find the root cause of that issue and I can't figure out whats wrong.

I initially encounter this problem in a complex dataflow but I was able to reproduce the “bug” with a very simple dataflow. 

Usecase to reproduce the problem :

• The dataflow has 2 very simple queries.  The first query imports a csv file and the other gets the maximum value of the timestamp column from the first query.
• Everything refreshes just fine in the editor. 
• I setup the mapping to upload into separate destination tables in Dataverse then save it. 
• It’s when I try to refresh the dataflow from the web service that I get the error :
  “bugColumnNotFound,getMax,15/12/2020 5:51:52 pm,15/12/2020 5:51:54 pm,Failed,"Error Code: Mashup Exception Error, Error Details: Couldn't refresh the entity because of an issue with the mashup document MashupException.Error: Expression.Error: The column 'timestamp' of the table wasn't found. Details: timestamp (request id: 30d76f34-7155-442d-946c-05732e1dd5c9)."
• Important observation : The problem disappears when I disable load on the first query (getFile)…. But I obviously want to load data for the getFile query…it’s actually the most important part of the dataflow.

My source file contains 4 rows and 2 columns (testColumnNotFound.csv)

itemno;timestamp
1;2020-12-09 05:47:10
2;2019-01-04 14:55:41
3;2018-02-14 01:51:29
4;2010-03-18 12:20:43

1st query “ : getFile  =

let
  Source = SharePoint.Files(urlAPI, [ApiVersion = 15]),
  #"Sorted rows" = Table.Sort(Source, {{"Name", Order.Ascending}}),
  Navigation = #"Sorted rows"{[Name = "testColumnNotFound.csv", #"Folder Path" = sourcePath]}[Content],
  #"Imported CSV" = Csv.Document(Navigation, [Delimiter = ";", Columns = 2, QuoteStyle = QuoteStyle.None]),
  #"Promoted headers" = Table.PromoteHeaders(#"Imported CSV", [PromoteAllScalars = true]),
  #"Changed column type" = Table.TransformColumnTypes(#"Promoted headers", {{"itemno", Int64.Type}, {"timestamp", type datetime}})
in
  #"Changed column type"

BUG Column not found: getFile query

2nd query : getMax =

let
  Source = #table({"c1"}, {{0}}),
  #"Added custom" = Table.AddColumn(Source, "maxtime", each List.Max(getFile[timestamp]))
in
  #"Added custom"

BUG Column not found: getMax query

Thank you

--mo

[bhalicki]

Hi @molegris ,

Your specification sounds exactly what I am trying to do... and am also hitting the same issue.  I have two queries, one that loads the last import date and another which loads the data.  If I disable one of the queries, everything works fine.  If I reenable, it complains about unable to find a column.  The interesting part is, the error message says it cannot find a column on a table that actually works when only one query is enabled.  I suspect the error message is not reflective of what's actually happening and this is a bug with dataflow/dataverse.

[PHalseyKMS · Accepted solution]

I also had a similar problem, seems to be a bug in dataflow v2 (as my dataflow worked in v1, but stopped working in v2).

I resolved it by only loading copies of the master query (so I ended up with 3 queries instead of 2 copies and leaving the master query set to do not load), looks like the dataflow removes the reference to the column if it is used.

Pretty poor bug if you are trying to load large hierarchical data sets!

[EricRegnier]

Hi @molegris,

I tried it from my side with a CSV file containing the same content. It seems to work fine in the designer, auto-refresh and manual refresh. There were a couple of differences though that I noticed:

• My dataflow version is V2. Not sure of your version, but try to create a blank new Dataflow to have V2?
• My getFile query is a per below. I using the Csv.Document instead of the SharePoint connector

let
Source = Csv.Document(Web.Contents("URL"), [Delimiter = ";", Columns = 2, QuoteStyle = QuoteStyle.None]),
#"Promoted headers" = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
#"Changed column type" = Table.TransformColumnTypes(#"Promoted headers", {{"itemno", Int64.Type}, {"timestamp", type datetime}})
in
#"Changed column type"​

• I converted my getMax query maxtime column to date time type:

let
Source = #table({"c1"}, {{0}}),
#"Added custom" = Table.AddColumn(Source, "maxtime", each List.Max(getFile[timestamp])),
#"Changed column type" = Table.TransformColumnTypes(#"Added custom", {{"maxtime", type datetime}})
in
#"Changed column type"​