Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Narender
Resolver I
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.

 

Please suggest me.

 

 

Thanks,

 

 

Narender

 

 

1 ACCEPTED SOLUTION
Narender
Resolver I
Resolver I

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

 

 

Narender

View solution in original post

5 REPLIES 5
GilbertQ
Super User
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) 




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

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

Hi there

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

TomMartens
Super User
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

image.png

 

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
Narender
Resolver I
Resolver I

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

 

 

Narender

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.