Facing issue with Date column format in Power BI

0
votes

I have an excel dateset where date column includes date in dd-mm-yyyy format(Proper date) and in text format that too in mm/dd/yyyy format(just text not proper date). want to convert all dates in dd-mm-yyyy format. How to do all in dd-mm-yyyy format in Power Query editor

The question is: In Power Query Editor Perform all necessary action to convert all the dates in the "Date" column to be in "dd-mm-yyyy" format.

I am new in Power BI. Please help on this.

enter image description here

1
powerquerypowerbi-desktop
below is part of the date column. data is there in this way. Order Date 7/27/2012 9/14/2013 5/15/2015 5/17/2017 10/26/2016 11-07-2011 1/18/2013 11/30/2016 3/23/2017 5/23/2016 02-09-2014 2/18/2011 1/28/2013 6/20/2011 04-04-2010 6/19/2014 3/28/2011 07-07-2011 12-07-2015 1/19/2011 12/31/2010 10/28/2015 4/13/2015 05-01-2015 8/25/2011 02-10-2016 2/28/2015 6/13/2011 6/23/2012 09-01-2011 11/13/2014 11-03-2013 12-05-2016 09-08-2013 3/22/2014 11-01-2011 1/15/2016 11/29/2010 11/19/2011 07-06-2016 11-11-2015 5/28/2013 – Sudip Narayan Choudhury

1 Answers

0
votes

Importing correctly

If you set the right culture, all the inputs are converted to a type date

let
    Source = #table(
        type table[Date = text],
        { {"7/27/2012"}, {"9/14/2020"}, {"04-11-2020"} }
    ),

    // using the system's current culture
    #"Dates" = Table.TransformColumnTypes(
        Source,
        { {"Date", type date} }
    ),

    //specify a specific culture
    #"US Dates" = Table.TransformColumnTypes(
        Source,
        { {"Date", type date} },
        "en-US"
    )
in
    #"US Dates"

date verses text

I'm not clear if you're referring to different date format strings, or actual data types when you say Proper Date, I think you mean the first.

In the query editor where you import dates, it should be type date If it visually displays out of order, that's okay. It's using your systems current settings.

Once you're in the data model, that is when you pick your date format strings. Notice that both images use the same data, the green is the data type in Power Query.

Blue is the format string that lets you override the defaults.

enter image description here