Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Guys,
I have a small issue in my current dataset. The value of the column IDT_DTM_ID consist a wrong date value. Check the image here below.
I would like to transform 20170102 into a date type 02-01-2017.
Can you help me out?
Kind regards,
Rega
Solved! Go to Solution.
In the Query Editor, you can click on the Transform tab, then the Data Type dropdown and select Date.
If that doesn't work then maybe regional date settings are messing it up (Power BI might expect year, day, month instead of year, month, day) .
If so, you can try extracting data into three new columns, then merging them back together into a date format that Power BI can recognize. For example:
1. Click Add Column tab and select Extract > Range and set Starting Index to 4 and Number of Characters to 2. That will get you the month in its own column
2. Select Extract > Last Characters and set Count to 2. That will get you the day.
3. Select Extract > First Characters and set Count to 4. That will get you the year
4. Click the Transform tab. Select the three new columns and click Merge Columns and set the separator to Custom, use a hyphen as the separator, then enter a column name
5. Finally change the Data Type to Date.
(If this doesn't work, go back to step 2 and swap the order of the day and month columns and try again)
Convert the date int into text and from text into date with the following function combination.
Date.From( Text.From( [Date_Int_Column] ) )
In the Query Editor, you can click on the Transform tab, then the Data Type dropdown and select Date.
If that doesn't work then maybe regional date settings are messing it up (Power BI might expect year, day, month instead of year, month, day) .
If so, you can try extracting data into three new columns, then merging them back together into a date format that Power BI can recognize. For example:
1. Click Add Column tab and select Extract > Range and set Starting Index to 4 and Number of Characters to 2. That will get you the month in its own column
2. Select Extract > Last Characters and set Count to 2. That will get you the day.
3. Select Extract > First Characters and set Count to 4. That will get you the year
4. Click the Transform tab. Select the three new columns and click Merge Columns and set the separator to Custom, use a hyphen as the separator, then enter a column name
5. Finally change the Data Type to Date.
(If this doesn't work, go back to step 2 and swap the order of the day and month columns and try again)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
97 | |
80 | |
67 | |
63 |
User | Count |
---|---|
147 | |
110 | |
108 | |
85 | |
64 |