Forum Discussion
Sharepoint file not updated
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])
Super UserWhat 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.
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
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])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>) )