I have no idea how to find the source of data for a table refresh in Sharepoint

[RockandGrohl]

Hi guys, our organization has a sheet which contains a couple tables on each tab, and these are refreshed.

I've no experience whatsoever with Power Query, so hoping for some help here.

When I hit refresh, I get "[Expression.Error] The key didn't match any rows in the table"

So I then open the Queries & Connections panel and see each of the table in each tab has an exclamation next to it with

An error occurred in the ‘MainData’ query. Expression.Error: The key didn't match any rows in the table.
Details:
    Key=
        Id=60421309-1234-437d-1010-0d6433d75758
    Table=[Table]

After some Googling I think this error means something has changed in the source table, something to do with that Id string, but I'm not sure (I've changed a few characters around for safety because I've no idea what it is)

I go to the Power Query Editor and the Query appears to be called "MainData" - because every table that tries to be refreshed says "An error occurred in the ‘MainData’ query."

Looking at the MainData query, where do I go from here to identify *what* it's actually trying to look at?

If I right click MainData and do "Advanced Editor" it gives me this:

   Source = SharePoint.Tables("https://[Myorgname].sharepoint.com/sites/irf", [Implementation="2.0", ViewMode="All"]),

Great! So there's the source, my organisation name sites and then IRF.


But here's the rub. IRF is just a base directory, which has many folders & files in. The query doesn't seem to point me to any specific source.

So I'm completely at a loss of how to identify what the source is, to figure out why that ID code is suddenly unsuitable.


Thank you!

 

[RoryA]

With the source step selected, can you see a list of tables, with an ID column at the start?

 

[RockandGrohl]

With the source step selected, can you see a list of tables, with an ID column at the start?

Hi Rory

Yep, I've just found that (not seen it before). The source step appears to be a single table with multiple columns (odata.type, odata.id, odata.etag, Id, etc)

In the properties of the "MainData" query when it says "The key didn't match the rows in the table" I press "Go to error" which takes me to "Navigation" in the steps which immediately follows the "Source" step at the top.

If I hit edit settings on that it shows a folder called https://[Myorgname].sharepoint.com/sites/irf [40]

And finally at the bottom of the list of tables within that irf folder, it gives me one called "Web Template Extensions", which has one item - "Items:60421309-1234-437d-1010-0d6433d75758"

Ok, excellent, but I am still confused as to how I access this document.


Thanks for your help so far.

 

[RoryA]

Have you tried navigating to that site in a browser and then examining the site contents?

 

[RockandGrohl]

With the source step selected, can you see a list of tables, with an ID column at the start?

Yep, when I go to the IRF site it is a landing page which has some buttons on it to upload a CSV, see FAQ's etc, and there's a subfolder there for documentation with a few subfolders, but I can't see anything that looks like it would be a data source.

I'm new to this aspect of Sharepoint so I'm finding it difficult to determine what exactly I'm looking for.

Thanks

 

[RoryA]

Do you not have a toolbar to the left that lists things like Pages, Site Contents etc?

 

[RockandGrohl]

Do you not have a toolbar to the left that lists things like Pages, Site Contents etc?

Yeah, there is Registers, which has a subfolder called Documentation. This has 2,000 empty folders in it and then another folder which has some excel file that isn't anything to do with anything.

Then another folder called IRF documentation which doesn't have anything helpful in it. Word documents & PDF's etc.

 

[RoryA]

OK. In your original query, I presume the second step is called Navigation? If so, its formula should start with Source{[Id=" followed by an ID string. See if you can match that string to one of the ID items in the table shown in the Source step.