I am taking advantage of the Fabric trial at the moment and been trying to play around with Dataflow Gen2 and how we can use this for our power query needs.
We have a folder on Sharepoint where multiple files sit - these are .xlsm files with VBA code in them and each file has a worksheet with a data table. By default this sheet is hidden from all users views unless they have been given access through VBA.
In Power BI Desktop when we connect and publish this query I am able to use the Combine Files function in query editor and it identifies the hidden sheet for me to pull in. I can then apply transformations to the same file and it repeats this for all other files - this has been working without any issues.
However, in Dataflow Gen2 query editor I cannot seem to see this sheet when I try to Combine Files. If I open the individual file in query editor I can see this file but this is not ideal because I need to combine all files collect all the data in the tables in these .xlsm files.
Another interesting thing is I set the sheet to visible in one file and when I pull that in via Combine Files it works and applies this to all other files but this is still not ideal because there is one file that has this sheet visible.
Am I missing something here?
