Power BI – Configuring SharePoint/Folder refreshing using On-Premise Gateway

I had a requirement to connect and refresh their files stored in SharePoint On-Premise using the On-Premise Gateway. And are the steps that you can follow in order to connect to files stored within SharePoint On-Premise or files stored in Folders.

In my working example below I am going to connect to a SharePoint Folder. You can apply the same steps for a Folder as well.

How to find the SharePoint Folder for Windows Explorer

I am not sure if people are aware, but you can actually connect to SharePoint Files and Folders using your Windows Explorer. The trick I am going to show below is how to find the correct URL.

Typically when files are stored within SharePoint the SharePoint Administrator will create a document library. If you cannot see the details below please confirm with your SharePoint Administrator that where you are looking is indeed a Document Library.

NOTE: For this to work you HAVE to use Internet Explorer

  • I went to the location where I had my files stored.
  • Then in the ribbon on the top I clicked on LIBRARY
  • Now if you look across the ribbon, under the section Connect & Export there will be a button which says Open with Explorer, click on this.
  • This will then open in Windows Explorer
  • Now if you look above you will see that the notation is a web based notation.
  • All that you now need to do is to change this to use a UNC notation.
  • So with my example I changed it to the following.
    • So my UNC was:
      • \\SP_Corp.domain.com\business\fg-bi-hub\Program Dashboards
    • If you see above I have changed it from the https:// to the double backslash \\
    • And then I have changed it from the forward slash / to the backslash \
  • Now you have the folder location of where your files are stored.

Connecting to your files using Power BI Desktop

In the steps below I will demonstrate how I connected to my files using Power BI Desktop.

The steps below are critical if you want to ensure that the On-Premise Gateway
refresh will work later.

  • I opened my Power BI Desktop file and clicked on Get Data
  • Now the critical part
    is right at the start, because in order for this to work I HAD to select Folder
    as shown below.
    • NOTE: If I did not select Folder (I did not with my initial testing) then when it comes to the refreshing I got an error which I will explain later. (Related to the Gateway setup for the Data Source)
    • I clicked on Connect
  • Next I then selected my folder as with my example above.
    • \\SP_Corp.domain.com\business\fg-bi-hub\Program Dashboards
    • Then I clicked OK.
    • If I did not put in the UNC Folder Path, I got the following error
  • When the window opened I clicked on the Edit button
    • The reason that I did this is because I still wanted to select my individual file from within the folder.
  • This then opened the Query Editor.
  • What I did next was to select the file that I wanted, in my example it is the file called Calculated Columns Data.xlsx
    • I did this by right clicking on the file in the Name Column and selecting
      Text Filters and then Equals
    • This then filtered my Folder to only show the file that I wanted.
  • Next I clicked on the Combined Files Button
    • This then opens the Combined Files Window.
  • As with my example I selected the Table that contained the data I required. I selected Table1
    • Then clicked OK.
  • Once loaded I then got my data loaded into my Table in the Query Editor
  • Now I modified my table and did what I required to get it into the required format.
    • NOTE: For those interested what I found was key to making sure the refresh work was by going into the Advanced Editor and looking at how the data was imported.
    • It is that it has to have Folder.Files, if it is anything else then the refresh in the On-Premise Gateway in the Power BI Service WILL fail.
  • I then clicked Close & Apply to load this to my Power BI Desktop Model.
  • I then created a table so that I can see my required data.
  • I then uploaded this to the Power BI Service.

Creating the Data Source in the Gateway on the Power BI Service

Next I had to create the related Data Source in the Gateway section of the Power BI Service.

NOTE: You need to be an Admin in the Power BI Service to complete the steps below, if you are not, then ask your Admin to configure the steps below.

NOTE II: I had already installed and configured the On-Premise Gateway within the domain I am working in. You can reference this installation
guide: On-premises data gateway

  • I logged into the Power BI Service and then clicked on Manage Gateways
  • I then clicked on Add Data Source
  • Next I added my Data Source with the details of my example below.
    • As you can see I had to choose the Data Source Type as a Folder.
    • This is so that the connection will be made from my Power BI uploaded Dataset with the Gateway in the Power BI Service.
    • I had to also ensure that the Windows Username and Password that I used in this Data Source, also had the correct permissions in the SharePoint Document Library.
      • In the Organization I was consulting, they had the following user setup in the SharePoint document library
      • This is the same Windows username and password that I used to configure the Data Source.
    • I then clicked Add
  • Now the Gateway configuration is complete.

Connecting my dataset to the Gateway in the Power BI Service

The final step I did was to connect my dataset to the Gateway in the Power BI Service and make sure that it refreshes.

  • I went into the Power BI Service.
  • Then clicked on Settings and selected Settings
  • Then I clicked on Datasets
  • I then went to my dataset that I had previously uploaded, where I wanted to connect to it using the Gateway connection.
    • I then expanded the Gateway connection
    • Now I had the option to Use a data gateway
    • And in here I selected my Gateway that I had previously configured.
    • I then clicked Apply.
  • I got the following confirmation.
  • Then the final step was to test that it was all working and I did this by going into the Datasets and clicking Refresh now
  • Once that had completed I went back into Settings, then
    Datasets and clicked on my dataset.
    • Then at the top I clicked on Refresh History

Error – Could not find the file

What happened is previously I had not configured everything as per my steps above so I got the following error below when trying to refresh.

As in the past the error messages can be a bit cryptic. But what I learnt was that for this particular dataset that I was using I was NOT connecting use the Folder.Files, but rather the File.Contents

And now looking at the above it became clearer to me as to why it was failing.

The issue is that when I added the Data Source for the Gateway in the Power BI Service, I was selecting the Folder as a Source, but in my Power BI Desktop connection I was connecting use a File and NOT a Folder.

As soon as I changed the above to a Folder the refresh worked.

Conclusion

What I have demonstrated in the steps above is how to connect to files
stored in SharePoint or in a Folder. As well as how to resolve if you get an error.

If you have any comments or suggestions please leave them in the area below.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s