Connecting to Excel Recordsdata Saved in SharePoint On-line from Energy BI Desktop

Microsoft Excel is among the most typical knowledge sources for Energy BI. We are able to retailer Excel recordsdata in numerous storage varieties. The best way we get knowledge from Excel varies relying on the storage kind. On this publish, I shortly present two strategies to hook up with an Excel file saved in SharePoint On-line.

Methodology 1: Getting the Excel File Path from the Excel Desktop App

This technique requires you to have the Excel utility put in in your machine. On this technique, we open the Excel recordsdata saved in SharePoint On-line within the Excel Desktop App in our machine and get the file path from there.

In SharePoint On-line go to the specified doc library then comply with these steps to make it work:

1. Choose the Excel file
2. Click on the Open button
3. Click on Open in app

This opens the Excel file within the Excel Desktop utility. Within the Excel comply with these steps:

4. Click on the File menu
5. Click on Data
6. Click on the Copy path button

To date we bought the Excel file path. The step is to get knowledge from the copied path in Energy BI Desktop.

Open Energy BI Desktop and comply with these steps:

7. Click on Get knowledge
8. Click on Internet

9. Paste the trail we copied from Excel within the URL textual content field
10. Delete the ?net=1 from the tip of the copied path
11. Click on OK

12. From the Entry Internet Content material web page, click on Organizational account
13. Click on Register and go your credentials
14. Click on Join

Necessary Word
In the event you miss step 10, you'll get the next error:
Particulars: "The enter URL is invalid. Please present a URL to the file path on SharePoint as much as the file title solely (with no question or fragment half)."

There you’ve got it. Now you possibly can choose tables for sheets and begin constructing your studies in Energy BI Desktop.

Methodology 2: Getting the Excel File Path Immediately from SharePoint (with out opening the file within the Excel desktop app)

Final week I had a session with one in all my prospects going by way of the exact same situation. The shopper needed to train his different colleagues tips on how to use Energy BI to get knowledge from Excel recordsdata saved in SharePoint On-line. He thought, going by way of all of the steps defined within the first technique is an excessive amount of, and it won’t be straightforward for his colleagues to soak up it. So we thought to have a look at our choices in SharePoint On-line, that he discovered one thing fascinating. So this technique is initially found by my buyer, Mr Callum Fraser. Observe the steps beneath to get the file path straight from SharePoint On-line:

1. Choose the Excel file
2. Click on the ellipsis button
3. Click on Particulars
4. Within the Particulars pane, scroll down within the pane to search out the Path part
5. Click on the Copy Direct Hyperlink button

In Energy BI Desktop, simply click on Get Knowledge, then choose Internet and paste the hyperlink within the URL textual content field.

The advantages of this technique over the earlier technique:

• It requires fewer clicks to get the direct hyperlink
• The direct hyperlink doesn’t have any question elements so we don’t require to switch the hyperlink
• The entire course of is less complicated to recollect

As at all times, I want to know your opinion. Have you ever used these strategies earlier than? Have you learnt a greater method to get the information from Excel recordsdata saved in SharePoint On-line? Please share your ideas within the remark part beneath.

Associated