Showing results for 
Search instead for 
Did you mean: 

Unable to save a dataflow



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:

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

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

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.


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? 




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 


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):

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


Here's the second query:

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


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


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).


Olivier, thanks for your response.


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



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


Here's a query example that works:

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

 Hi all, 


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


Best Regards,
Qiuyun Yu 

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 


2. remove the table fields manually


We are working on an official fix. 


Thanks for reporting this.