Columns missing from data file in power query

[UK06B1]

Hello 

I have an issue when i link power query to a data set it only brings thourgh up to column 7. But when i open the data file in excel there is data in column 8. 

Is there a way in the advanced properties to change the code to bring through column 8 as well instead of referring to the sheet ? 

thanks

Neil

[UK06B1]

Hi  , the source data is coming from another system (Business Objects) so ideally i dont want to touch that file. See below, i think it has something to do with how the ranges are set in the file which is coming from the other system but i would prefer not to have to go into the data - ideally we should be able to refresh in power query (power BI) if possible. 

thanks 

[Waldo35]

Based on the screenshot it seems to take 7 columns, but for some reason it is taking the A column even tho it should start a B. From a data perspective i do think you should look into getting the excel to start on the A column instead of the B. just because it makes more sense. It should work without that tho. I have no issue loading in even when it starts on column B. Could you try making a manual version where it starts on column A or it's transfered to a table. Just to rule out that option out. (then at least you know where to look)

[UK06B1]

Thanks what is strange is that if i link direct to the data it misses the column. But if i open the data file and close without making any changes at all and then go into power query (both Excel & BI) it actually works. I assume it has something to do with the query/connection properties (in the source data file) so was wondering if i can change the connection/query properties to pick up the extended range ? 

Is it actually possible to change query properties direct ? as you see here it goes to "G744" when in fact i want it to go to H744. 

I have replicated the issue in Excel power query and get the same issue as in power BI so its not really an excel issue i think. 

cheers 

[Waldo35]

As far as i know it's not possible to input a custom range in the connector to the actual object. I suggest contacting microsoft support, maybe they can see something but sadly i cannot help you out with this. I don't see anything thats going wrong from your end, especially if the problem exists in excel and pbi.

[Waldo35]

did u make the columns into a table, and what did you select in the navigator. If possible make it a table in the excel and then select the table in the navigator, maybe that leads to better results. But it should work either way so something weird is going on. Also try restarting / making a new excel and copying the data.

sheet version

table version