Values not pulled by Power Query from specific column in Excel table

Question

I have copies of a standard-formatted data table in multiple sheets in my Excel 2013 workbook that I am using Power Query add-in to pull data from and compile into a larger table. Each summary table has 5 columns of data. When hitting ‘refresh’, Power Query is set up to pull data from all 5 columns. Initially this worked as expected with the sheets I made. More recently, when adding new sheets, the queried data from the middle column is always blank in the larger table. When I look into the power query editor, these values are returned as null, even though there are numbers entered into this middle column. The problem seemed to have coincided with when I started generating the sheets using a recorded macro. To test if the problem was in the macro, I tried making a new sheet without the macro and the same outcome still occurred. I changed the cell positioning of the tables to check if there was something weird with the formatting of a column, and the problem still happened.

The steps I have taken that lead to this problem:

1. Created a 5 x 8 table with column headers and populated multiple sheets with copies of it.
2. In Power Query tab, I got external data using ‘From other sources’ and selecting ‘blank query’.
3. Entered ‘=Excel.CurrentWorkbook()’ into editor bar.
4. After clicking on double arrow icon in green column, I selected all the columns from the tables and unchecked ‘Use original column name as prefix’.
5. On ‘Name’ column arrow button, went to ‘text filters’ and clicked ‘does not equal’.
6. I selected ‘Query’ in drop down list.
7. I Clicked ‘Close and Load’ to produce the summary table.

I’ve searched for similar problems using tags [power query][Microsoft-excel 2013] and nothing has come up.

Answer 1

The expand step works on Column names, so the most likely cause is a variation in the Column names between sheets, e.g. mixed case, leading or trailing spaces, extra embedded spaces. It's usually easiest to resolve those issues by correcting the Excel data.