Replace values to null

[BatTodor]

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 regards Todor Todorov

[Dom-Perthyn]

If you only wish to do this for paricular test values (assuming XYX is not one of them to keep) you can use an each if:

=Table.ReplaceValue(#"PreviousTable", each if [TestColumn] = "TestValue" then [ReplaceColumn] else "XYX", null ,Replacer.ReplaceValue,{"ReplaceColumn"}),
Note TestColumn does not have to be the same one as ReplaceColumn

All Values to be replaced to null it is:
=Table.ReplaceValue(#"PreviousTable", each [ReplaceColumn], null ,Replacer.ReplaceValue,{"ReplaceColumn"}),

[Panteras01]

If you select the columns that you want to have the null replaced and  and then right click and use the replace value option on the pop up menu it will create this step that works with the selected columns

= Table.ReplaceValue(#"name of previous step",null,"",Replacer.ReplaceValue,{"Column", "Column1","Column2","ColumnN"})

[Ulsq]

I usually use "extract" under the transform tab to extract everything before " " (Space). This return "" (blank) in the entire column. Now you can replace "" (blank) for 0 or null.

[v-frfei-msft]

Hi @BatTodor,

Based on my test, we can take the following steps to meet your requirement.

1. Duplicate the fact table and filter the both tables. One for the rows that your want to change the values to null and one for the date that you want to keep.

2. Filter the table that you want to replace the values to null and make the values of any column to error by changing data type or something other actions. Then we can replace all the error to null.

3. Append the two tables as new one and fill up/down.

For more details, please check the pbix as attached.

https://www.dropbox.com/s/ydxao6hufnayohn/null.pbix?dl=0

Regards,

Frank

[BatTodor]

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]

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]

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]

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

[v-frfei-msft]

Hi @BatTodor,

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

Regards,

Frank

[v-frfei-msft]

Hi @BatTodor,

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

Regards,

Frank

[Anonymous]

Hi @BatTodor,

If you want to replace all values as null, it is maybe easier to add a new colum with all null values, delete the old column and rename the null column with the same name as the old one.

Br,

T

[BatTodor]

Hi t_R,

It's OK to do that if there is just one column. But if there are lots of columns - 50, 100, 1000 need to find different approach.

I'm trying to find solution for lots of columns.

Best regards

Todor

[Anonymous]

Hi @BatTodor,

Can you show some dummy data on what you're trying to do?

Br, 

T