Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Power Query changing field format after replacing Null

In Power Query, I encountered an issue where I had a column formatted as Whole Number. There were Null values in the column, so I added a step to replace Null with zero. After that step, somehow Power Query automatically/incorrectly changed the format of the column to Decimal Number. There is no line in the m code changing the format to decimal. Another step later in the process was expecting this column to be whole number, so I was getting a “we couldn’t convert to Number” error.

 

M Code:

#"Changed Type9" = Table.TransformColumnTypes(#"Sort By Date Value Then Employee ID",{{"Hire Count", Int64.Type}}),  // Changed the column to Whole Number

    #"Replaced Value1" = Table.ReplaceValue(#"Changed Type9",null,0,Replacer.ReplaceValue,{"Hire Count"}),  // Replaced Null with Zero

    #"Changed Type10" = Table.TransformColumnTypes(#"Replaced Value1",{{"Hire Count", Int64.Type}}),   // Had to change the format back to Whole Number even though there is no step here that changed it to Decimal Number

 

This is my first post, so my apologies if I posted this to the wrong section.

 

Thanks, Ryan

Status: Investigating

Hi @rwmed99a 

Here is my PQ . After I replaced the null value with 0 , I did have the problem you mentioned, the data type was automatically changed, then I changed it back to whole number, and I was successful. Can you create a simple samplet and provide it to us for testing?

Ailsamsft_0-1679021699092.png

Best Regards,
Community Support Team _ Ailsa Tao

Comments
v-yetao1-msft
Community Support
Status changed to: Investigating

Hi @rwmed99a 

Here is my PQ . After I replaced the null value with 0 , I did have the problem you mentioned, the data type was automatically changed, then I changed it back to whole number, and I was successful. Can you create a simple samplet and provide it to us for testing?

Ailsamsft_0-1679021699092.png

Best Regards,
Community Support Team _ Ailsa Tao