Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
julesdude
Post Partisan
Post Partisan

Need Help Handling Different Date Time Formats Imported from Multiple Excel Workbooks

Hi,

In Power Query in my Power BI I am consolidating a number of Excel sheets from different Excel workbooks in a SharePoint folder. Each week this is updated. All of the workbooks have a sheet called Data and in that sheet is a table of exported data from a system. One of the columns in the table is called "CreationDate". For a while now, the format files imported for this column have been presented like this:
"6/14/2023 11:48:39 AM" (as it appears when viewed in the original Excel workbook. The Format option is Custom > "m/d/yyyy h:mm". When I import this into Power BI Power Query and have the data type set to Text, I get it as "14/06/2023 11:48:39"
However, recently the exported data has changed slightly so that the "CreationDate" column now has the date format like this - "7/19/2023 2:05:47 AM" with the Format set to "General". When I import this into Power BI Power Query and have the data type set to Text, I get it as "7/19/2023 2:05:47 AM".
So it looks as if the AM and PM is now being added and because this is now a different format for the newly exported files, when Power Query imports them changing column type to Date Time I get the date in DateCreation column replaced with "Error" for these new formats.

How can I handle this change in format so that "Error" is not shown instead of the date and both old and new date formats can be processed?

The error message itself appears as this when I click on it:

DataFormat.Error: The specified culture is not supported.
Details:
dd/mm/yyyy hh:mm:ss

4 REPLIES 4
BA_Pete
Super User
Super User

Hi @julesdude ,

 

You shoudl be able to change the data type of this column using locale:

BA_Pete_1-1690371126951.png

 

For the following example, I set the locale settings to Data Type = Date/Time and Locale = English (United States).

 

Example query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtM3NNE3MjAyVjA0tDKxsDK2VIrVwSas4OiLSyYAImOub2gJkTGyMjC1MjGHacGUAOmIBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [datetime = _t]),
    duplicateDatetime = Table.DuplicateColumn(Source, "datetime", "datetimeWithLocale"),
    chgTypeWithLocale = Table.TransformColumnTypes(duplicateDatetime, {{"datetimeWithLocale", type datetime}}, "en-US")
in
    chgTypeWithLocale

 

Example output:

BA_Pete_0-1690371059423.png

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Thanks @BA_Pete 
I tried both Locale > US and UK and no joy - still those errors unfortunately:

julesdude_0-1690373956678.png

julesdude_1-1690373983576.png

EDIT: I think part of the issue here is that the date format is from two different locales - so when a colleague pulls the NEW exports from his system, he gets it in US format but also with the AM and PM , and for the historical files, they were pulled in the UK so UK format of dd/mm and without the AM PM.

 

 

The problem you have here is that many dates can be interpreted as either UK or US format e.g. 6/7/23 or 11/9/23 etc. so you have to be able to tell PQ which conversion it should be using for any given datetime value.

If possible, I think you'll need to identify the exact date at which the locale format changed at source, then create a new custom column to evaluate the conversion required at each date.

Do you have another date column of a known and consistent locale to use as a reference point, like [Report Date] or [Export Data] or similar?

 

If yes, we can look at the conditional column option.

If no, then I think you'll need to segregate your data import by 'Old Locale' and 'New Locale', perform the relevant conversion on each, then append the two together.

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




@BA_Pete 
Just edited last post 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors