Forum Discussion

yangty's avatar
yangty
Frequent Visitor
1 year ago
Solved

Issue with adding datetime.localnow() column in dataflow gen2

hello,    I'm stuck on this issue for a little while. I want to add a column in the dataflow for ETL_upload_datetime, so I created a new columen with formular DateTime.LocalNow() as datetime type, ...
data hub
  • V-yubandi-msft's avatar
    V-yubandi-msft
    1 year ago

    Hello yangty ,

     

    Thank you for your response. As this issue appears to be a potential bug, I suggest raising a Microsoft Fabric support ticket for further investigation.

     

    To submit a support ticket for Fabric and Power BI, please follow the steps outlined in this guide:

    How to create a Fabric and Power BI Support ticket - Power BI | Microsoft Learn

     

    If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.

V-yubandi-msft's avatar
V-yubandi-msft
Community Support
1 year ago

Hi yangty ,

Thanks for contacting the Microsoft Fabric Community. We were able to reproduce your issue. The error occurs because Power Query treats DateTime.LocalNow() differently in the editor versus when refreshing the Dataflow.

 

Add the ETL_upload_datetime Column:

  1. In the Power Query Editor, create a new column using the following formula.

Formula: DateTime.LocalNow()

 

 

2. This will add the current timestamp as a DateTime value.

 

However, if this doesn’t work as expected after refreshing, try changing the formula to.

Formula: DateTime.From(DateTime.LocalNow())

 

 

Both formulas should work. Could you please try these and let us know if you need any further assistance? We are ready to help.

 

If my response solved your query, please mark it as the Accepted solution to help others find it easily.

And if my answer was helpful, I'd really appreciate a 'Kudo'.

yangty's avatar
yangty
Frequent Visitor
1 year ago

Thank you for responding to my question, I have tried both formula but received the same error message when trying to refresh the dataflow. There is one thing I noticted that you don't have the 7 digit second precision in your screenshot, but I have the date time returns as "2025-02-18 00:43:29.9836282". Any thougths on that? could that be the cause? Thanks!

  • V-yubandi-msft's avatar
    V-yubandi-msft
    Community Support
    1 year ago

    Hi yangty ,

    Thank you for your feedback. We have not identified any issues caused by this precision. I successfully loaded the data into a test table on my Lakehouse.

     

     

    M Code:

     

    let

      Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUpJLE5RitWJVjICchJTihMhPGMgLz0jPS0DzDMB8orTUoqLwDxTIC87KwsorRQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),

      #"Changed column type" = Table.TransformColumnTypes(Source, {{"Column1", Int64.Type}, {"Column2", type text}}),

      #"Added custom" = Table.AddColumn(#"Changed column type", "Datetime", each DateTime.LocalNow()),

      #"Changed column type 1" = Table.TransformColumnTypes(#"Added custom", {{"Datetime", type datetime}})

    in

      #"Changed column type 1"

     

    Note: Try adding this last step #changed Column type1

    (Table.TransformColumnTypes(laststep, {{"Datetime", type datetime}}))

     

    If my response solved your query, please mark it as the Accepted solution to help others find it easily.

    And if my answer was helpful, I'd really appreciate a 'Kudos'.