The Excel Connectors in Power BI

Like it or not, Excel is one of the most popular data sources to build Power BI reports. Surprisingly, except for when you want to connect to an Excel file that is located on your local laptop or a shared network drive, there is no easy way for users to connect to an Excel file that is hosted on cloud services via the Power BI’s user interface (UI).

This blog post will provide you with a universal M script, which you can use to connect to Excel files hosted on not only on-prem sources but also cloud environments, such as OneDrive for Business, SharePoint, and Google Sheets (both public and private). You can download the pbit file in my GitHub repository for a quick start.

THE OUT-OF-THE-BOX EXCEL CONNECTOR

Excel is so much popular that at the start page of Power BI, there are even 4 different ways for you to import an Excel file (see image below).

If you look at the M script in Power Query, the automatically generated M script starts with 2 functions of Excel.Workbook() and File.Contents(). Things will go well as you can still connect to a local Excel file, clean it in Power Query, create beautiful reports in Power BI, and use it for yourself. However, when you want to share the report with your colleagues on Power BI Service, or you want your colleagues to collaborate on editing the source Excel file, you may get into the trouble of installing the on-premises data gateway so that the Power BI Service up on the cloud can talk with the data source down on-premise.

This data gateway can be a real problem if you don’t have admin rights, and can’t install it on an on-prem server. To get rid of this unnecessary trouble, hosting Excel files on the cloud is a no-brainer. However, it’s not obvious how to connect to an online Excel in Power BI. This blog post is a one-stop place, where you can get the universal code, and instructions on where to get the paths for the 6 most popular hosting places of Excel: local machine, shared network drive, OneDrive for Business, SharePoint, Google Sheets (both private and public).

Basically, to import the Excel files in those environments, you need to:

1. Copy & paste the universal M script in your blank Power Query;
2. Find the Excel path, then replace it with the bolded InputYourExcelPathHere part in the M script;
3. Adjust the Permission setting;
4. Load the target worksheet, and continue with your data transformation.

Copy & paste THE universal M SCRIPT

The explanation for this M script can be found in the description of each step of the script. However, for a quick start, you can open Power BI Desktop, then choose “Blank query” as in the screenshot below.

Next, you right-click on the query and choose “Advanced Editor“.

Then copy the code below and paste it in the Advanced Editor window. Please note the bold “InputYourExcelPathHere” in the code, which you need to replace later (see the next step for instructions).

let
  prExcelPath
    = "InputYourExcelPathHere",
  Source =
    if Text.Contains(
      prExcelPath,
      "https://docs.google.com/spreadsheets/d/"
    ) //Google Sheets is different than other cases                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
    then
      try //try with the public Google Sheet first
        Excel.Workbook(
          Web.Contents(
            "https://docs.google.com/spreadsheets/d/"
              & Text.BetweenDelimiters(prExcelPath, "/", "/", 4, 0)
              & "/export?format=xlsx&id="
              & Text.BetweenDelimiters(prExcelPath, "/", "/", 4, 0)
          ),
          null,
          true
        )
      otherwise //if there is error in the previous step, use the Beta GoogleSheets connector
        GoogleSheets.Contents(prExcelPath)
    else
      Excel.Workbook(Web.Contents(Replacer.ReplaceText(prExcelPath, "?web=1", "")), null, true) //use Web.Contents function for other cases, even the local files
in
  Source

This should look something similar to this.

Now, just leave the Advanced Editor window open there and find the path of the Excel file as instructed below.

Find the Excel path and replace

I organize this part into 3 subgroups based on their similarities: On-prem, Microsoft, and Google. If you have found the path, just copy it and jump to the “Adjust the Permission setting” part.

On-prem: LOCAL MACHINE AND SHARED NETWORK DRIVE

As mentioned above, connecting to Excel hosted on-prem is not recommended because of the gateway hassle. However, if you just want to test the local Excel first, and will upload that file online for data refresh and collaboration purposes later, then this article is just for your use case. The universal M script can also be helpful in the situation where you have separate Development and Production environments (or the whole set of Development-Test-Acceptance-Production). Changing the value of parameter prExcelPath helps you switch easily between these environments.

You can find the Excel path easily in Windows Explorer. Please remember to add “\” and your Excel file name with the extension (.xlsx or .xls) at the end. The pattern of the final Excel path should be like this:

"driverName:\folderIfAny\subFolderIfAny\fileName.xlsx" --for local machine
"\\folderIfAny\subFolderIfAny\fileName.xlsx" --for shared network drive.

The dirverName can be C, D, or whatever your driver name is. There can be spaces in the path.

Micrsoft: OneDrive for Business AND Sharepoint (FILES HOSTED ON MICROSOFT TEAMS)

Either the Excel file is in OneDrive for Business or in the Files tab of Microsoft Team (which is actually hosted in SharePoint), you need to open them in the Excel app in Desktop. In the “File” tab, choose “Info“, then click “Copy path” like the screenshot below.

When you open “File” tab in the Excel app on Browser or Microsoft Teams, choose “Info“, click “Open in Desktop App” as an alternative way to open that Excel file in Desktop App, as in the screenshot below.

Please also notice that, unlike common belief, you cannot copy the URL of the Excel file in the browser or click “Copy link” in Microsoft Teams to connect to Power BI. That URL is not the correct one to use. For OneDrive for Business, the copied path should have the following pattern:

https://yourOrganizationName-my.sharepoint.com/personal/your_email_com/Documents/folderIfAny/subFolderIfAny/fileName.xlsx?web=1

And for the SharePoint file:

https://yourOrganizationName.sharepoint.com/sites/yourTeamsName/Shared%20Documents/General/folderIfAny/subFolderIfAny/fileName.xlsx?web=1

You may wonder why there is “sharepoint” in OneDrive’s path. It’s because OneDrive for Business uses SharePoint technology under the hood. Just replace the link with the “InputYourExcelPathHere” in the M script provided above. Please be informed that you don’t need to remove the “?web=1” part as it’s already taken care of in the M script.

Google: GOOGLE SHEETs

After years of waiting, there is already now Google Sheets connector in Power BI! In the past, the only way to connect to Google Sheets from Power BI was first to share that Google Sheet publicly. More discussion will follow in the next part, but it may sound scary as you may not want your data, by all means, to be viewed by unauthorized people.

Although the new Google Sheets connector is still in Beta version, after some tests, it seems to be quite reliable. If you have any trouble with it, I’m curious to know more. The great thing about this connector is that it connects to both public and private Google Sheet.

• For the public Google Sheet: the Beta connector still asks you to have a Google account to connect. The workaround has been included in the provided universal M script so that you don’t need to have a Google account!
• For the private Google Sheet: you still need to sign in with the account that has been shared. But that’s understandable to prove that you are an authorized person to view that Excel file.

Anyway, you don’t need to worry about the details. The only thing you should know is what the URL should look like. The M script may not work if the URL doesn’t have the following pattern.

https://docs.google.com/spreadsheets/d/idNumber/editXXXYYYZZZ

Just copy and replace the URL with the “InputYourExcelPathHere” in the M script provided above.

Adjust the Permission setting

When you click “Done” to close the Advanced Editor window, you will see this warning. You need to click “Edit Credentials” to choose the appropriate permission to connect to the Excel file.

As in “Find the Excel path and replace” part, I also divide this into 3 subgroups. So just jump directly to the subgroup which relates to your situation.

On-prem

You should be able to view Excel file from your local computer. Otherwise, you will see this error.

Please try to fix the permission until you can view that Excel file on your computer first. When you have access, after closing the Advanced Editor, there should be no more error.

Micrsoft

You should choose “Organization account” and sign in with your company’s account, which has access to that Excel file, and click “Connect“.

GOOGLE

public link

If you see this window, your Google Sheet is public and can be seen by anyone on the internet. That’s why the “Anonymous” option is enough in this case. Please think of changing it to private necessary.

PRIVATE LINK

If you see the window below, your Google Sheet is secured and you need to sign in with your own Google account to connect to it.

Load the worksheet

If all steps above are correctly applied, here comes the most enjoyable step! You just click the “Table” in the Data column to choose the worksheet that you want to load as the screenshot below. Then just continue with your data transformation in Power Query Editor.

tHE pbit tEMPLATE FILE

For your convenience, you can also download this pbit file from my GitHub repository. Here is the link.
Enjoy!