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

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
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Users online (2,883)