I have seven queries inside the dataflow, created exactly in this order:
- A: Reading Excel workbook (loading disabled)
- B: Doing some transformations to query A (loading disabled)
- C: Doing other transformations to query A (loading disabled)
- D: Applying function E to query B and using query F (loading enabled)
- E: Custom function (loading disabled)
- F: Data entered by hand (loading disabled)
- 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!).