Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I was wondering if you gurus can help. I'm trying to replace all the different dates that appear in the value column with either 0 or null whichever works in the power query. Thank you in advance.
Hi @cool_333 ,
Try this, using Table.TransformColumns:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VZBLDsUgDAPvwjpS8wGSd5aK+1+jTwWp9tIzFii+76Z+aV6uHk1aVVvyZwHMquTgjniUxMYDsOvpTuxmytw46eUuvnFRO6Rv/AOcdv4zxfIXXmfojB1eCuF1dHGwo7M7u0GTsKMJJjva4QtrPQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, ValueIni = _t]),
#"Added Custom" = Table.AddColumn(Source, "Value", each try Number.FromText([ValueIni]) otherwise Date.FromText([ValueIni])),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"ValueIni"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Date", type date}}),
#"===Start here" = #"Changed Type",
#"Replaced Date" = Table.TransformColumns(
#"===Start here",
{
{"Value", each if Value.Is(_, Date.Type) then null else _ }
}
)
in
#"Replaced Date"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
53 | |
21 | |
12 | |
12 |