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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Jessica_17
Helper IV
Helper IV

Get text box + table from sharepoint in power BI

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

Jessica_17_1-1737233750147.png

 

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

Jessica_17_2-1737233770631.png

 

Can anyone please help me on this?

 

 

 

1 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

 


Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696

View solution in original post

10 REPLIES 10
rohit1991
Super User
Super User

hi @Jessica_17 

To combine text box and table data from multiple Excel files and tabs in SharePoint into Power BI:

  1. Connect to SharePoint Folder: Use Get Data > SharePoint Folder in Power BI. Load all Excel files.

  2. Process Excel Files:

    • Filter .xlsx files and expand the Content column.
    • Use Combine Files to process all tabs and load table data.
  3. Handle Text Boxes:

    • If text box data is in cells, include the relevant sheet.
    • If text boxes aren’t stored in cells, copy their content to a sheet manually for automated processing.
  4. Merge Data:

    • Append all tables, adding columns for file and sheet names if needed.

This approach consolidates text and table data into one Power BI table.

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
Super User
Super User

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.

 

danextian_0-1737246786382.png

 










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

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.

Jessica_17_0-1737273813881.png

 

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.










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

HI @danextian 

Thanks for replying.

AmiraBedh
Super User
Super User

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


Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696

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

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

 


Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696

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.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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