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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.