Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Convert Integer/ text to Date format

Hello all,

 

I have from date and to date columns with data as

 

From date

010318

050625

090524

 

i want to convert this to date format, when i change directly in power bi it is giving the wrong values. I also used Dax as

 

FROMDATE1 = LEFT(MC859A01[FROM DATE],2)&"/"&MID(MC859A01[FROM DATE],3,2)&"/"&RIGHT(MC859A01[FROM DATE],2)
 
but when i convert this new column to date format, its throwing error again.
 
Can anyone tell me the solution for this.
 
TIA
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    HI Anonymous 

    For your formula, you just need to keep the format of this formula same with your local system.

    For example:

    In my computer, my date format is mm/dd/yyyy

    So I adjust your formula as below:

    FROMDATE1 = MID(MC859A01[FROM DATE],3,2)&"/"&RIGHT(MC859A01[FROM DATE],2)&"/"& LEFT(MC859A01[FROM DATE],2)

    Then change the datatype to date

     

    Regards,

    Lin

10 Replies

  • JirkaZ's avatar
    JirkaZ
    Solution Specialist
    6 years ago

    Anonymous in Power Query just use:

    #date("yyyy string", "mm string", "dd string")

     

    This should work

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      JirkaZ I dint get you what you exactly mean..are you telling to create a new column in query editor. if so, column created in query editor cant be used in normal desktop

      • JirkaZ's avatar
        JirkaZ
        Solution Specialist
        6 years ago

        Anonymous that's exactly what I'm saying. Why wouldn't you be able to use a column created in Power Query in the report/desktop?

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      if possible, please give example

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

    HI Anonymous 

    For your formula, you just need to keep the format of this formula same with your local system.

    For example:

    In my computer, my date format is mm/dd/yyyy

    So I adjust your formula as below:

    FROMDATE1 = MID(MC859A01[FROM DATE],3,2)&"/"&RIGHT(MC859A01[FROM DATE],2)&"/"& LEFT(MC859A01[FROM DATE],2)

    Then change the datatype to date

     

    Regards,

    Lin

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    6 years ago

    Hi,

    This M code works

    let
    Source = Excel.CurrentWorkbook(){[Name="Dates"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Date.From(Text.From("20"&[From Date], "en-US")))
in
    #"Added Custom"

    Hope this helps.