Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Data format issue

Hi, 

 

i have a column in data set with format 1.2017 (as january 2017) but power BI reads it as 12-1899...?? how can i make it so is it read as january 2017? 

Thanks!

Need Help
  • MatejZukovic's avatar
    MatejZukovic
    6 years ago

    Hi Anonymous ,

     

    you can create new column, replace "." with "-" and convert Data type to Date.

     

    Month = SUBSTITUTE('Table'[Date],".","-")

    Modelling:

    Data Type : 'Date'

    Format : MMMM yyyy

     

    Hope it helps,

    Matej

     

4 Replies

  • MatejZukovic's avatar
    MatejZukovic
    Resolver I
    6 years ago

    Hi Anonymous ,

     

    you can create new column, replace "." with "-" and convert Data type to Date.

     

    Month = SUBSTITUTE('Table'[Date],".","-")

    Modelling:

    Data Type : 'Date'

    Format : MMMM yyyy

     

    Hope it helps,

    Matej

     

    • AlB's avatar
      AlB
      Community Champion
      6 years ago

      Hi Anonymous 

      You can also do it in the query editor without the need for a new column. Just click on the column with the right button of the mouse, choose "Replace value" and replace "." with "-". Then change type of the column to Date and that should be it

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      thanks, work fine.

      • v-eachen-msft's avatar
        v-eachen-msft
        Community Support
        6 years ago

        Hi Anonymous ,

         

        If your issues are solved, you could accept the reply you like.