Why am I getting inconsistent "null" values in Power Query?

[EMN]

I have a table in Excel with 18 columns. All of these columns have formulas in them. There are values in all column cells up to row 18.
When I load this sheet into Power Bi, the 13th and 14th columns will show some "null" values from rows 19 onwards. There is nothing but formulas in these blank and null cells but for some reason null shows in these columns:

This causes me problems, as when I import the data via a folder, a "Source" column will be added (listing the file name) and it will fill down to the rows that show "null" in columns 13 and 14.

This means if I go to delete all "blank rows" it wont delete the rows that have "null" in columns 13 and 14. 

How do I get Power Query to stop showing "null" in these blank columns? 

[v-linhuizh-msft · Accepted solution]

Thanks for the replies from Tahreem24 and POSPOS.

Hi @EMN ,

Based on the image provided, you can choose to remove the bottom 11 rows or keep the top 18 row instead of choosing to delete the blank rows.

You can filter out values that are not null:

Result:

Best Regards,
Zhu

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

[EMN]

Thank you very much, all of these replies were helpful but this one has solved it for me! Appreciate your speedy and detailed assistance 🙂

[POSPOS]

@EMN  -  You can try to remove the formula from the blank cells from Row 19 as your data is only up to row 18.
This will get rid of the null rows

[Tahreem24]

@EMN IN home tab of Power Query use the "Remove blank" rows option in your scenario.

[EMN]

That doesn't remove them - this is the problem. When I import the data via a folder, a "Source.Name" column will be added (listing the file name) and it will automatically fill down to the rows that show "null" in columns 13 and 14.

This means if I go to delete all "blank rows" it wont delete the rows that have "null". I shifted the columns along to show, the file name is "generic_testplan_v3_experiments_withdummydata"; this column is added and populated automatically when I "Combine and Transform" the data. It populates upto the same number of rows that show "null" value, so I need the null value not to show in the first place: