cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Nicki Helper I
Helper I

Replace null with 0

Hi ,

I want to replace Null value for all columns with zero in power BI Query.

For example, 

Table.ReplaceValue(#"Pivoted Column",null,0,Replacer.ReplaceValue,{"X1 ", "X2", "X3"})

 

I do not want to write my column name  like  {"X1 ", "X2", "X3"}.

Is there any function in M for replacing all columns with one value ?

 

Thanks

Nicki

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User II
Super User II

Re: Replace null with 0

Hi @Nicki,

you can use Table.TransformColumns function for your expected output.

 

let
    // my test data
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQIiI6VYnWgQwxjEj40FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    // get all column names as list
    allColumnNames = Table.ColumnNames(Source),
    // transform the column name list into a list of list, where every inner list contains column name and a function for replacing null value
    allTranformations = List.Transform(allColumnNames, each {_, each if _ = null or _ = "" then 0 else _}),
    // apply the transformations
    tranformColumns = Table.TransformColumns(Source, allTranformations)
in
    tranformColumns

View solution in original post

3 REPLIES 3
Community Support
Community Support

Re: Replace null with 0

Hi @Nicki ,

 

We can ctrl + click to chooes multi columns to work around.

 

ctrl.PNG

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Highlighted
Super User II
Super User II

Re: Replace null with 0

Hi @Nicki,

you can use Table.TransformColumns function for your expected output.

 

let
    // my test data
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQIiI6VYnWgQwxjEj40FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    // get all column names as list
    allColumnNames = Table.ColumnNames(Source),
    // transform the column name list into a list of list, where every inner list contains column name and a function for replacing null value
    allTranformations = List.Transform(allColumnNames, each {_, each if _ = null or _ = "" then 0 else _}),
    // apply the transformations
    tranformColumns = Table.TransformColumns(Source, allTranformations)
in
    tranformColumns

View solution in original post

Nicki Helper I
Helper I

Re: Replace null with 0

Thank you. It works fine.

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors