Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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"
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
@Anonymous - 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.
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"
@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
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.