Sunday, June 12, 2022
HomeBusiness IntelligenceFast Ideas: Connecting to Excel Recordsdata Saved in SharePoint On-line from Energy...

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


Connecting to Excel Files Stored in SharePoint Online from Power BI Desktop

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

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

This methodology requires you to have the Excel utility put in in your machine. On this methodology, we open the Excel information 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 observe these steps to make it work:

  1. Choose the Excel file
  2. Click on the Open button
  3. Click on Open in app
Opening Excel file from SharePoint Online in Excel Desktop App
Opening Excel file from SharePoint On-line in Excel Desktop App

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

  1. Click on the File menu
  2. Click on Data
  3. Click on the Copy path button
Copying Excel Path from Excel Desktop App
Copying Excel Path from Excel Desktop App

To this point we received the Excel file path. The step is to get information from the copied path in Energy BI Desktop.

Open Energy BI Desktop and observe these steps:

  1. Click on Get information
  2. Click on Net
Getting data from Excel stored in SharePoint Online in Power BI Desktop
Getting information from Excel saved in SharePoint On-line in Energy BI Desktop
  1. Paste the trail we copied from Excel within the URL textual content field
  2. Delete the ?internet=1 from the top of the copied path
  3. Click on OK
Modifying the path copied from Excel to get the data in Power BI Desktop
Modifying the trail copied from Excel to get the information in Energy BI Desktop

  1. From the Entry Net Content material web page, click on Organizational account
  2. Click on Check in and go your credentials
  3. Click on Join
Passing credentials for an Organizational Account to access Excel date from SharePoint Online in Power BI Desktop
Passing credentials for an Organizational Account to entry Excel date from SharePoint On-line in Energy BI Desktop
Necessary Be aware
Should 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)."
An error raises if we forget to take out the query part from the file path
An error raises if we neglect to take out the question half from the file path

There you might have it. Now you possibly can choose tables for sheets and begin constructing your experiences in Energy BI Desktop.

Navigating the Excel data stored in SharePoint Online
Navigating the Excel information saved in SharePoint On-line

Technique 2: Getting the Excel File Path Straight 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 the exact same state of affairs. The client needed to train his different colleagues the way to use Energy BI to get information from Excel information saved in SharePoint On-line. He thought, going by all of the steps defined within the first methodology is an excessive amount of, and it won’t be simple for his colleagues to soak up it. So we thought to take a look at our choices in SharePoint On-line, that he discovered one thing attention-grabbing. So this methodology is initially found by my buyer, Mr Callum Fraser. Comply with the steps under to get the file path immediately 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 seek out the Path part
  5. Click on the Copy Direct Hyperlink button
Copying Direct File Link from File Details in SharePoint Online
Copying Direct File Hyperlink from File Particulars in SharePoint On-line

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

Getting data from Excel hosted in SharePoint Online with a Direct File Link
Getting information from Excel hosted in SharePoint On-line with a Direct Hyperlink

The advantages of this methodology over the earlier methodology:

  • It requires fewer clicks to get the direct hyperlink
  • The direct hyperlink doesn’t have any question components so we don’t require to change the hyperlink
  • The entire course of is simpler to recollect

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

RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Most Popular

Recent Comments