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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.