2

I have an expression right now that exports the table while removing unnecessary columns:

EVALUATE
ALLEXCEPT(TABLE, TABLE[M],TABLE[N])

In the table there are columns that have dates that when brought into CSV end up like: enter image description here

Is there a way to add a parameter to the EVALUATE expression so that the format can be changed to show up like "mm/dd/yyyy" (ex.12/19/2014) ? As opposed to the comma separated interpretation? Is there a function that can be applied to all date containing fields at the same time (all dates need to be in same format) or does it have to apply per column?

The dates are in 'text' datatype in power bi

Something like this: enter image description here

konsama
  • 327
  • 2
  • 12

1 Answers1

0

Are you looking for the FORMAT function? https://dax.guide/format/? I guess you want to iterate over the table and add a new column

EVALUATE
ADDCOLUMNS (
    ALLEXCEPT ( TABLE, TABLE[M], TABLE[N] ),
    "New Date column", FORMAT (
        DATE ( LEFT ( Date, 4 ), MID ( Date, 5, 2 ), RIGHT ( Date, 2 ) ),
        "yyyy-mm-dd"
    )
)

But a transformation like this could also be done by using Power Query (transform data in PBI).

rasenkantenstein
  • 357
  • 1
  • 2
  • 16
  • I have columns in the table that have date values from power Bi but the show up as comma separated in Excel like described above. I was wondering if there was a way to format the values in the column (without adding new columns) I saw that there was a specific date format 'ddddd' in DAX but didn't know how to apply it into the function provided. – konsama Jun 28 '20 at 20:16
  • Ah, I get it. You wanted to have an option like SET DATEFORMAT for T-SQL? – rasenkantenstein Jun 29 '20 at 12:21
  • I'm not certain of the T-SQL reference, but possibly. In power bi I did format the date but the date shows up as 00:00 when exported then I added another step in query to change type to text and when exported to excel it read it as the first image attached. I would like for a way, I was thinking that this may be possible in DAX studio, to do this in a manner where the date value is interpreted like the second image. – konsama Jun 29 '20 at 14:48