In Excel sheet I havea column data where some set of records are in 'mm/dd/yyyy' format and some are in 'mm-dd-yyyy'. But I need all records in one format with data type as "DATE". How could I achieve?

I have tried by changing data type into 'date' format but while doing transformations in power bi errors occured because there data format loaded as text format. it is not allowing any date operations.

CodePudding user response：

If you can guarantee that the format is either mm/dd/yyyy or mm-dd-yyyy you can transform both your columns at the same time using the optional Culture setting you can specify for Date.FromText in Power Query.

Here is an example:

let
    Source = Table.FromRecords({
        [birthdate="8/26/1961", acct_open_date="09-10-1991"],
        [birthdate="03-07-1915", acct_open_date="8/21/1992"]
    }),
    #"Parsed Date" = Table.TransformColumns(Source,
        {
            {"birthdate", each Date.FromText(_,"en-US"), type date},
            {"acct_open_date", each Date.FromText(_,"en-US"), type date}
        })
in
    #"Parsed Date"

To implement, you will have to use the Advanced Editor or the Formula Bar. Here is a step-by-step:

1. Load your table
2. Select e.g. birthdate and click Transform > Date > Parse.
3. In your formula bar, replace the entire code with this:

= Table.TransformColumns(Source,
        {
            {"birthdate", each Date.FromText(_,"en-US"), type date},
            {"acct_open_date", each Date.FromText(_,"en-US"), type date}
        })

4. You should have the dates readily transformed now:

CodePudding user response：

You need to change the Power BI options. Power BI -> File -> Options and settings -> Options Try to change the Regional Settings to English (United States).