Data in first row of table importing incorrect into the query editor

[wbstadeli]

Hi Guys,

I just discovered the "query" section under data and would like to use it to get averages of values across multiple excel files in a certain folder. Im trying to start simple, just one file in the folder and bringing in what is called "Table9" into the editor. The problem in all my files, this "Table9" does not have a header, the first row contains data. When i open it in the editor to bring in just that table, the first row is treated as a header. I have several cells in the first row that have the same value so excel automatically is adding a number to the value that is the same as the previous( to keep distinction between columns names i suppose..), which of course distorts my entries. How can i get the table to come in without headers? or just add a mock header row, but not use the first row as headers? Below is an example of what is happening:

Table9 before bringing it into query editor
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]25[/TD]
[TD]30[/TD]
[TD]25[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Table9 after bringing it into query editor
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]25[/TD]
[TD]30[/TD]
[TD]251[/TD]
[TD]252[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks for any help with this!!

 

[VBA Geek]

Delete the step in your Query called "Promote Headers"

Or un-check the below option in Power Query settings:

 

[wbstadeli]

Thanks for your reply, unfortunately i tried unchecking that box, that did not help. I cant delete "promote headers" because i havent even got that far with the procedure, this is when i select new query>from file>from folder>"i select the proper folder" >and then click "edit". It loads the files in the query editor, and then i click the double arrows in the "content" to see the data. This opens up "combine files" and the objects that are on the sheets for extraction. I click on "Table9" and in the preview pane it already shows the distorted data for this table. Any other ideas?

 

[VBA Geek]

When you import from folders, PQ adds a set of extra functions as you see below. That should be where the Promote Headers takes place

 

[wbstadeli]

I see the screen you have shown there, but when i click on "Transform Sample File from ...." all i see under "Applied Steps" is source. What am i missing?

 

[VBA Geek]

I see the screen you have shown there, but when i click on "Transform Sample File from ...." all i see under "Applied Steps" is source. What am i missing?

Difficult to tell without seeing your file, I would look in those added queries, I assume that is where your Promote Headers takes place