Forum Discussion
Sharepoint online lists - new data everyday
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"
Community ChampionAnonymous - Are you saying that the entire list is deleted every day?
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.
- lbendlin
Super User
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"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?
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
Microsoft Employee
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
