Combining tables causing refresh to fail in Power BI Service

[cmncp]

Background

I have some excel files hosted in on-premise sharepoint folder.  There is one file for each country, and the contents of each file are the same.  I want to combine the contents into a single table.  In order to enable the gateway to refresh the data, I need to create individual queries for each file, using the "Web" data connector type.  If I do this for each file, without combining the data, it works fine, and I can refresh the datasource in the Power BI Service (using the gateway).

Problem

As soon as I combine the files into a single table (using Table.Combine), the refresh fails, giving me the super helpfull error "There was an error in the data gateway.".

According to the recent "Planning a PowerBI Enterprise Deployment" whitepaper published by Chris Webb, complex M queries can cause issues with data refresh.  But I wouldn't have thought that a simple Table.Combine would be considered complex.

Has anyway experienced a similar problem?  Is there a workaround any knows of?

[Anonymous]

Hi

Had the same problem. All sources worked well (SSAS+SQL+Excel from Sharepoint folder) until I combined. The solution was to enable the "Allow user's cloud data sources to refresh through this gateway cluster" on the gateway. 

[GilbertQ]

Hi @cmncp

Why don't you rather get the files from a Folder? In SharePoint you can also get to Document Libriaries via a UNC or Folder location.

Then once you have done that, when you are configuring your Gateway, you can point it to the same folder location? As well as ensure that the Service Account that the Gateway Service runs under, also has read permissions to your SharePoint Document libriary.

I have done this successfully in the past and it works really well.

[cmncp]

Hi @GilbertQ.

Thanks for the reply.  Were you using SharePoint online or on-premise.  In my experience, getting files from a folder did not work with on-premise SharePoint.

[GilbertQ]

Hi @cmncp

I have done this using On-Premise SharePoint. 

I have a blog post below, in which you can follow the steps to get it working.

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

[cmncp]

@GilbertQ  Very good blog post.

I am having issues trying to set up the gateway.  It is telling me the credentials are invalid.  I am not the owner of the SharePoint site, but I do have access.  In fact I created the folder and copied the files into it.

[GilbertQ]

Hi @cmncp

Thanks for the comments on the blog post.

I would suggest looking to see what LogOn Account the Power BI Gateway Service is using on the Server.

Once you got the domain account, then add this same domain account into your SharePoint Document Libriary Permissions. If this Domain Account is not in there it will then fail when trying to Add it as a Data Source in the On-Premise Gateway.

Also when you installed the On-Premise Gateway did you install it in Personal Mode or Enterprise mode?

[cmncp · Accepted solution]

@GilbertQ  Just wanted to let you know I was not able to get it working using the Folder option.  I was unable to resolve the access errors.  However, I was able to get it to work using the Sharepoint Folder option. They key there is to initially select the SharePoint Site, and then apply to filter to select the individual folder that has my files.

Thanks again for your suggesitons.