Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I have a column as DateTime which is connected to excel files and users have keyed in TEXT value in source excel file.
This will keep happening again and again, How do I write a logic in Power Query that converts all TEXT values entered in this column to null
Solved! Go to Solution.
Do a transformation step on your column and then tweak the code to look like this:
= Table.TransformColumns(Source, {{"Planned Finish Date & Time",
each try DateTime.FromText(_) otherwise null, type datetime}})
Here's a full code sample you can paste into the Advanced Editor:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtI1MNQ1MA0xMLYyNrYyNNIzMjU3AAKlWJ1oJTARklpRohQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Planned Finish Date & Time" = _t]),
#"Text to Nulls" = Table.TransformColumns(Source,{{"Planned Finish Date & Time", each try DateTime.FromText(_) otherwise null, type datetime}})
in
#"Text to Nulls"
Hi @smjzahid ,
You could follow @AlexisOlson 's suggestion to use error handling function Try... Otherwise
Or add a replaced error step after changing type:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Do a transformation step on your column and then tweak the code to look like this:
= Table.TransformColumns(Source, {{"Planned Finish Date & Time",
each try DateTime.FromText(_) otherwise null, type datetime}})
Here's a full code sample you can paste into the Advanced Editor:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtI1MNQ1MA0xMLYyNrYyNNIzMjU3AAKlWJ1oJTARklpRohQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Planned Finish Date & Time" = _t]),
#"Text to Nulls" = Table.TransformColumns(Source,{{"Planned Finish Date & Time", each try DateTime.FromText(_) otherwise null, type datetime}})
in
#"Text to Nulls"
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
User | Count |
---|---|
39 | |
20 | |
20 | |
13 | |
13 |