Forum Discussion
Connecting PowerQuery to SharePoint Online Folder AND hidden EXCEL-workingsheets
Step 3 is your culprit. Don't do that. Instead create a custom function that extracts all required sheets from a given Excel file. You then have to combine these as the return value of the function. And finally run that function across all excel files and expand the added column.
So to recap - you need to combine all the relevant sheets inside all the relevant excel files.
Do you see the last column in your Power Query ?
Hello lbendlin ,
thanks for your answer.
The issue I have that I can open a single file in the folder and also access the data in the hidden workingsheet OR I can parse the folder and access all files but then I am not able to open the hidden workingsheet.
These are the steps I did:
1. Get Data from the SharePoint online folder:
2. Open the source, click "transform data", then change in PowerQuery the command line to access the folder structure:
3. I then use some filter options and then the "combine"-action:
4. I do get this view, but cannot access the hidden workingsheet (also after just opening one workingsheet being visible to me, I still do not know how to open the hidden one):
5. Alternatively, before using the "combine"-action, I can open one single file and in there I can see the hidden tab and also access it (but then I do not how to execute this particular step for ALL files in the folder...)
THANKS!
Step 3 is your culprit. Don't do that. Instead create a custom function that extracts all required sheets from a given Excel file. You then have to combine these as the return value of the function. And finally run that function across all excel files and expand the added column.
So to recap - you need to combine all the relevant sheets inside all the relevant excel files.