How to Connect an SharePoint Excel Files to Power BI, Connect a SharePoint Folder to Power BI, and Automate Form Responses to Excels hosted in SharePoint
In order to connect an Excel file hosted in SharePoint to Power BI, you will use the following steps.
1. Get Excel SharePoint file link: Navigate to your excel file hosted in SharePoint and grab the link from the file using => share and then press copy link.
2. Use Web Connector in Power BI: Once your link is copied, open up Power BI and go to Get Data, navigate to the web connector, and then paste the link from the copy link from SharePoint into the input for the web connector.
3. Remove the key and :x/r/ from URL: Be sure to delete the "?key" that is generated from the link at the end of the URL string, the only part that should be left in the string at the end is the file path which is .xlsx. You then also need to navigate to the ":x/r/" string that is generated in the beginning of the SharePoint URL right after the SharePoint site link.
4. Sign In and Authenticate on the Lowest Level: Authenticate at the lowest level of the SharePoint link by going to the drop down and selecting the longest path, or the last one available in the list. Authenticate using the Organization Account method which should prompt your Active Directory login. Once the data connection is successful, select the tabs you want to bring in from the excel sheet. Publish the Power BI file to the Power BI web service after you are done building your dashboard on top of the SharePoint excel datasets.
5. Make sure Power BI Dataset is Authenticated in Web Service: Navigate to the dataset settings for the semantic model that you have just published to the web service. Ensure that you are authenticated on the Power BI data source settings for the semantic model.
In order to connect an Excel folder with multiple files hosted in SharePoint to Power BI and combine the files, you will use the following steps in the following documentation.
In order to get the responses to write from a Microsoft form to a SharePoint hosted Excel, you will use the following steps.
1. Navigate to Power Automate or also called Microsoft Flow, you can find this application and all Microsoft apps as https://office.com.
2. Create a Power Automate flow using an instant cloud flow.
3. Choose the when a Microsoft Teams form response is submitted as the first step, navigate to and select the form that you are looking to automate the responses from.
3. For the second step choose the get the form response using the response ID.
4. For the third step choose the add row to Excel to add the response row to the SharePoint hosted excel. Make sure that the excel is the table defined within the excel file that you are using to write to as a named range.
5. Activate your flow and submit a form response to see if the response will write to the SharePoint hosted excel.