Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ST126
New Member

Connecting PowerQuery to SharePoint Online Folder AND hidden EXCEL-workingsheets

Hey guys,

 

I have the following setup and I please need some advice here:

 

1. SharePoint Online with Folder which includes 500+ MS-Excel files (.xlsx). Files are uploaded by different users over time and the users can also update the files. For the reporting purposes, only the latest version of the file is relevant.

 

2. These Excel-files contain a workingsheet which contains then a table with data I want to extract (same name and structure of the table throuhout all files). Problem is, that this workingsheet is HIDDEN in all files.

 

3. I want now to connect in PowerBI / PowerQuery to the SharePoint Online folder and use the data from all the hidden workingsheets in MS-Excel files to create my reporting.

 

4. Ultimately, the created dashboards should be embedded back on the SharePoint page, taking also individual reader and access rights of the users into consideration (I have a separate SharePoint list for this).

 

Problem: I can connect to the folder and access the Excel-files BUT I cannot access the hidden workingsheet. I found solutions online for the single, isolated issues (connecting to a folder / accessing hidden workingsheets), but I struggle with the combination of the two...

 

Any advice is highly welcome.

 

Thanks and BR

ST126

 

 

P.S.: As this is my first post here and as I am more a Business- than an IT user, I highly appreciate your patience 😅.

 

 

1 ACCEPTED SOLUTION

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.

View solution in original post

4 REPLIES 4
ST126
New Member

Thanks again @lbendlin . We managed today to successfully connect to the various xlsx-files in the folder and extract the data from the hidden workingsheet.

 

 

lbendlin
Super User
Super User

Do you see the last column in your Power Query ?

 

lbendlin_0-1686188247814.png

 

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:

ST126_0-1686571702069.png

 

2. Open the source, click "transform data", then change in PowerQuery the command line to access the folder structure:

ST126_1-1686571877626.png

3. I then use some filter options and then the "combine"-action:

ST126_2-1686571927634.png

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

ST126_3-1686572018262.png

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors