problem in converting text format to date format in Power Query

[FJME]

Dear community !

I downloaded an entire table in format csv to power query and since it is in text, i changed to date format  but  errors occur in "dates" with the days in the place of month (portuguese format) as in attach. I tried different solutions (functions, column to date) in the source and in power query but the same results remain. The only "patch" was to change the format in the desktop sids (table view) but the errors persist in Power Query...

Thanks

[amustafa · Accepted solution]

I did a test with sample csv file...

id,Date
1,1/11/1990
2,1/12/1990
3,1/13/1990

Using the M Code below it worked fine. 

let
Source = Csv.Document(File.Contents("C:\Users\aliom\OneDrive\Desktop\Sample.csv"),[Delimiter=",", Columns=2, Encoding=65001, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Inserted Parsed Date" = Table.AddColumn(#"Promoted Headers", "Parse", each Date.From(DateTimeZone.From([Date])), type date),
#"Changed Type" = Table.TransformColumnTypes(#"Inserted Parsed Date",{{"Parse", type date}})
in
#"Changed Type"

[collinsg · Accepted solution]

Hi FJME,

Here's an approach you might try. The #"Convert Date" step uses "Date.From" with the "locale" parameter "en-US". This interprets "Date" as being in the format "M/d/yyyy".

let
  Source = Csv.Document(
  File.Contents("C:\Users\Appin\test.csv"),
    [
      Delimiter = ",",
      Columns = 2,
      Encoding = 65001,
      QuoteStyle = QuoteStyle.None
    ]
  ),
  #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
  #"Convert Date" = Table.TransformColumns(#"Promoted Headers", {{"Date", each Date.From(_, "en-US")}}),
  #"Changed Type" = Table.TransformColumnTypes(#"Convert Date", {{"Date", type date}})
in
  #"Changed Type"

 Hope this helps.

[collinsg · Accepted solution]

Hi FJME,

Here's an approach you might try. The #"Convert Date" step uses "Date.From" with the "locale" parameter "en-US". This interprets "Date" as being in the format "M/d/yyyy".

let
  Source = Csv.Document(
  File.Contents("C:\Users\Appin\test.csv"),
    [
      Delimiter = ",",
      Columns = 2,
      Encoding = 65001,
      QuoteStyle = QuoteStyle.None
    ]
  ),
  #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
  #"Convert Date" = Table.TransformColumns(#"Promoted Headers", {{"Date", each Date.From(_, "en-US")}}),
  #"Changed Type" = Table.TransformColumnTypes(#"Convert Date", {{"Date", type date}})
in
  #"Changed Type"

 Hope this helps.

[FJME]

Thanks everyone!

A little distraction because i tried in a first moment to change the regional settings to English (USA) that didn't appears (but Portuguese (Inglês) instead was the correct...). However the M suggestions are also correct.

regards

FE

[amustafa]

On row 13, click next to the error and paste the error here. It will show the vaule it's having a problem converting to Date.

[FJME]

Hi amustafa!

He don't accept above 12 as month...in think that is the reason. I tried regional stteings....date.value,etc without success

Regards

[ronrsnfld · Accepted solution]

All you should need to do is set the proper locale in the #"Changed Type" step (or whatever it is called in Portuguese.

The locale refers to the format of the text date. Since it seems to MDY which is a US format, you set the locale accordingly.

Or if you are doing this from the UI, right click on the column and select to Change Type to date using locale:

Then set the locale to English-United States and that should correct things.

If you are working in the Advanced Editor, add the culture argument to the #"Changed Step" step:

...
#"Changed Step" = Table.TransformColumnTypes(previous_step, { {"Date", type date}},"en-US")
...

[amustafa · Accepted solution]

I did a test with sample csv file...

id,Date
1,1/11/1990
2,1/12/1990
3,1/13/1990

Using the M Code below it worked fine. 

let
Source = Csv.Document(File.Contents("C:\Users\aliom\OneDrive\Desktop\Sample.csv"),[Delimiter=",", Columns=2, Encoding=65001, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Inserted Parsed Date" = Table.AddColumn(#"Promoted Headers", "Parse", each Date.From(DateTimeZone.From([Date])), type date),
#"Changed Type" = Table.TransformColumnTypes(#"Inserted Parsed Date",{{"Parse", type date}})
in
#"Changed Type"

[FJME]

Thanks a lot amustafa! I left now my daily job but tomorrow i will test in the morning and give you feedback. Regards. FE