Get text box + table from sharepoint in power BI

[Jessica_17]

Hello All,
I want to get multiple excel data which is in sharepoint into 1 table in power BI.

Data structure of all excel file is like this, which has both columns and text box

And in Power BI I want the output like the screenshot below which has both text box and table data of all excel and its tab into one, table in power BI. 

NOTE:- Each excel has multiple tabs too, so we need data of all tabs too

Can anyone please help me on this?

[rohit1991]

hi @Jessica_17 

Unfortunately, Power BI can’t natively pull text directly from Excel text boxes or shapes, whether your files are in SharePoint or anywhere else. It’s a technical limitation that trips up a lot of people.

Here’s what you can do:

1. For all your table data (columns, rows) from all Excel tabs: Power BI and Power Query are great at this! Just connect to your SharePoint folder, combine the Excel files, and pull all the sheets/tables into one table. You can add columns for the file/tab names if needed no problem, and this process is automatable and refreshes on a schedule.

2. For the text box/comment/note info: Power BI (and Power Automate) cannot read data from Excel text boxes or shapes only from actual worksheet cells. If you have any control over the files, the best solution is to put those notes or comments into a cell (maybe above the table or in a hidden “Notes” cell). Then Power BI can grab them just like any other data and combine them as you want.

3. If the text box content must stay as a shape: The only way to automate getting that data is by running a VBA or Python script to extract the text from the box into a cell before your Power BI/Power Automate refresh. After that, your automated Power BI workflow will include everything.

Table/sheet data: fully supported, easy to automate. Text box content: needs to be in a cell for automation. Otherwise, use a script to copy it over before your refresh.

[Jessica_17]

Hi @rohit1991 ,

Text box is not inside a cell, can we automate the process of copy pasting process of text box into cell, as there are so many excel sheet + tab which cannot be done manually and its should be done monthly basis.

[danextian]

Hi @Jessica_17 

To connect to multiple files, you'll need to use the SharePoint folder connection. While the Query Editor's GUI includes a "combine and transform" feature, it is limited to a single tab in a workbook, making it unsuitable if you need to connect to multiple tabs. I have a YouTube tutorial demonstrating how to use the folder connection for this purpose. The SharePoint folder connection works similarly to a regular folder connection, with the main difference being that for SharePoint, you must connect to the site's base URL and filter the path to the desired folder, as direct folder connections are not an option. After that, you can transform a single tab and apply those transformations across all tabs and workbooks (covered in the tutorial).

Things that are not possible:

• Power BI cannot retrieve data directly from textboxes in Excel. It only supports data extraction from Sheets, Tables, and Defined Names, not from other objects such as textboxes.
• When row categories in a hierarchy are merged at a higher level, lower-level categories are displayed as itemized entries relative to the levels above or below, and it appears that below screenshot will become part of the value area and merging of different cells is not possible.

[Jessica_17]

Hi @danextian 
Thanks for the reply, but is their any even using power automate too, to make this scenrio feasible? and for col1 and col2, we can have same values in multiple rows instead of combining, is that feasible? Like the screenshot below.

[danextian]

The look of your table is feasible but the data from the textbox is not. You will need to use VBA to get the data from the textboxes and save them in the worksheet.

[Jessica_17]

HI @danextian 

Thanks for replying.

[AmiraBedh]

Can you please provide the input excel file in Google Drive ?

[Jessica_17]

HI @AmiraBedh 

Here is the input file and output in the same excel, please let me know if you can access it.
https://drive.google.com/drive/folders/1oQyFknDX9c5lA-ANcrCUPuMWk577ix0q?usp=sharing
Thanks

[AmiraBedh · Accepted solution]

1st step you can use VBA to get the value of the textboxes to cells :

https://stackoverflow.com/questions/21673105/get-the-value-of-a-textbox-to-a-cell

[Jessica_17]

HI @AmiraBedh ,

Thanks for the reply, but is there any solution in Power BI or power automate itself? which can be automated on monthly basis without any human intervention.