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 ...
v-frfei-msft's avatar
v-frfei-msft
Icon for Community Support rankCommunity 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

 

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
    Icon for Community Support rankCommunity 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