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
alvin199
Helper III
Helper III

Problems on data data type

I have a text column data which is national ID. The first 2 characters are Year, following 2 characters are Month and the next 2 characters are Day. 

 

alvin199_0-1641961408274.png

 

I have use Extract > Range in Add Column menu of Power Query to seperate out 3 columns for the first 6 digit.  So I have Year, Month and Day column. Each of these column is having 2 characters in Text data type. 

alvin199_1-1641961692023.png

 

Next, I highlighted these 3 columns and right click to select Merge Columns, seperator is /  . The output is like this

alvin199_2-1641961841500.png

 

Then, I change the data type to Date data type. However, it comes out with Error. 

alvin199_3-1641961936653.png

 

How to solve this errror?

3 REPLIES 3
alvin199
Helper III
Helper III

This works.

Dates_try =
FORMAT(
table[column],
"mm/dd/yyyy"
)
Jihwan_Kim
Super User
Super User

Hi,

I think your setting is mm/dd/yyyy, but you want dd/mm/yyyy. (Or, other way round)

 

Please try the below.

-Place the cursor on the column name [Dates]

-Right click

-select "Change Type" -> "Using Locale"

-select "Data Type" = "Date"

-select "Locale" = English (United Kingdom) or your setting.

 

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here and Go to My LinkedIn Page


amitchandak
Super User
Super User

@alvin199 , append as year-month-date

 

or create a new column like - Power query

#date([Year],[Months], [Days])

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.