Forum Discussion

Narender's avatar
Narender
Resolver I
8 years ago
Solved

Create date from year-month field

Hi All,

 

I have a field in yyyy-mm (like 2018-01) format.

 

I need to create this field to complete date dd-mm-yyyy (like 12-01-2018). days can be any.

 

Please suggest me.

 

 

Thanks,

 

 

Narender

 

 

Need Help
  • Narender's avatar
    Narender
    8 years ago

    I did this in power query . Convert date type -> date.It has added 01 day with each yyyy-mm field.

     

     

    Narender

5 Replies

  • GilbertQ's avatar
    GilbertQ
    Super User
    8 years ago

    Hi there if you go into Query Editor, you can add a new Columns from Examples and then put in what you want it to be as shown above.

     

    If that does not work, you can add a Custom Column and put in the following:

     

    "01-" & Text.End(Text.From([Column with yyyy-mm]),2) & "-" & Text.Start(Text.From([Column with yyyy-mm]),4)
    • Narender's avatar
      Narender
      Resolver I
      8 years ago

      Thanks GilbertQ.

       

      I have 1 more question.

       

      I used a script for calender.

       

      Dates 5 =
      GENERATE (
      CALENDAR( DATE( YEAR( TODAY() ) - 4, MONTH( TODAY() ), DAY( TODAY()) ), TODAY()),
      VAR startOfWeek = 1 // Where 1 is Sunday and 7 is Saturday, thus a 3 would be Tuesday
      VAR currentDay = [Date]
      VAR days = DAY( currentDay )
      VAR months = MONTH ( currentDay )
      VAR years = YEAR ( currentDay )
      VAR nowYear = YEAR( TODAY() )
      VAR nowMonth = MONTH( TODAY() )
      VAR dayIndex = DATEDIFF( currentDay, TODAY(), DAY) * -1
      VAR todayNum = WEEKDAY( TODAY() )
      VAR weekIndex = INT( ROUNDDOWN( ( dayIndex + -1 * IF( todayNum + startOfWeek <= 6, todayNum + startOfWeek, todayNum + startOfWeek - 7 )) / 7, 0 ) )
      RETURN ROW (
      "Day", days,
      "Month", months,
      "Year", years,
      "Day Index", dayIndex,
      "Week Index", weekIndex,
      "Month Index", INT( (years - nowYear ) * 12 + months - nowMonth ),
      "Year Index", INT( years - nowYear )
      )
      )

       

      It is showing date in dd-mm-yyyy hh:mm:ss formate.

       

      I need to change this date formate to dd-mm-yyyy (exclude the hh:mm:ss).

       

      Please let me know the how i can modify this date from above calender script.   

       

      I can do via modeling, but i want to know how to do in script. 

       

       

       

      Thanks,

       

      Narender

      • GilbertQ's avatar
        GilbertQ
        Super User
        8 years ago
        Hi there

        You can do it in the modeling ribbon and change it from Date/Time to Date
  • Narender's avatar
    Narender
    Resolver I
    8 years ago

    I did this in power query . Convert date type -> date.It has added 01 day with each yyyy-mm field.

     

     

    Narender

  • TomMartens's avatar
    TomMartens
    Super User
    8 years ago

    Hey,

     

    I'm wondering why you have to because if I enter values like this (using enter data)

     

    The query editor changes this automatically to a datetime column

     

    The formula for the automatic step will look like this:

    = Table.TransformColumnTypes(Source,{{"DateIncomplete", type date}})

    Wondering if this does not happen in your table

     

    Regards

    Tom