Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
BatTodor
Frequent Visitor

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

13 REPLIES 13
Dom-Perthyn
Advocate I
Advocate I

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
Frequent Visitor

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
New Member

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
Community Support
Community Support

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.

 

Capture.PNG

2.PNG

For more details, please check the pbix as attached.

 

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

 

Regards,

Frank

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

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:

2018-08-30 11_31_59-DB-ForMerging - Query Editor.png

Lots of columns, lots of values...

 

Best regards

Todor

 

 

 

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.

 

123123.PNG

 

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

 

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

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

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

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

Hi @BatTodor,

 

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

 

Regards,

Frank

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

Hi @BatTodor,

 

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

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Anonymous
Not applicable

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

 

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
Not applicable

Hi @BatTodor,

 

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

 

Br, 

T

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.