Data from excel showing up with zeros instead of blank, I want the blanks to show

[petermb72]

I have a excel spreadsheet that pulls data from other tabs into the one sheet.  If there is no data the field is blank (but there is a formula in the field to get the data from another tab).  When I pull this sheet into power query editor, all of the blank fields are replaced with a zero.  I would prefer that they were blank or Null. 

The data in excel(formulas in all fields) looks like this :

Data	Jan	Feb	Mar	April	May
West	0	1	7
East	1	2	3

When it is in power query editor the same data looks like:

Data	Jan	Feb	Mar	April	May
West	0	1	7	0	0
	1	2	3	0	0

When I Use Power query editor, I want to be able to remove all of the months that do not have data in them yet.  Where there is a zero, I can tell the editor to remove zeros, but it will remove West's 0 in January.  

So my question again is, why does PQE bring zeros in where it should be blanks or null?  Sencond question is, if I can't fix this, how can I remove the months that should have blanks but are coming in with zeros?

Thanks,
Peter

[petermb72]

I tried as explained above and All it did was shift the zeros to left justified (turned them to text).  It did not remove them.  Thanks for the tip but, no dice.

[v-xinruzhu-msft]

Hi @petermb72 

Based on your description, when you enter the power query editor, it will change the data type automatcially, you can remove the change type step.

Then all the data types will be text, and the blank column will keep blank, then you can set the data type by yourself. e.g you can set the data type of all the blank columns to text, then change the data type of other columns to number so that you can remove the blank column.

Best Regards!

Yolo Zhu

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