Sharepoint online lists - new data everyday

[Anonymous]

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? 

[v-juanli-msft]

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

[Greg_Deckler]

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

[Anonymous]

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 · Accepted solution]

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]

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

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

@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 

[mahoneypat]

You might consider making a Flow that saves all the list contents daily into a csv file stored on SharePoint or OneDrive.  You can then use the latest file as your source for Power BI, and you would have historical data files if needed too.  If the GUID is changing (which is surprising), you could have the Flow first get the list of lists from the Site and filter to it by name, then Get Items, ...

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

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.