Unable to save a dataflow

[Daniil]

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

[Daniil]

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.

[v-qiuyu-msft]

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 

[Daniil]

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.

[otravers]

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

[Daniil]

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:

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

[v-qiuyu-msft]

 Hi all, 

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

Best Regards,
Qiuyun Yu 

[sagivh]

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. 

[quyenduong]

Hello @sagivh,

I have a data flow connecting from weeky xlsx data files. Then I build a dataflow for these data. Whenever I do some transformation steps in the data flow (create new columns, change data type, extract characters from one column to create a new columns, change columns order, etc.), the table that I connect with Power report (Desktop) shows an empty table.

Now, I leave data flow without any transformation and connect it to datamart: I transform the data in datamart. It also cannot save and show some error message 

When I leave dataflow without any transformation and then connect data flow into power bi desktop, then transform all data in desktop and it works.

Do you know why it is like that? We cannot transform data in data flow or data mart?? Thank you for answering