cancel
Showing results for 
Search instead for 
Did you mean: 

Unable to save a dataflow

image.png

 

I have seven queries inside the dataflow, created exactly in this order:

  1. A: Reading Excel workbook (loading disabled)
  2. B: Doing some transformations to query A (loading disabled)
  3. C: Doing other transformations to query A (loading disabled)
  4. D: Applying function E to query B and using query F (loading enabled)
  5. E: Custom function (loading disabled)
  6. F: Data entered by hand (loading disabled)
  7. G: Applying function E to query C and using query F (loading enabled)

I see the preview of all queries, but I am unable to save the dataflow (see the screenshot above).

 

Here's what I tried:

  • Disabled loading of queries D and G and only loading query F -- got the same message
  • Deleted queries D and G and tried to load query F -- still the same message
  • Deleted query F -- same message
  • Deleted queries B and C -- same message
  • Deleted query A (only query F left at this point) -- finally it saved all right

 

I added back query E -- the dataflow saved all right. Then I added back query A and it failed to save again.

 

Here's the M script of query A:

let
    Source = Excel.Workbook(File.Contents("********.xlsx"), null, true)
in
    Source

I know for a fact that my gateway can access this file because I am using queries A-G in my .pbix file, which refreshes all right.

 

Finally, I tried to play with the third argument of Excel.Workbook -- setting it to false or null did not help, unfortunately.

 

What am I doing wrong?

 

P. S. There should be an Idea Label on this forum for dataflows and apps (there is one for Content Packs but they're deprecated!).

Status: Accepted
Comments
Member

Okay it turned out that indeed dataflows don't like queries like my query A above. When I removed it and instead pointed queries B and C directly to the source (Excel), everything worked. I don't think this is supposed to happen though -- having staging queries like query A is a perfectly valid business scenario.

Moderator

Hi @Daniil,

 

It looks like the Query B and C links to the Query A, which feature do you use to create query B and C, Reference or Duplicate? 

 

a1.PNG

 

As there are many queries, if there are only three queries (like below), does the same issue occur? 

 

  1. A: Reading Excel workbook (loading disabled)
  2. B: Doing some transformations to query A (loading disabled)
  3. C: Doing other transformations to query A (loading disabled)

By the way, please update the on-premise data gateway to the latest version to avoid potential issue. 

 

Best Regards,
Qiuyun Yu 

Member

My apologies for the poorly-worded issue -- I should have just talked about query A.

 

I've installed the latest gateway (3000.0.155.1), and I still cannot create a dataflow that contains a query like query A.

 

Here's a simplistic example of a dataflow:

  1. Connecting to an Excel file without picking any item specifically -- just listing contents. Loading disabled.
  2. An independent query that contains a list of numbers -- loading enabled.

Here's the code of the first query (path to the file anonymized):

let
    Source = Excel.Workbook(File.Contents("********.xlsx"), null, true)
in
    Source

 

Here's the second query:

let
  Source = {1..10},
  #"Converted to table" = Table.FromList(Source, Splitter.SplitByNothing(), type table [Num = number], null, ExtraValues.Error)
in
  #"Converted to table"

 

This dataflow cannot be saved -- the error is the same as in the original post.

Member

I ran into a similar error while trying to retrieve files from SharePoint, and I don't think the issue in your case like mine is because we're using staging queries. Rather my theory is that Dataflows (currently?) doesn't like queries that end up with loading a file as their final output (while processing files during the PQO process is fine, provided the final output is a table or list).

Member

Olivier, thanks for your response.

 

I've managed to pinpoint the issue: columns that contain tables:

 

image.png

If you remove this column, the dataflow can be saved successfully.

 

Here's a query example that works:

let
  Source = Excel.Workbook(File.Contents("********.xlsx"), null, true),
  #"Removed columns" = Table.RemoveColumns(Source, {"Data"})
in
  #"Removed columns"
Moderator

 Hi all, 

 

I have reported this issue internally: CRI 94779994. Will keep you update once I get any information. 

 

Best Regards,
Qiuyun Yu 

Moderator
Status changed to: Accepted
 
Frequent Visitor

Hi Daniil, we have started investigating this bug internally, its indeed as you said a case where fields which contain tables are not being removed as part of validation and are preventing saving the model.

 

The workaround currently is to:

1.  not disable load of the query 

or 

2. remove the table fields manually

 

We are working on an official fix. 

 

Thanks for reporting this.