Forum Discussion
Excel import - Columns missing data
A couple of things:
- Adding a column as you did will always add that column even if it doesn't exist in the source. It will, as you discovered, return NULL.
- I suspect your source data is different and not actually "MPG" in all of the files. And Power Query is case sensitive, so MPG will not import the mpg column, or the Mpg column.
Go back to your excel files and look at the exact column headings and see what they are.
If you find that the columns are changing in name by file but not in their index number - i.e. the MPG data you want is always in the 13th column, but may have a different heading, then it becomes a bit trickier to find, but still doable, but I don't want to go down that path until you've confirmed exactly what is going on.
A couple of things:
- Adding a column as you did will always add that column even if it doesn't exist in the source. It will, as you discovered, return NULL.
- I suspect your source data is different and not actually "MPG" in all of the files. And Power Query is case sensitive, so MPG will not import the mpg column, or the Mpg column.
Go back to your excel files and look at the exact column headings and see what they are.
If you find that the columns are changing in name by file but not in their index number - i.e. the MPG data you want is always in the 13th column, but may have a different heading, then it becomes a bit trickier to find, but still doable, but I don't want to go down that path until you've confirmed exactly what is going on.
edhans , when I first read your message I scoffed and thought "What a silly thing to check" as I knew that all of the column headers were capitals. However, I will eat my words gladly as some of the headers had a space afterwards. I'm a complete beginner to all this so it's a valuable lesson to learn. THANK YOU.
- Excellent! Glad my answer helped and your project is now moving forward.
