Using DAX Studio to export Tables from Power BI for date formatting for Excel output

user_9886515 0
Viewed 624

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

daxstudio dax
1 Answers

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).

user_56909400
Related