Forum Discussion

cmncp's avatar
cmncp
Helper III
9 years ago
Solved

Combining tables causing refresh to fail in Power BI Service

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?

Need Help
  • cmncp's avatar
    cmncp
    9 years ago

    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.

7 Replies

  • GilbertQ's avatar
    GilbertQ
    Super User
    9 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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.