cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
erezb Frequent Visitor
Frequent Visitor

Converting from Text to Date

Hi

all,

 

How can I extract out from a date field the month and year arguments and create a new coloum with the format:

 

mm\yyyyy

 

I have an original date column ("minnipuk") holding the data in a dd/mm/yyyy format.

 

29/09/2015

 

what I need is - 9-2015 or even better sep-2015.

 

I did a new measure: in the query editor 

                                                               Text.From(Date.Month([minnpk]))
                                                                &"-"
                                                                &Text.From(Date.Year([minnpk]))

 

 

but when I used it in an x axis inside a graph the data is not sorted correctly (9-2016 comes after 10-2016  ), and  I think I understand why:It's a text not a date.

 

Is it possible to make the new column I have created a date type?

Should It be done in the query editor or inside the report itself?

 

Thanks

Erez 

 

 

 

 

 

 

 

 

3 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Converting from Text to Date

Do you have dedicated continuous calendar table in your data model which is best practice for time intelligence based reporting. If you dont have this table, you can create one, here is the link

 

https://kohera.be/blog/business-intelligence/how-to-create-a-date-table-in-power-bi-in-2-simple-step...

 

Although see below on how to extract Month and Year and you need to sort this column by other column to get month order correctly.

 

Column 2 = FORMAT('Table'[Date], "MM/YY")
Column = FORMAT('Table'[Date], "MMM/YY")





Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos Smiley Happy
Feel free to email me with any of your BI needs.





View solution in original post

Phil_Seamark Super Contributor
Super Contributor

Re: Converting from Text to Date

You say you also want a column to be "what I need is - 9-2015 or even better sep-2015."

 

Add a column to your DATE table

 

Month = FORMAT('Dates'[Date],"mmm-YYYY")

 

To get this sorted correctly also add the following column

 

MonthID = INT(format('Dates'[date],"YYYYMM")

Once you have these two columns,  Set your Month column to be sorted by your MonthID column.

 

Month Sort.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

erezb Frequent Visitor
Frequent Visitor

Re: Converting from Text to Date

Thanks, parry2k.

 

I used your  second soultion and it works fine.

 

I'm not quiet sure I have understood your first solution. I did try to  creatie this and got a message "The syntax for ';' is incorrect" I'm  not sure why it occurs.

 

However, suppose I have managed to create the table and also have a 2'nd table with my impoted data that contains a date field,

Do you mean there should be some kind of join between them by the date fields (formated : dd/mm/yyyy) and than get the corresponding date format I'm intersted in ("mm/yyyy") from the date table.

 

Thanks again

 

View solution in original post

10 REPLIES 10
Super User
Super User

Re: Converting from Text to Date

Do you have dedicated continuous calendar table in your data model which is best practice for time intelligence based reporting. If you dont have this table, you can create one, here is the link

 

https://kohera.be/blog/business-intelligence/how-to-create-a-date-table-in-power-bi-in-2-simple-step...

 

Although see below on how to extract Month and Year and you need to sort this column by other column to get month order correctly.

 

Column 2 = FORMAT('Table'[Date], "MM/YY")
Column = FORMAT('Table'[Date], "MMM/YY")





Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos Smiley Happy
Feel free to email me with any of your BI needs.





View solution in original post

Phil_Seamark Super Contributor
Super Contributor

Re: Converting from Text to Date

You say you also want a column to be "what I need is - 9-2015 or even better sep-2015."

 

Add a column to your DATE table

 

Month = FORMAT('Dates'[Date],"mmm-YYYY")

 

To get this sorted correctly also add the following column

 

MonthID = INT(format('Dates'[date],"YYYYMM")

Once you have these two columns,  Set your Month column to be sorted by your MonthID column.

 

Month Sort.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

erezb Frequent Visitor
Frequent Visitor

Re: Converting from Text to Date

Thanks, parry2k.

 

I used your  second soultion and it works fine.

 

I'm not quiet sure I have understood your first solution. I did try to  creatie this and got a message "The syntax for ';' is incorrect" I'm  not sure why it occurs.

 

However, suppose I have managed to create the table and also have a 2'nd table with my impoted data that contains a date field,

Do you mean there should be some kind of join between them by the date fields (formated : dd/mm/yyyy) and than get the corresponding date format I'm intersted in ("mm/yyyy") from the date table.

 

Thanks again

 

View solution in original post

Anonymous
Not applicable

Re: Converting from Text to Date

"I'm not quiet sure I have understood your first solution. I did try to  creatie this and got a message "The syntax for ';' is incorrect" I'm  not sure why it occurs."

Try changing the ';' to ','
(Change the semicolon to a comma in all places)

dchapman Frequent Visitor
Frequent Visitor

Re: Converting from Text to Date

I need to convert 0h 00m to hours.  I haven't been able to find a solution and saw this post about converting text to date.  The text is different in each row.  Any suggestions would be helpful.  

Phil_Seamark Super Contributor
Super Contributor

Re: Converting from Text to Date

Hi @dchapman

 

This might work

 

    HOUR(
        TIMEVALUE(
            SUBSTITUTE(
               SUBSTITUTE("0h 00m","h",":") ,
                "m","")
            )   
        )

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

dchapman Frequent Visitor
Frequent Visitor

Re: Converting from Text to Date

Hi Phil, 

 

Thank you for the formula.  I tried adding a custom column using that formula but I may have done it incorrectly.  Where do I put in a reference to the original column data?  Or should I be doing something different? 

 

This is what I have in my function bar: 

= Table.AddColumn(#"Filtered Rows4", "Custom", each HOUR( TIMEVALUE( SUBSTITUTE( SUBSTITUTE("0h 00m","h",":") , "m","") ) ))

 

I'm really new at using Power Query and not finding a lot of helpful information from Microsoft on how to use this product. 

 

Thanks,

Derik 

Phil_Seamark Super Contributor
Super Contributor

Re: Converting from Text to Date

This code will work as a calculated column in Power BI Desktop (not Power Query)

 

 

New Column = 
HOUR( TIMEVALUE( SUBSTITUTE( SUBSTITUTE([your column here],"h",":") , "m","") ) )

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Anonymous
Not applicable

Re: Converting from Text to Date

Hello!  This is actually my first post being a new user in power query.  

 

I have a text date in "MMM" format.  How can I convert it to the current date?

 

Thnak you in advance.

 

 

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 430 members 4,064 guests
Please welcome our newest community members: