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.
I need change a date column to a Whole number column, whole number column should show YYYYMMDD format, I need do it with M code, I know how to step by step do it, but it too much step, Is here anyone know the easy way to change it. Thank you .
Solved! Go to Solution.
Hi @wangjuan303
The simplest way I can think of is something like this:
Number.From(Date.ToText([Date],"yyyyMMdd"))
Here is a complete sample query:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJUitWJVjLWNzRA8Awt9S2APAMDpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
#"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Date", type date}}, "en-NZ"),
#"Added Custom" = Table.AddColumn(#"Changed Type with Locale", "Date Integer", each Number.From(Date.ToText([Date],"yyyyMMdd")), Int64.Type)
in
#"Added Custom"
Regards,
Owen
Another trick. You can use this formula
idDate = Table.AddColumn(RenombraFecha, "ID_DATE", each 10000 * Date.Year([DATE_COLUMN]) + 100 * Date.Month([DATE_COLUMN]) + Date.Day([DATE_COLUMN]), Int64.Type),
Hi @wangjuan303
The simplest way I can think of is something like this:
Number.From(Date.ToText([Date],"yyyyMMdd"))
Here is a complete sample query:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJUitWJVjLWNzRA8Awt9S2APAMDpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
#"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Date", type date}}, "en-NZ"),
#"Added Custom" = Table.AddColumn(#"Changed Type with Locale", "Date Integer", each Number.From(Date.ToText([Date],"yyyyMMdd")), Int64.Type)
in
#"Added Custom"
Regards,
Owen
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |