Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Sharepoint online lists - new data everyday

I have a dataset where I am reporting on Operational Events over a 24 hour period. One of the data sources is customer contact data which is deleted automatically at 2am and then refreshed with new data (same headers) on a sharepoint list. This is connected through a Sharepoint Online List and is the only part of my dataset that doesn't refresh on the schedule refresh because the GUID changes, has anyone ever had this problem and/or managed to get round this? 

Need Help
Tips and Tricks
  • lbendlin's avatar
    lbendlin
    6 years ago

    Check your auto generated M Code.  It probably looks like this:

     

    let
    Source = SharePoint.Tables("https://xxx.sharepoint.com/teams/yyy", [ApiVersion = 15]),
    #"463db794-eb4d-45b4-8ad4-818c002c3811" = Source{[Id="463db794-eb4d-45b4-8ad4-818c002c3811"]}[Items],
    #"Renamed Columns" = Table.RenameColumns(#"463db794-eb4d-45b4-8ad4-818c002c3811",{{"ID", "ID.1"}})
in
    #"Renamed Columns"

     

    Change it to key off the name instead of the Id (hopefully the list name is constant?!)

     

    let
    Source = SharePoint.Tables("https://xxx.sharepoint.com/teams/yyy", [ApiVersion = 15]),
    MyList = Source{[Title="My List Name"]}[Items],
    #"Renamed Columns" = Table.RenameColumns(MyList,{{"ID", "ID.1"}})
in
    #"Renamed Columns"

     

9 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    Anonymous - Are you saying that the entire list is deleted every day?

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      More a total refresh everyday at 02:00 which clears the previous days data and refreshes with a new set of data, obviously the headers stay the same. I get a GUID error when I try a scheduled refresh as the GUID seems the change everyday. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      this is the error 

       

      {"error":{"code":"ModelRefresh_ShortMessage_ProcessingError","pbi.error":{"code":"ModelRefresh_ShortMessage_ProcessingError","parameters":{},"details":[{"code":"Message","detail":{"type":1,"value":"The key didn't match any rows in the table."}}],"exceptionCulprit":1}}} Table: Customer Contacts.

       

      Greg_Deckler 

      • lbendlin's avatar
        lbendlin
        Super User
        6 years ago

        Check your auto generated M Code.  It probably looks like this:

         

        let
    Source = SharePoint.Tables("https://xxx.sharepoint.com/teams/yyy", [ApiVersion = 15]),
    #"463db794-eb4d-45b4-8ad4-818c002c3811" = Source{[Id="463db794-eb4d-45b4-8ad4-818c002c3811"]}[Items],
    #"Renamed Columns" = Table.RenameColumns(#"463db794-eb4d-45b4-8ad4-818c002c3811",{{"ID", "ID.1"}})
in
    #"Renamed Columns"

         

        Change it to key off the name instead of the Id (hopefully the list name is constant?!)

         

        let
    Source = SharePoint.Tables("https://xxx.sharepoint.com/teams/yyy", [ApiVersion = 15]),
    MyList = Source{[Title="My List Name"]}[Items],
    #"Renamed Columns" = Table.RenameColumns(MyList,{{"ID", "ID.1"}})
in
    #"Renamed Columns"

         

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    Hi Anonymous 

    "One of the data sources is customer contact data which is deleted automatically at 2am and then refreshed with new data (same headers) on a sharepoint list",

    Could you show me what is deleted in your data source and what is not changed?

     

    This error "The key didn't match any rows in the table" may indicate power bi queries some content inside a list, but there is no appropriate data here.

     

    Best Regards

    Maggie