cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
molegris
Advocate II
Advocate II

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

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 queryBUG 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 queryBUG Column not found: getMax query

 

 

 

 

 

Thank you

--mo

1 ACCEPTED SOLUTION

Accepted Solutions
PHalseyKMS
New Member

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!

View solution in original post

3 REPLIES 3
bhalicki
Frequent Visitor

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
New Member

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!

View solution in original post

EricRegnier
Super User II
Super User II

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"​

 

Helpful resources

Announcements
User Groups Public Preview

Join us for our User Group Public Preview!

Power Apps User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

Power Apps Community Call

Monthly Power Apps Community Call

Did you miss the call?? Check out the Power Apps Community Call here!

Power Platform ISV STudio

Power Platform ISV Studio

ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.

Users online (76,965)