Create date from year-month field

[Narender]

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

[GilbertQ]

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]

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]

Hi there

You can do it in the modeling ribbon and change it from Date/Time to Date

[TomMartens]

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

[Narender · Accepted solution]

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

Narender