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
Anonymous
Not applicable

Convert text to dates in a column with mixed date types

Hi all,

 

I'm trying to do time studies using an excel source file with a monthdaytime column populated with data coming from multiple countries.  In the date column, half of the dates are written as monthday, the other half are written as daymonth.  PowerBI recognizes the monthday values as dates but classifies the daymonth values as text. 

 

How can I convert the daymonth text values into date values also?  

 

The daymonth values convert to "Error" when I try data type transform in Power Query Editor.  

 

I'm next going to try the PowerQuery DateTimeFromText function on the monthdaytime column but I don't know how this will affect the monthday values already being recognized as dates in the same column.

 

Any help appreciated!

2 ACCEPTED SOLUTIONS
natabird3
Skilled Sharer
Skilled Sharer

Spoiler
 

change type.JPGcustom column.JPGoutput.JPG

Is this what you try to achieve? You can use the custom column option and create the rules needed to do the transformation displayed in the tables. Hope this helps. 

View solution in original post

Hi @Anonymous ,

 

Do you have another column identify the date column type? For example the date 2/1, If can be first day of february or the second day of january if you do not have any other column can identify the format.

 

If you do have one, we can create a custom column depends on it easily in Power Query Editor.

 

 

if [Type] = "MMDD" then Date.FromText([Date]) else Date.FromText([Date],"fr-SN")

 

 

3.PNG4.PNG

 


BTW, pbix as attached.

 

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

12 REPLIES 12
Anonymous
Not applicable

Hello, 

I do have and download from SAP which i cant change and have mixed date formats see the example please.

any idea how to convert it to dates 

 

date
M 11.2020
M 02.2021
D 05.02.2021
D 19.02.2021
W 14.2021
D 09.04.2021
W 15.2021
W 16.2021

thanks

 

v-lid-msft
Community Support
Community Support

Hi @Anonymous ,


How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

 

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
natabird3
Skilled Sharer
Skilled Sharer

Spoiler
 

change type.JPGcustom column.JPGoutput.JPG

Is this what you try to achieve? You can use the custom column option and create the rules needed to do the transformation displayed in the tables. Hope this helps. 

Anonymous
Not applicable

Hi, thanks for your suggestion and apologize for the delayed response.

If I understand your screen shot correctly, I would create an if-then statement for each day, i.e. up to 365 if-thens?

Hi @Anonymous ,

 

We just need to create one if-else formula if you have another column for each rows, such as following tables:

 

IDFormatDate
1MMDD1/2/2019
2DDMM1/2/2019
3DDMM31/1/2019
4MMDD12/1/2019
5MMDD10/1/2019

 

We can create a custom column using formula based on the Format Column. The format column could also be the country or other value that can identify the way you want to format the date.

 

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

its definitely not a pretty way but is a quick way to get what you need. Maybe there is a way to add an if statement so that if number is between 1-31 and 1-12 or something like that but not sure to be honest as the way the data comes in power bi is usually in a different format and so having it to flip just the day and month is more difficult then if you had the year I would say.

Hello,

 

Have you tried to use a calculated column with Format.

Please have a look at the documentation link https://docs.microsoft.com/en-us/dax/custom-date-and-time-formats-for-the-format-function

 

I hope it would help

natabird3
Skilled Sharer
Skilled Sharer

if you can already easily identify which half is month year and which half is year month you can separate the columns and align them properly. Meaning if you load to power bi one column is only month year and the other is only year month you can use the conversions within Power bI like in edit queries in transform tab, you can split column by number of characters and reorder the columns to match the one that works fine and then merge them again. Then combine with the other column as it will be blank for the first half and blank for the second half once merged will be one column with date. Hope this helps. 

Anonymous
Not applicable

Thanks very much for your quick reply.  I neglected to mention the source file is live, meaning new data is added to it daily.  The solution you recommended would work for a static file but I'm hoping there's a solution that auto converts the new data coming in into a date format.   

Hi @Anonymous ,

 

Do you have another column identify the date column type? For example the date 2/1, If can be first day of february or the second day of january if you do not have any other column can identify the format.

 

If you do have one, we can create a custom column depends on it easily in Power Query Editor.

 

 

if [Type] = "MMDD" then Date.FromText([Date]) else Date.FromText([Date],"fr-SN")

 

 

3.PNG4.PNG

 


BTW, pbix as attached.

 

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-lid-msft ,

 

I'm quite noob with the coding. How to make the "Type" column at Power Query which identifying the date format, either DDmm or mmDD?


Appreciate your time and understanding. Thanks!

Because it was saying using an excel source file so i assumed is an excel file. Then you will need to write a calculated column with if statements.

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.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.