cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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

Accepted Solutions
Highlighted
Super User V
Super User V

Re: 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"

 

View solution in original post

9 REPLIES 9
Highlighted
Super User IX
Super User IX

Re: Sharepoint online lists - new data everyday

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


---------------------------------------

Not link spamming! If I posted a link in reply it is because I did assume you know how to search the web but thought it was the fastest way to fix your issue.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Frequent Visitor

Re: Sharepoint online lists - new data everyday

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. 

Highlighted
Frequent Visitor

Re: Sharepoint online lists - new data everyday

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 

Highlighted
Super User IX
Super User IX

Re: Sharepoint online lists - new data everyday

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


---------------------------------------

Not link spamming! If I posted a link in reply it is because I did assume you know how to search the web but thought it was the fastest way to fix your issue.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Super User V
Super User V

Re: 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"

 

View solution in original post

Highlighted
Frequent Visitor

Re: Sharepoint online lists - new data everyday

@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 

Highlighted
Super User VI
Super User VI

Re: Sharepoint online lists - new data everyday

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? Please mark my post as a solution! Kudos are also appreciated!

Proud to be a Super User!




Highlighted
Frequent Visitor

Re: Sharepoint online lists - new data everyday

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

Highlighted
Community Support
Community Support

Re: Sharepoint online lists - new data everyday

Hi @DanH84 

"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

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors