Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Monthname to date

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 ...
  • Icey's avatar
    Icey
    6 years ago

    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.

    transform.gif

    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)

    date.PNG

    Best regards

    Icey

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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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

yukipilas_0-1597938679357.png

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's avatar
Icey
Icon for Community Support rankCommunity Support
6 years ago

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.

transform.gif

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)

date.PNG

Best regards

Icey

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