Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi experts!
I have a calumn of that format:
DateTime |
202201311529530000 |
202201311829530000 |
000000 |
000000 |
Now I would like to transform that into this:
DateTime |
31.01.2022 15:29:53 |
31.01.2022 18:29:53 |
How is this possible ussing Power Query?
Since there are a lot of columns with that kind of format I would like to use the solution as a function.
Solved! Go to Solution.
A possible solution (ignoring those last 4 zeros)
Place the following M code in a blank query to see the steps.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMjIwNDY0NDWyNDU2AAKlWB0kYQsUYQMDDGYsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DateTime = _t]),
#"Added Custom" = Table.AddColumn(Source, "Custom", each if [DateTime]= "000000" then null else #datetime(Number.From(Text.Start([DateTime], 4)), Number.From(Text.Middle([DateTime], 4,2)), Number.From(Text.Middle([DateTime], 6,2)), Number.From(Text.Middle([DateTime], 8,2)),Number.From(Text.Middle([DateTime], 10,2)), Number.From(Text.Middle([DateTime], 12,2))), type datetime),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"DateTime"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "DateTime"}})
in
#"Renamed Columns"
|
Please accept the solution when done and consider giving a thumbs up if posts are helpful. Contact me privately for support with any larger-scale BI needs, tutoring, etc. |
You could use a function like this and create a custom column using the function to do the conversion, then delete the original column. See it all at work in the attached file.
(input_ as text) =>
if input_= "000000" then null else #datetime(Number.From(Text.Start(input_, 4)), Number.From(Text.Middle(input_, 4,2)), Number.From(Text.Middle(input_, 6,2)), Number.From(Text.Middle(input_, 8,2)),Number.From(Text.Middle(input_, 10,2)), Number.From(Text.Middle(input_, 12,2)))
|
Please accept the solution when done and consider giving a thumbs up if posts are helpful. Contact me privately for support with any larger-scale BI needs, tutoring, etc. |
You could use a function like this and create a custom column using the function to do the conversion, then delete the original column. See it all at work in the attached file.
(input_ as text) =>
if input_= "000000" then null else #datetime(Number.From(Text.Start(input_, 4)), Number.From(Text.Middle(input_, 4,2)), Number.From(Text.Middle(input_, 6,2)), Number.From(Text.Middle(input_, 8,2)),Number.From(Text.Middle(input_, 10,2)), Number.From(Text.Middle(input_, 12,2)))
|
Please accept the solution when done and consider giving a thumbs up if posts are helpful. Contact me privately for support with any larger-scale BI needs, tutoring, etc. |
A possible solution (ignoring those last 4 zeros)
Place the following M code in a blank query to see the steps.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMjIwNDY0NDWyNDU2AAKlWB0kYQsUYQMDDGYsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DateTime = _t]),
#"Added Custom" = Table.AddColumn(Source, "Custom", each if [DateTime]= "000000" then null else #datetime(Number.From(Text.Start([DateTime], 4)), Number.From(Text.Middle([DateTime], 4,2)), Number.From(Text.Middle([DateTime], 6,2)), Number.From(Text.Middle([DateTime], 8,2)),Number.From(Text.Middle([DateTime], 10,2)), Number.From(Text.Middle([DateTime], 12,2))), type datetime),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"DateTime"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "DateTime"}})
in
#"Renamed Columns"
|
Please accept the solution when done and consider giving a thumbs up if posts are helpful. Contact me privately for support with any larger-scale BI needs, tutoring, etc. |
@AlB : THanks a lot! It works perfectly well. How can I switch this into a individual function / query? I would like to use this functionn for multiple columns
Hi @joshua1990
Are the last four zeros to be ignored? If not, please show an example in which those positions are not zero with the expected output
|
Please accept the solution when done and consider giving a thumbs up if posts are helpful. Contact me privately for support with any larger-scale BI needs, tutoring, etc. |