Forum Discussion

BatTodor's avatar
BatTodor
Frequent Visitor
8 years ago

Replace values to null

Hi, I want to replace all values in all selected columns to null. In general, main aim is to Fill down all cells with value, but at first need to clear all previous information. Is it possible? Best ...
BatTodor's avatar
BatTodor
Frequent Visitor
8 years ago

Hi v-frfei-msft,

Sounds to work (can't download attached pbix).

Probably will lose some performance - I'm using 32-bit excel.

 

Wonder if there is wa to replace "each text/value" to null. In this case will set data to text and replace to null.

In my case values are numbers and I tried Table.ReplaceValue(<TableName>,each {0..9},null,Replacer.ReplaceValue,<ColumnNames>) but no success.

 

Anonymous

In this case I have 50 columns, but make screenshot for few:

Lots of columns, lots of values...

 

Best regards

Todor

 

 

 

v-frfei-msft's avatar
v-frfei-msft
Community Support
8 years ago

Hi BatTodor,

 

We can use  Table.TransformColumns to meet your requirement.

 

 

    #"Table.TransformColumns"=Table.TransformColumns(previsoustep, {"ID", each if _ is number then "null" else _})

 

Here is the result for your reference.

 

 

The M code in power query is like this.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSgeJYnWglIyjPGMwzhvJMwTwTKM8IzDOF8kzAPDMUleYopligqLREsc/QAGFoLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Group = _t, Class = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Group", Int64.Type}, {"Class", Int64.Type}}),
#"Table.TransformColumns"=Table.TransformColumns(#"Changed Type", {"ID", each if _ is number then "null" else _})
in
    #"Table.TransformColumns"

For more details, please check the pbix as attached.

 

https://www.dropbox.com/s/7sldff3q5oi0m68/replace.pbix?dl=0

 

Regards,

Frank