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 d...
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"

     

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity 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
      Icon for Super User rankSuper 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"

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        lbendlin Thanks, my list name stays the same so I have written the code like this (below) and I'll try it tonight, the list refreshes in Sharepoint around 2am, do you see any obvious errors in this? 

         

        Source = SharePoint.Tables("https://xxx.sharepoint.com/xxx/xxx/", [ApiVersion = 15]),
        MyList = Source{[Title="Customer Contacts"]}[Items],
        #"Renamed Columns" = Table.RenameColumns(MyList,{{"ID", "ID.1"}}),
        #"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns",{"Title", "NeedGroup", "NeedType", "Need", "ContactType", "StreetName", "Postcode", "ContactDateTime", "Modified", "Created"})
        in
        #"Removed Other Columns"

    • Greg_Deckler's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      6 years ago

      Anonymous - Are you doing any transformation in your query or is just a straight-up load of the SharePoint list? Can you post your query code from Advanced Editor?

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Greg_Deckler 

        Its a straighup load of the list and I just select the columns I want 

         

         

        let
        Source = SharePoint.Tables("https://XXXX.sharepoint.com/sites/XXXX/", [ApiVersion = 15]),
        #"18b51dfb-6739-4164-b7ae-faf433095086" = Source{[Id="18b51dfb-6739-4164-b7ae-faf433095086"]}[Items],
        #"Renamed Columns" = Table.RenameColumns(#"18b51dfb-6739-4164-b7ae-faf433095086",{{"ID", "ID.1"}}),
        #"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns",{"Title", "NeedGroup", "NeedType", "Need", "ContactType", "StreetName", "Postcode", "ContactDateTime", "Modified", "Created"})
        in
        #"Removed Other Columns"

         

        The list is called Customer Contacts