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!

Reply
nj17
Helper III
Helper III

replace numeric values with 0 in Power Query

Hi

@amitchandak  @Ashish_Mathur 

How can I replace below column's numeric value to 0 in Power query

 

nj17_0-1662606803177.png

 

Also I cant see any advanced option to replace all values to 0

nj17_0-1662607294082.png

 

 

Thanks

NJ

1 ACCEPTED SOLUTION
v-yueyunzh-msft
Community Support
Community Support

Hi, @nj17 

According to your describtion,You want to replace the non-null value of a column with 0. Right?

Here are my steps you can follow:

(1) This is my test data :

vyueyunzhmsft_0-1662707459984.jpeg

 

(2)You can click “Custom Column” in Power Query Editor and enter as follows:

if [Column1] <> null then 0 else null

vyueyunzhmsft_1-1662707459985.jpeg

 

(3)Then we just need to delete the original column,then we can meet your need :

vyueyunzhmsft_2-1662707459986.jpeg

 

 

 

Best Regards,

Aniya Zhang

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

View solution in original post

4 REPLIES 4
v-yueyunzh-msft
Community Support
Community Support

Hi, @nj17 

According to your describtion,You want to replace the non-null value of a column with 0. Right?

Here are my steps you can follow:

(1) This is my test data :

vyueyunzhmsft_0-1662707459984.jpeg

 

(2)You can click “Custom Column” in Power Query Editor and enter as follows:

if [Column1] <> null then 0 else null

vyueyunzhmsft_1-1662707459985.jpeg

 

(3)Then we just need to delete the original column,then we can meet your need :

vyueyunzhmsft_2-1662707459986.jpeg

 

 

 

Best Regards,

Aniya Zhang

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

Ashish_Mathur
Super User
Super User

Hi,

Try this M code

=if Value.Type([Amount]) = type number then 0 else null

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Shreeram04
Resolver II
Resolver II

Hi @nj17,

 

If you don't want to do any further calculations using this column means you can convert data type from number to text and you can change it into 0.

 

Step 1:

Shreeram04_0-1662612824219.png

 

Shreeram04_1-1662612856452.png

 

Shreeram04_2-1662612882198.png

Change the value to 0 for two to three value power bi automatically change the value to 0.

Shreeram04_3-1662612906599.png

 

Shreeram04_4-1662612932733.png

Thanks,

 

Hi @Shreeram04 

I need to do further calculations on this so cant change datatype

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.