Monthname to date

[Anonymous]

Hello all,

i have a table with monthnames and year but no date, i would like to generate a duplicate column with a actual date, for example

March, 2018 = 01.03.2018 

it doesnt matter if the conversion is always the first of each month,

appreaciate the support 

Miguel 

[harshnathani]

Hi @lkalawski ,

In M create a Custom Colum

if [Column1] = "Jan" then #date(2018, 1, 1) else if [Column1] = "Feb" then #date(2018, 1, 2) else if [Column1] = "Mar" then #date(2018, 1, 3) else if [Column1] = "Apr" then #date(2018, 1, 4) else if [Column1] = "May" then #date(2018, 1, 5) else if [Column1] = "Jun" then #date(2018, 1, 6) else if [Column1] = "Jul" then #date(2018, 1, 7) else if [Column1] = "Aug" then #date(2018, 1, 😎 else if [Column1] = "Sep" then #date(2018, 1, 9) else if [Column1] = "Oct" then #date(2018, 1, 10) else if [Column1] = "Nov" then #date(2018, 1, 11) else if [Column1] = "Dec" then #date(2018, 1, 12) else null)

You can also use Conditional Column

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[lkalawski]

Hi @harshnathani ,

Your solution is suboptimal, because you would have to create a separate case in the code for each year. It doesn't make sense. 

_______________
If I helped, please accept the solution and give kudos! 😀

[harshnathani]

HI @lkalawski ,

Thanks for the feedback.

But the user is in the Power Query screen. 

Also, I do not see a column for year so I assume the dates are all in 2018.

Else you can combine the year Column to make it optimal.

Can you share an optimal formula in Power Query.

Thanks,

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[harshnathani]

Hi @Anonymous ,

Here is the optimised code based on @lkalawski  feedback

if [Column1] = "Jan" then [Column2]&"/1/1" else if [Column1] = "Feb" then [Column2]&"/1/2" else if [Column1] = "Mar" then [Column2]&"/1/3" else if [Column1] = "Apr" then [Column2]&"/1/4" else if [Column1] = "May" then [Column2]&"/1/5" else if [Column1] = "Jun" then [Column2]&"/1/6" else if [Column1] = "Jul" then [Column2]&"/1/7" else if [Column1] = "Aug" then [Column2]&"/1/8" else if [Column1] = "Sep" then [Column2]&"/1/9" else if [Column1] = "Oct" then [Column2]&"/1/10" else if [Column1] = "Nov" then [Column2]&"/1/11" else if [Column1] = "Dec" then [Column2]&"/1/12" else null)

Once you execute this code. You can then convert this into a Date Format in Power Query.

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[Anonymous]

Hi all, i tried the solution in the power Query but my power BY crashes:

therefore i used the first suggestion from @lkalawski.

However here the disadvantage is that is that i have to switch my table to Import moment and cannot use Direct Query,

you have any ideas ?

[Icey · Accepted solution]

Hello @yukipilas ,

I try to replay the scenario by connecting it to the SQL Server database in DirectQuery mode.

I don't think we can transform the data type in this scenario.

Therefore, it is suggested to add a new column in the database.

For the SQL Server database, try the following:

USE YourDatabase
ALTER TABLE YourTableName ADD Date_  AS CAST(CONCAT(Year_ , '/' , Month_ , '/' ,1) as date)

Best regards

Icey

If this post helps,then consider Accepting it as the solution to help other members find it more quickly.

[amitchandak]

@Anonymous ,

Try new column like these mark the as date column

Date = "01 " & [Month]  // format March, 2018. Try mark that as date

or

Date = "01-" & left([Month], len([Month])-6)  & "-" & right([month],4)

[lkalawski]

Hi @Anonymous,

You can create a new column that will contain the date:

Date = DATEVALUE(Table2[Year]&"/"&Table2[Month]&"/"&"1")

To create your format please use this:

Date = FORMAT(DATEVALUE(Table2[Year]&"/"&Table2[Month]&"/"&"1"),"dd.mm.YYYY")

Or you can create a new date table that contains different dates.

Use the formula for this: https://docs.microsoft.com/en-us/dax/calendar-function-dax

or use this post: https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Then combine the tables as appropriate and use the date that is in the newly created table.

_______________
If I helped, please accept the solution and give kudos! 😀