Transform Datetime column to add an hour at query stage

[jasmith866]

I've managed to create a custom column which adds an hour to a datetime field by referencing it and adding + #duration(0,1,0,0)

What i'd like to do is instead transform the original column to add the hour there, as a lot of the rest of the report is driven by the first field and I'd rather not update all the references to a new vustom field - can this be done? Everything i've tried re: transform column hasn't worked

[v-eqin-msft · Accepted solution]

Hi @jasmith866 ,

Sorry for my misunderstanding...

You could use the following in Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtC31DdUMDSwMgAhpVgduKiRgqEhpqixgqGRlTG6qImCpZWpKVBcKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DateTime = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DateTime", type datetime}}),
    Custom1 = Table.TransformColumns(#"Changed Type",{"DateTime",each _+#duration(0,1,0,0)})
in
    Custom1

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.

Best Regards,
Eyelyn Qin

[v-eqin-msft]

Hi @jasmith866 ,

According to my understand, you want to create a new column which add a hour based on the original DateTime column , right?

1.In Power Query -->Add a custom column using the M code as follows:

2.Use the following DAX formula to add a new calculate column:

Column =

[DateTime] + 1 / 24

The final result looks like this:

Did I answer your question ? Please mark my reply as solution. Thank you very much.

If not, please upload some insensitive data samples and expected output.

Best Regards,

Eyelyn Qin

[jasmith866]

Thanks for your reply. Not quite - i'm looking to transform the original column, not create a new one

[amitchandak]

@jasmith866 , nor very create

New column = [column]+ #duration(0,1,0,0)