Date Format Issue in Power query

Power Query M Code Showcase
1

Hi Team,

I have date file where the entries are in “MMDDYYYY” format but when I try to convert the data type to “DATE” format from text its throwing me error in Power BI , Actually I want the date to be formatted in “MMDDYYYY” format irrespective of my system format

Is there a way to do handle this ?

Screenshot 1
image
Screenshot 2
image

Test File
Date Format Issue.csv (216 Bytes)

Thanks
Dharmendar S

1 Like
2

@Dharma Yes, you can use Power Query in Power BI to convert the text date format “MMDDYYYY” to the date format “MM/DD/YYYY” and then change the data type to date. Here are the steps:

  1. Load the data into Power Query by selecting the source file and clicking on Edit Queries.
  2. Select the column that contains the dates in “MMDDYYYY” format.
  3. Click on the “Transform” tab and select “Replace Values” from the dropdown menu.
  4. In the “Replace Values” dialog box, enter " " (a space) in both the “Value To Find” and “Replace With” fields. This will remove any spaces from the dates.
  5. Click on the “Add Column” tab and select “Custom Column” from the dropdown menu.
  6. In the “Custom Column” dialog box, enter the following formula:

= Date.FromText(Text.PadStart(Text.From([YourColumnName]), 8, “0”))

Replace “YourColumnName” with the actual name of the column containing the dates. This formula converts the text to a date and sets the format to “MM/DD/YYYY”. 7. Click on “OK” to create the new column with the formatted dates.

  1. Right-click on the new column and select “Change Type” and then “Date”.
  2. Close and apply the changes to the query.

Now you should have the dates in “MM/DD/YYYY” format regardless of your system format.

3

Its not working, and i see even month name are wrong, it should be Apr, may, Jun

image
image1002×457 20.8 KB

@Melissa requesting your help on this, I was watching your video on Data clean up like pro, but I feel this scenario is not covered

please find the pbiX file for your reference
Test.pbix (68.3 KB)

4

@Dharma Please follow this video from EDNA
I hope this will work.

1 Like
5

Hi @Dharma,

Are you mixing dates from 2 sources US and UK?
The below code assumes US dates have a “/”, forward slash.

image
image874×414 13.2 KB

If you’re not mixing dates and all should be converted from a US date format

image
image835×337 11.6 KB

Here’s your sample file:
Test Date Format Issue.pbix (74.9 KB)

Hope this is helpful

1 Like
6

Hi @Dharma

Based on the file that you attached the problem is coming from the setting in the region and source system region. The easiest solution is to set your region setting as your source system.
For instance, it seems the source system gives you CSV file set date as MM-dd-yyyy but your system is dd-MM-yyyy.
You can go to Control Panel > Region > and set Short date as MM/dd/yyyy (same as source system) then power query will easily understand your dates.

If you don’t want to change the system date, you can set your regional setting inside Power BI on Excel Power Query. You just need to go to File> Options and Settings> Options > Regional Settings and choose English (United Kingdom) and then refresh your query.

Anyway, you always have the solution to solve this issue by the M code. I suggest using the M code when you have a different date format in one column.

I can say in M language:

if “You have a different format date in a column” then “Set date from windows control panel or PBI setting” otherwise “Use M code”

1 Like
7

Hey Dharma - Sounds like a candidate for Date.FromText.

You can either first replace all “-” by “/” and use the formula:

Date.FromText( [Date], [Format = "MM/dd/yyyy" ] )

Alternatively, the following approach also works:

try Date.FromText( [Date], [Format = "MM-dd-yyyy" ] ) otherwise 
 Date.FromText( [Date], [Format = "MM/dd/yyyy" ])

You can find more on fixing this here:

1 Like
8

@RickdeGroot , thank you for jumping in here to add value.