Loading excel files in Power BI desktop

[PushkarP]

Hello,

I want to load data in few excel files into Power BI desktop. The excel files are store on SharePoint. To import the data I opened the SharePoint in Windows explorer and browsed the excel files. The data load was successful and I publsed the report to My workspace. However, while trying to referesh, I was prompted for a Data Gateway. I was expecting Data Gateway is not required as excel files are on SharePoint. Due to some constraints I cannot use a Data Gateway. Now I want to know.

1. Is there a way to configure so that the Data Gateway is not required?

2. Is there any other way (than using Windows Explorer) to get data from excel files, which are stored on SharePoint, in Power BI desktop?

Please advise.

Thanks in advance!

Regards,
Pushkar

[v-robertq-msft · Accepted solution]

Hi, @PushkarP 

According to your description, I think you maybe make some mistakes when getting data, you can follow my steps and try again:
1. Go to your Sharepoint page, copy the main URL, like this:

2. Get data->Sharepoint folder, then sign in with Microsoft account:

3. Transform data, select the file and table you want:

4. Create the report and publish, you can go to the setting of the dataset, it says you don’t have to use the gateway to refresh this dataset(because data sources are in the cloud)

You should edit the correct credentials, then you can set scheduled refresh.

I guess that the main reason that the dataset needs a gateway to configure Scheduled refresh is using separate mashup queries to connect to on-premises and cloud sources.

If you just connect to files from Share point Online, you won’t need a gateway to configure Scheduled refresh.

Review data infrastructure dependencies

More info about connecting to Sharepoint on desktop

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-robertq-msft · Accepted solution]

Hi, @PushkarP 

According to your description, I think you maybe make some mistakes when getting data, you can follow my steps and try again:
1. Go to your Sharepoint page, copy the main URL, like this:

2. Get data->Sharepoint folder, then sign in with Microsoft account:

3. Transform data, select the file and table you want:

4. Create the report and publish, you can go to the setting of the dataset, it says you don’t have to use the gateway to refresh this dataset(because data sources are in the cloud)

You should edit the correct credentials, then you can set scheduled refresh.

I guess that the main reason that the dataset needs a gateway to configure Scheduled refresh is using separate mashup queries to connect to on-premises and cloud sources.

If you just connect to files from Share point Online, you won’t need a gateway to configure Scheduled refresh.

Review data infrastructure dependencies

More info about connecting to Sharepoint on desktop

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PushkarP]

@v-robertq-msft 

I did try using share point folder connection and followed the steps you suggested. An error occured.

What could be the possible reason of this?

[v-robertq-msft · Accepted solution]

Hi, @PushkarP 

I suggest you go to the step menu to find what's the original cause of this problem, and try to follow these links to fix it:

https://community.powerbi.com/t5/Desktop/Expression-Error-The-key-didn-t-match-any-rows-in-the-table... 

https://community.powerbi.com/t5/Desktop/Expression-Error-The-key-didn-t-match-any-rows-in-the-table... 

https://community.powerbi.com/t5/Desktop/how-to-resolve-Power-BI-error-the-key-didn-t-match-any-rows... 

If you still have a problem, please show more details.

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@PushkarP 

 ensure all tabs in worksheet are named EXACTLY as in previous updates. 

I received the same message when updated a datasource after three months of successfully updating same.  Expression Error: The key didn't match any rows in the table.

My data sourse is a monthly updated xlsx file.  My issue was resolved by ensuring all tabs in worksheet were named EXACTLY as in previous updates.   One of the tabs in the data had an underscore added.  the underscore did not exisit in previous updates. updated the spreadsheet tab resolved the keky didnt match message.

[PushkarP]

Update:

Also, tried to load file with 'SharePoint Folder', however, an error '

Test Table

Failed to save modifications to the server. Error returned: 'OLE DB or ODBC error: [Expression.Error] The key didn't match any rows in the table.. '., occured.

[Anonymous]

@PushkarP  you can make use of Web connector in power bi to connect the files from sharepoint....Also if you find that your sharepoint is onpremise which will need gateway configuration ...To meet your requirement you can paste the excel file to OneDrive  which will not need gateway configuration

Hope it resolves your issue? Please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos, Press the thumbs up button!!👍

[PushkarP]

@Anonymous Thanks for your reply. I did try with web connector. I got following message when tried to authenticate with organisational account.

I can access the site using browser so it is not an access issue. Can you please guide?

[Anonymous]

@PushkarP When you try to connect using web connector and with sharepoint online you will have to follow the below steps
1)Open sharepoint Click on the three dots and copy the excel file link 


2)the link will be somewhat like https://sampledata/:X:/R/Gatewaytest1.xlsxd=w6b9b7254680240c297f64b53f87f1091&csf=1&web=1&e=nm5gwO 
3)Remove the bold letters from the link and copy the revised link while connecting to web Power bi
https://sampledata/Gatewaytest1.xlsx-->your link should look like this

Hope it resolves your issue? Please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos, Press the thumbs up button!!👍

[PushkarP]

@Anonymous  Yes I am using link correctly as you suggested

[Anonymous]

@PushkarP  First would like to check if the sharepoint which you are using is online or onpremise...For online there is no need of gateway and for onpremise you need a gateway