Problem with date format

Power Query
1

Hi

I have a problem with my date field where I cannot change the format to date. If I change the format to date, it gives Error in all the rows. I’ve tried splitting the field and then merge but I get the same result.

image
image323×614 8.55 KB

2

Hi @BM_Girlracer,

Check out this article

I hope this is helpful

3

@BM_Girlracer In such cases change the data type using “Using Locale”

image

1 Like
4

Hi there,

Please take the following links into your further consideration to solve this issue:

1 - https://www.excelguru.ca/blog/2015/07/08/fix-date-errors/
2 - https://www.youtube.com/watch?v=qRjsf-tvsoA

Would be better if you provided your file.

Good luck…

5

Hi,

Useful links but it didn’t work for this case. I am still getting an error. Here’s the file.

Date Issue.csv (3.5 KB)

6

@BM_Girlracer

I solved this using the file you included by following these steps

  1. Promoted Headers

  2. Changed type to Date/Time

  3. Changed type (adding a new step, not accepting the ‘replace current’) to Date

    let
    Source = Csv.Document(File.Contents(“C:\Users\hrowe\Downloads\Date Issue.csv”),[Delimiter=“,”, Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #“Promoted Headers” = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #“Changed Type” = Table.TransformColumnTypes(#“Promoted Headers”,{{“Start Date”, type datetime}, {“End Date”, type datetime}}),
    #“Changed Type1” = Table.TransformColumnTypes(#“Changed Type”,{{“Start Date”, type date}, {“End Date”, type date}})
    in
    #“Changed Type1”

image

I believe the problem is because your dates came into PowerBi as text values (due to the CSV formatting)

7

Hi Heather

I tried the steps you’ve mentioned but not all got converted, some have errors.

image
image322×613 10.5 KB

I even tried changing it to type > using locale and it only change the ones without error. Also, my date format is UK DD/MM/YYYY but the data is in MM/DD/YYYY

8

must be the formatting issue, everything converted in my test - but I’m in the US, so it was easier for the file to read the dates.

I suppose you could do the transformation using something like column by example to extract the date and then convert it

9

@BM_Girlracer I changed my system setting to UK format and this works:

image
image1404×811 56 KB

10

Hi @BM_Girlracer. Your composite values (some with AM/PM, some without) is, I think, causing the conversion issues. That, and because of your mention of your locale being different from the dataset, suggest to me that a more “brute force” method would prove beneficial, where you extract the various components youself into separate new text columns (i.e., Year, Month, Day, Hour, Minute, Second), then combine the new Year, Month, and Day columns to get your date value, which can then be converted into a Date properly. I know @sam.mckay did a video showcasing this technique, but I can’t seem to lay my hands on it right now (sorry).

Hope this helps.
Greg

1 Like
11

@BM_Girlracer I think that @Greg described the problem correctly. You need to take 2 steps:

  1. extract the date text before the space delimiter.
  2. convert to date using locale

Edit:
As datetime with locale should work as well, you can do also these 2 steps:

  1. change type with locale to datetime
    Table.TransformColumnTypes(#"Promoted Headers", {{"Start Date", type datetime}}, "en-US")
  2. change type to date
1 Like
12

Thanks! Problem fixed :slight_smile: