Forum Discussion
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
15 Replies
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
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
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
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"})Extract First Character and enter 0 in the pop-up, that's all 😄
Note: This is applicable on multiple columns as well.@ ramdas_01
That worked perfectly! Exactly what I needed. Why does Transform>Extract>First Character>0 work? If you could, please explain.
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
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 ReplaceColumnAll Values to be replaced to null it is:
=Table.ReplaceValue(#"PreviousTable", each [ReplaceColumn], null ,Replacer.ReplaceValue,{"ReplaceColumn"}),
