Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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,
Happy to help!
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.
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,
Happy to help!