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

 

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

    Hi BatTodor,

     

    Does that make sense? If so, kindly mark my answer as a solution to close the case.

     

    Regards,

    Frank

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

    Hi BatTodor,

     

    Does that make sense? If so, kindly mark my answer as a solution to close the case.

     

    Regards,

    Frank

  • BatTodor's avatar
    BatTodor
    Frequent Visitor
    8 years ago

    Hi v-frfei-msft,

    Thanks a lot for support! :-)

    When I use for one column, it's OK!

    Unfortunately failed to implement dinamicaly solution. Tryed to use #"Table.TransformColumns" = Table.TransformColumns(PromotedHeaders, List.Transform(Columns, each {_, "null"})),

    #"Table.TransformColumns" = Table.TransformColumns(previousstep, List.Transform(Columns, each {_, "null"})),

     where Columns is list of columns to replace.

     

    Could be done?

     

    Best regards

    Todor

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

      Hi BatTodor,

       

      Based on my test, here we can only replace the values one by one. We cannot replace all the values of column list.

       

       

      Regards,

      Frank