Excel import - Columns missing data

[bilogin]

Hi all, 

I have a folder with sales data for each quarter for 2 financial years. 

I imported the data all good however when I looked closer, I saw that the entire column was missing. I looked online for an answer, saw that the column wasn't included and went into the Advanced Editor and saw that the column wasn't there. I added it in, highlighted below: 

I then realised that the column didn't exist in the FY17/18 data so I added in the column to each spreadsheet and saved.

Opened a completely new pbix and re-connected to the sources. 

The column is now included in the data (yay) however no data has been pulling through. It's expected that it would be null for the 17/18 data but it should be populating for 18/19 and 19/20.

Thanks in advance.

Bec

[dax]

Hi @bilogin , 

I am not clear about your requirement, did you mean that when you combine sheets , the data still missing in power query? Please makse sure the data type is correct and make sure whether the column name are the same, or refresh the datasource to see whether it work or not.  By the way, if possible could you please inform me more detailed information(such as your expected output and your sample data)? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[edhans · Accepted solution]

A couple of things:

1. 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.
2. 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.

[bilogin]

@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. 

[edhans]

Excellent! Glad my answer helped and your project is now moving forward.