Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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? 

1 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"

 

View solution in original post

9 REPLIES 9
v-juanli-msft
Community Support
Community Support

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
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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 

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
Not applicable

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

@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 

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Anonymous
Not applicable

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. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors