Forum Discussion
Replace values to null
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
Hi BatTodor,
Does that make sense? If so, kindly mark my answer as a solution to close the case.
Regards,
Frank
