Forum Discussion

PPStar's avatar
PPStar
Helper V
2 years ago
Solved

Format Date Column

Hello. 

I have a dataflow in the power BI Service. There is a date column (Date) which needs to be as a text data type (dont ask why) and needs to be in a specific format. 

Currently the date column is in the dd/mm/yyyy format. 

I need to change this so its in the yyyy-MM-dd format , but as a string. 

 

I have added the following code

Text.From([#"Date"], "yyyy-MM-dd", "en-US") 
 
and i get the following error : 
 
Expression.Error: 3 arguments were passed to a function which expects between 1 and 2.
Details
Reason = Expression.Error
Arguments = {#date(2015, 6, 21), "yyyy-MM-dd", "en-US"}
 
WHat am i doing wrong?

 

Need Help
  • _AAndrade's avatar
    _AAndrade
    2 years ago

    Try this: Table.TransformColumns(Navigation, {{"YYYY-MM-DD", each Date.ToText(_, "yyyy-MM-dd"), type text}}) and see if this solves your problem

8 Replies

    • PPStar's avatar
      PPStar
      Helper V
      2 years ago

      Thanks. That worked. 

       

      Do you know how i can alter the existing M code - so we dont create a new column

       

      At the moment i have this M code

       

        #"Added custom" = Table.AddColumn(Navigation, "Custom", each Date.ToText([#"Date"], "yyyy-MM-dd")),
        #"Transform columns" = Table.TransformColumnTypes(#"Added custom", {{"Custom", type text}})
       
      This create a new column called Custom. Whilst this works. I want to acheive the same without creating a new column
      Can we just modify the existing column to ensure its in yyyy-MM-dd format and as text?

       

  • _AAndrade's avatar
    _AAndrade
    Resident Rockstar
    2 years ago

    Yes it's possible. Please try this:

    Table.TransformColumns(Navigation, {[#"Date"], each Date.ToText(_, "yyyy-MM-dd")})

    Instead of "Table.AddColumn(Navigation, "Custom", each Date.ToText([#"Date"], "yyyy-MM-dd"))"

     

    • PPStar's avatar
      PPStar
      Helper V
      2 years ago

      Whilst visually, this looks correct, i just tried it and it gives me this error : 

       

      Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?

      Details
      Reason = Expression.Error
       
      Any ideas
      • _AAndrade's avatar
        _AAndrade
        Resident Rockstar
        2 years ago

        I use tha name of my column Date where I want to transform.
        Probably the error is on the [#"Date"]. Replace this column name for your column date name.