Promoting First Row to Headers - inconsistent with Web Connection

I am connecting to a number (100+) sharepoint 2007 lists using the Web Connection method and in some cases it connects to the table I need and identifies the headers in the table all good!

Other times with a different Sharepint 2007 site it will not recognise the headers & I have to manually (part of the query steps) promote the first row to headers and then can apply the rest of my usual query steps

Then (weirdly) at some undefined point in the future, the table where I had to promote the first line to headers will fail on a refresh because it cant find a column name I have defined in a query step.... I go into to query editor and the table has now recognised the headers automatically & the promote first row to headers is overwriting the headers with the first row of data!! so I now have to amend the query to remove the "promote" query step.

It seems really inconsistent and is preventing me from using a Custom Function to automate the many table loads Im doing as I cant seem to get consistency in having to promote headers or not..

All Sharepoint sites Im connecting to are in the same sharepoint collection/site in the same server farm.

Any idea's from you clever guys & girls out there as to what might be happening & how I can stop it?

Promoting First Row to Headers - inconsistent with Web Connection

I am connecting to a number (100+) sharepoint 2007 lists using the Web Connection method and in some cases it connects to the table I need and identifies the headers in the table all good!

Other times with a different Sharepint 2007 site it will not recognise the headers & I have to manually (part of the query steps) promote the first row to headers and then can apply the rest of my usual query steps

Then (weirdly) at some undefined point in the future, the table where I had to promote the first line to headers will fail on a refresh because it cant find a column name I have defined in a query step.... I go into to query editor and the table has now recognised the headers automatically & the promote first row to headers is overwriting the headers with the first row of data!! so I now have to amend the query to remove the "promote" query step.

It seems really inconsistent and is preventing me from using a Custom Function to automate the many table loads Im doing as I cant seem to get consistency in having to promote headers or not..

All Sharepoint sites Im connecting to are in the same sharepoint collection/site in the same server farm.

Any idea's from you clever guys & girls out there as to what might be happening & how I can stop it?

Its happened again today, hence my prompt to reply back to this thread.... this time to a connection that I have had in place for over 6months where I had to promote first lines to headers now recognises the headers automatically & the promote headers now removes my headers & replaces withthe first data record.

Im checking with my sharepoint team if anything at all has changed in the last 24hours on that site...

So, I am using the following..

Power BI Desktop November 2018 edition (but it been happening for months & months)

Out of the box Web Connection in the desktop client

Connecting to a Sharepoint 2007 list with a public view that removes any limits on records returned etc

Sharepoint 2007 is on-premise

I refresh my dataset in the power BI service using Desktop Gateway; but the same issue happens if I refresh my dataset directly in the desktop client (Currently using November 2018 version of Gateway)