cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Resolver I

## 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.

Thanks,

Narender

1 ACCEPTED SOLUTION
Resolver I

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

Narender

5 REPLIES 5
Super User

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) `

Proud to be a Super User!

Power BI Blog

Resolver I

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

Super User
Hi there

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

Proud to be a Super User!

Power BI Blog

Super User

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

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Resolver I

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

Narender