Sharepoint file not updated

[annar]

Hello,

I am creating a report in Power BI using Excel file containing four sheets which is located on the Sharepoint. I have used `Sharepoint folder` connector. The file is being updated once a month and two sheets are correctly updated in the Power BI Desktop and two are not. Their structure is identical. All sheets are being updated in the way that four lines are added in all four sheets. In the two problematic sheets the four most up to date lines are always missing.  I have refreshed the files in the Power Query few times as well as cleared the cache in Power BI. 

[v-nmadadi-msft]

Hi  @annar ,
Thanks for reaching out to the Microsoft fabric community forum.

Based on the previous discussion in the forum: Solved: Fetching data from sharepoint but it's not refresh... - Microsoft Fabric Community
what worked for them was to make sure that all users closed the file on their browsers, and then I refreshed in PowerBI and the changes updated.

Other than that try to  
Go to File -> Options and Settings -> Options -> Data Load -> Clear Cache under Data Cache Management options.

Go to Transform Data > Data Source Settings -> Clear permissions for your SharePoint connections. Re-add the connection through get data or the Data Source settings and authenticate.

Please check the refresh status after clearing cache and re adding the connection details.

I hope this information helps. Please do let us know if you have any further queries.
Thank you

[danextian]

What are you transformations? It is possible that one of those transformations cause the new lines to be missing. It's hard to figure out the reason without seeing the query.

[annar]

There are not many transformations:

let
Source = SharePoint.Files("https://xxx.sharepoint.com/sites/xxx/", [ApiVersion = 15]),
#"Filtered Rows" = Table.SelectRows(Source, each ([Name] = "xxx.xlsx")),
#"xxx xlsx_https://steria sharepoint com/sites/xxx/Shared Documents/Customer team management/xxx/xxx/" = #"Filtered Rows"{[Name="xxx.xlsx",#"Folder Path"="https://xxx.sharepoint.com/sites/xxx/Customer team management/xxx/xxx/"]}[Content],
#"Imported Excel Workbook" = Excel.Workbook(#"xxx xlsx_https://xxx sharepoint com/sites/xxx/Shared Documents/Customer team management/xxx/xxx/"),
IncidentsFacts_Sheet = #"Imported Excel Workbook"{[Item="IncidentsFacts",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(IncidentsFacts_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Priority", Int64.Type}, {"Start of Month", type date}, {"Value", Int64.Type}})
in
#"Changed Type"

I have marked sensitive data as `xxx`. I don't think transformations are reponsible for last four lines to be missing. In the two problematic sheets last four lines were also missing last month. It is strange because two sheets are ok and two sheets are not and they all have the same structures and the same transformation steps were applied. 

[danextian · Accepted solution]

I wonder why you're using a Sharepoint folder connection when you are connecting to a single file only as shown in your query.  That aside, are you able to see the missing rows in the query editor? What about in the preview prior to connecting to the worksheet itself?

If you don't see those lines, try adding InferSheetDimensions option to Excel.Workbook

Excel.Workbook(#"xxx xlsx_https://xxx sharepoint com/sites/xxx/Shared Documents/Customer team management/xxx/xxx/", [InferSheetDimensions = true])

[annar]

Thank you, adding 

[InferSheetDimensions = true]
did resolve the issue. 🙂 I did not see the missing rows in the preview prior to connecting to the worksheet itself. 

And regarding your question about the Sharepoint Folder - if I am connecting to only one file from a Sharepoint folder, would the Web connector be the best choice?

[danextian]

The SharePoint folder connector can become quite slow, especially as the site grows in size. The more files it contains, the longer it takes to load. It's often better to use the Web connector instead. If you do need to connect to a SharePoint folder, consider using SharePoint.Contents, which lets you navigate the site’s folder structure hierarchically and can offer better performance and control.

Exce.Workbook(Web.Contents(<complete path>) )

[FBergamaschi]

have yo urefreshed from the cloud either manually or scheduled? What was the result of the refresh (any error)?

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[annar]

Hello, the file was modified four days ago and the changes are visible on the Sharepoint but not in the Power Query. The report is published to the Service and I refreshed the data manually - the refresh was successfull. What is interesting, the data for two sheets is updated and for two - it isn't.