Forum Discussion
Excel sharepoint data not available on Power BI Service
Hello everyone,
I have an issue when connecting to an excel in sharepoint. Usually it works fine but for some reason it is not working now.
I connect to an excel file on sharepoint to gather historical data, then I connect to an API and Append both requests.
On desktop this works fine and I can refresh the dataset.
As soon as i upload it on power BI service, after the first refresh, all the data from the sharepoint file is gone. I dont understand why, I have some other queries working the same way in the same dataset and I don't have the issue.
Does anyone know how to solve this ?
My request for the file :
Source = SharePoint.Files("sharepointurl", [ApiVersion = 15]),
HistoricalFile = Table.SelectRows(Source, each Text.Contains([Name], "filename.xlsx")){0}[Content],
#"Imported Excel Workbook" = Excel.Workbook(HistoricalFile),
Feuil1_Sheet = #"Imported Excel Workbook"{[Item="Feuil1",Kind="Sheet"]}[Data],
Thank you !
MD
Hi mdufresne34 ,
Thanks for reaching out to the Microsoft fabric community forum.
This issue usually happens because of how Power BI Service handles SharePoint connections differently than Power BI Desktop. When you're using SharePoint.Files() in Power BI Desktop, it works fine because it's using your local credentials. But once the report is published to the Power BI Service, the service itself needs proper access permissions to the SharePoint file, and that's often where things break.
A more reliable approach is to use SharePoint.Contents() instead, which connects directly to the file path within SharePoint rather than loading all files from the site. Also, make sure to go into Power BI Service settings after publishing, and update the data source credentials to use OAuth2 with your organizational account.
This will allow the service to authenticate correctly and keep the SharePoint file connected during refresh. Here's a sample structure you can try using: connect to the SharePoint site with SharePoint.Contents, navigate to the "Shared Documents" folder, get the file, and then load your sheet as needed.
This method tends to be more stable for scheduled refreshes and avoids the issue of disappearing data after the first refresh.
If the response has addressed your query, please "Accept it as a solution" and give a "Kudos" so other members can easily find it.
Best Regards,
Tejaswi.
Community Support
8 Replies
Make sure your source filter results in exactly one file. Add a FolderPath filter criterion, for example.
What do you mean ? Could you give me an example ?
Modify the HistoricalFile step to be more robust. It makes too many assumptions.
Hi mdufresne34 ,
Thanks for reaching out to the Microsoft fabric community forum.
This issue usually happens because of how Power BI Service handles SharePoint connections differently than Power BI Desktop. When you're using SharePoint.Files() in Power BI Desktop, it works fine because it's using your local credentials. But once the report is published to the Power BI Service, the service itself needs proper access permissions to the SharePoint file, and that's often where things break.
A more reliable approach is to use SharePoint.Contents() instead, which connects directly to the file path within SharePoint rather than loading all files from the site. Also, make sure to go into Power BI Service settings after publishing, and update the data source credentials to use OAuth2 with your organizational account.
This will allow the service to authenticate correctly and keep the SharePoint file connected during refresh. Here's a sample structure you can try using: connect to the SharePoint site with SharePoint.Contents, navigate to the "Shared Documents" folder, get the file, and then load your sheet as needed.
This method tends to be more stable for scheduled refreshes and avoids the issue of disappearing data after the first refresh.
If the response has addressed your query, please "Accept it as a solution" and give a "Kudos" so other members can easily find it.
Best Regards,
Tejaswi.
Community SupportHi mdufresne34,
I wanted to follow up and see if you had a chance to review the information shared. If you have any further questions or need additional assistance, feel free to reach out.
If the response answered your query, kindly “Accept as Solution” and Give “Kudos” to help others in the community benefit from it as well.
Thank you.
Tejaswi
Hi mdufresne34,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.Thank you.
