Forum Discussion

julesdude's avatar
julesdude
Post Partisan
3 years ago

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 call...
Need Help
BA_Pete's avatar
BA_Pete
Super User
3 years ago

Hi julesdude ,

 

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

 

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:

 

Pete

  • julesdude's avatar
    julesdude
    Post Partisan
    3 years ago

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

    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.

     

    • julesdude's avatar
      julesdude
      Post Partisan
      3 years ago

      BA_Pete 
      Just edited last post 

    • BA_Pete's avatar
      BA_Pete
      Super User
      3 years ago

       

      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