Excel sharepoint data not available on Power BI Service

[mdufresne34]

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

[v-tejrama · Accepted solution]

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

[v-tejrama]

Hi @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

[v-tejrama]

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. 

[v-tejrama]

Hi @mdufresne34 ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and Accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[mdufresne34]

Hello, I tried again using Sharepoint.Content() and it didn't solve the issue.

[lbendlin]

Make sure your source filter results in exactly one file.  Add a FolderPath filter criterion, for example.

[mdufresne34]

What do you mean ? Could you give me an example ? 

[lbendlin]

Modify the HistoricalFile step to be more robust. It makes too many assumptions.