Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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:
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.
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:
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.
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:
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.
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?
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:
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.
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.
Update:
Also, tried to load file with 'SharePoint Folder', however, an error '
@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!!👍
@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?
@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!!👍
@Anonymous Yes I am using link correctly as you suggested
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
50 | |
18 | |
16 | |
16 | |
8 |