Replace values of date column in entire table using M query

[Anonymous]

Hi folks,

I am trying to replace "0000000" with "null"  in date column before changing its data type to Date.

I have more than 10 Date columns."Replace values" works column wise not for entire table or for selected column.

I want to repeat below M code  for 10 columns .

#"Replaced Value" = Table.ReplaceValue(#"Added Items","00000000",null,Replacer.ReplaceValue,Table.ColumnNames{#"Added Items"})

Can any one suggest how can I handle this in PQ.

Thank you in advance.

[dax]

Hi @Anonymous , 

You could refer to @ibarrau 's suggestions, just add column names in it like below

= Table.ReplaceValue(#"Added Items","00000000",null,Replacer.ReplaceValue,{"column1", "column2"})

Because the 000000 is text type, so you need to change @ibarrau 's expression to "00000".

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ibarrau · Accepted solution]

Hi. Creating something really automatic like that will take some time. We can work in improving but for now I think this will work. Just add columns in a single transformation:

= Table.ReplaceValue(#"LastStep",00000000,null,Replacer.ReplaceValue,{"Column1", "Column2", "ColumnN"})

That will work. Then if you want to add all columns with "date" type you can build a list like this to replace the text in { }.

= Table.ToList(Table.SelectColumns(Table.SelectRows(Table.Schema(STEPTABLENAME), each [Kind] = "date"), "Name"))

 Hope this helps,

Regards,