Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Sharepoint file not updated

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. 

  • danextian's avatar
    danextian
    1 year ago

    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])

8 Replies

  • 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

    • Anonymous's avatar
      Anonymous
      Not applicable

      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. 

  • 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.

    • Anonymous's avatar
      Anonymous
      Not applicable

      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's avatar
        danextian
        Icon for Super User rankSuper User

        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])
  • v-nmadadi-msft's avatar
    v-nmadadi-msft
    Icon for Community Support rankCommunity Support

    Hi  Anonymous ,
    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