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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
moe_elbohsaly
Helper I
Helper I

Data Type Change

My table has a column of type text. It stores the name of the month abbreviated and current year hyphenated.

 

Under column tables tab, once I click the field, can I change the Data Type to Date? Or will this cause internal problems? 

1 ACCEPTED SOLUTION
Fowmy
Super User
Super User

@moe_elbohsaly 

It depends on how that column has been used in your model in various calculations. As per your description, it is a text field, the best practice is to creat a dates table and link it to your fact table. you create a dates table with the following"

Dates = ADDCOLUMNS(
   CALENDAR("01/01/2021","31/12/2021"),
   "Month No" , MONTH([Date]),
   "Month Name" , FORMAT( [Date] , "Mmmm" ),
   "Year" , YEAR([Date]),
   "Month Year No" , (YEAR([Date]) * 100) + MONTH	([Date]),
   "Month Year" , FORMAT( [Date] , "Mmm yyyy"),
   "Quarter" , QUARTER([Date]),
   "Year Qtr" , FORMAT( [Date] , "YYYY \QQ"),
   "Week Day" , WEEKDAY([Date],2),
   "Week" , FORMAT( [Date] , "Dddd" )

)

.


Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

2 REPLIES 2
Fowmy
Super User
Super User

@moe_elbohsaly 

It depends on how that column has been used in your model in various calculations. As per your description, it is a text field, the best practice is to creat a dates table and link it to your fact table. you create a dates table with the following"

Dates = ADDCOLUMNS(
   CALENDAR("01/01/2021","31/12/2021"),
   "Month No" , MONTH([Date]),
   "Month Name" , FORMAT( [Date] , "Mmmm" ),
   "Year" , YEAR([Date]),
   "Month Year No" , (YEAR([Date]) * 100) + MONTH	([Date]),
   "Month Year" , FORMAT( [Date] , "Mmm yyyy"),
   "Quarter" , QUARTER([Date]),
   "Year Qtr" , FORMAT( [Date] , "YYYY \QQ"),
   "Week Day" , WEEKDAY([Date],2),
   "Week" , FORMAT( [Date] , "Dddd" )

)

.


Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Thanks @Fowmy ! I managed to do a DAX namely FORMAT([text_column],"MMM-YYYY") since my text contains the month name abbreviated and current year hyphenated.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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