Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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?
Solved! Go to 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
hi @Jessica_17
To combine text box and table data from multiple Excel files and tabs in SharePoint into Power BI:
Connect to SharePoint Folder: Use Get Data > SharePoint Folder in Power BI. Load all Excel files.
Process Excel Files:
Handle Text Boxes:
Merge Data:
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.
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:
Proud to be a Super User!
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.
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.
Proud to be a Super User!
Can you please provide the input excel file in Google Drive ?
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
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
145 | |
79 | |
64 | |
52 | |
47 |
User | Count |
---|---|
217 | |
89 | |
76 | |
67 | |
60 |