Date Created column showing as US date

[melimob]

Hi, I have managed to import a .csv file and want to create the 'date created' within the query table.

I have managed to do this however when I change type to 'date' to remove the time stamp, it's showing in US format instead of European.

Date created = 2/6/2020 (imported as US) so is referring to 6Feb20

however as European date will read this as 2 Jun 20

any ideas how I can read a US format and show it as correct European format?

thanks

[amitchandak]

Refer :https://community.powerbi.com/t5/Service/Date-issue-dd-mm-yyyy-vs-mm-dd-yyyy/td-p/560290

https://www.oraylis.de/blog/local-date-formats-in-power-bi

https://www.reddit.com/r/PowerBI/comments/9gvfm1/changing_date_format_mmddyyyy_to_ddmmyyyy/

[HotChilli]

Power Query.

Right-click the column->Change Type->Using Locale.   Pick Date and the  English US option

[melimob]

Hi @HotChilli 

thanks for your reply.. I tried this, updating the query changing type to US in all parts but

 #"Filtered Rows" = Table.SelectRows(Source, each ([Folder Path] = "https:///Sales/Spreadsheet Reports/PipelineImport/") and ([Extension] = ".csv")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Content", "Date created"}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Removed Other Columns", {{"Date created", type datetime}}, "en-US"),
    #"Invoked Custom Function" = Table.AddColumn(#"Changed Type with Locale", "LoadCSV", each LoadCSV([Content], [Date created])),
    #"Changed Type with Locale2" = Table.TransformColumnTypes(#"Invoked Custom Function", {{"Date created", type datetime}}, "en-US"),
    #"Removed Other Columns1" = Table.SelectColumns(#"Changed Type with Locale2",{"LoadCSV"}),
    #"Expanded LoadCSV" = Table.ExpandTableColumn(#"Removed Other Columns1", "LoadCSV", {"GroupHeaderValue", "textbox9", "datecreated"}, {"GroupHeaderValue", "textbox9", "datecreated"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded LoadCSV",{{"GroupHeaderValue", "Employee"}, {"textbox9", "Total Pipeline"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Total Pipeline", Currency.Type}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type",{{"datecreated", "Report Export Date"}}),
    #"Removed Duplicates" = Table.Distinct(#"Renamed Columns1"),
    #"Filtered Rows1" = Table.SelectRows(#"Removed Duplicates", each ([Employee] <> "")),
    #"Extracted Date" = Table.TransformColumns(#"Filtered Rows1",{{"Report Export Date", DateTime.Date, type date}}),
    #"Changed Type with Locale1" = Table.TransformColumnTypes(#"Extracted Date", {{"Report Export Date", type date}}, "en-US")
in
    #"Changed Type with Locale1"

 still not working? 

[v-lid-msft]

Hi @melimob ,

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

Best regards,

[v-lid-msft · Accepted solution]

Hi @melimob ,

If you want to read "02/06/2020" as 2nd June 2020, we can try to add cultures code into the power query as following:

 #"Filtered Rows" = Table.SelectRows(Source, each ([Folder Path] = "https:///Sales/Spreadsheet Reports/PipelineImport/") and ([Extension] = ".csv")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Content", "Date created"}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Removed Other Columns", {{"Date created", type datetime}}, "gsw-FR"),
    #"Invoked Custom Function" = Table.AddColumn(#"Changed Type with Locale", "LoadCSV", each LoadCSV([Content], [Date created])),
    #"Changed Type with Locale2" = Table.TransformColumnTypes(#"Invoked Custom Function", {{"Date created", type datetime}}, "gsw-FR"),
    #"Removed Other Columns1" = Table.SelectColumns(#"Changed Type with Locale2",{"LoadCSV"}),
    #"Expanded LoadCSV" = Table.ExpandTableColumn(#"Removed Other Columns1", "LoadCSV", {"GroupHeaderValue", "textbox9", "datecreated"}, {"GroupHeaderValue", "textbox9", "datecreated"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded LoadCSV",{{"GroupHeaderValue", "Employee"}, {"textbox9", "Total Pipeline"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Total Pipeline", Currency.Type}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type",{{"datecreated", "Report Export Date"}},"gsw-FR"),
    #"Removed Duplicates" = Table.Distinct(#"Renamed Columns1"),
    #"Filtered Rows1" = Table.SelectRows(#"Removed Duplicates", each ([Employee] <> "")),
    #"Extracted Date" = Table.TransformColumns(#"Filtered Rows1",{{"Report Export Date", DateTime.Date, type date}}, "gsw-FR")
in
    #"Extracted Date"

Best regards,