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.
Good morning...
I have a field which is text and has the following example: SOP 27/01/2019
I need to extract the date part and convert into a date field so I split the field into two: Field 1 = SOP , Field 2 = 27/01/2019
I need to convert the second field into a date field ending up with month/year: Jan 2019
thanks
Marc
Solved! Go to Solution.
Steps followed in Power Query
Go to the Power Query Editor window
Step 1: Home > Use First Row as Headers (in case your column header is not what it is supposed to be)
Step 2(optional): Right Click on the column > Duplicate Column (in case you want to keep the original column)
Step 3: Right Click on the column > Split Column > By Delimiter
Step 4: Select Space from drop-down > Ok
It will create two columns.
Step 5: Select the Date column > Transform > Extract > Last Characters > Enter 7
Step 6: Left Click on the 'ABC' icon in the column header > Select Date (It will transform it in the date column)
Step 7:Home> Close & Apply
Regards,
Vivek
If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂
https://www.vivran.in/
Good morning @MarcUrdang
I have used the Power Query to get the desired results:
You may find the pbix file here
Regards,
Vivek
If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂
https://www.vivran.in/
Hi
No it doesn't seem to work .. I formated your changed column to read as MMMYY and then created a new column showing the month/year of today ie Format(Today(),"MMMYY") which shows as Feb20 and then tried to compae the MMMYY from your new created field with this one and I get an error?
Also I am not sure of the steps to use Query to get the field you created?
Steps followed in Power Query
Go to the Power Query Editor window
Step 1: Home > Use First Row as Headers (in case your column header is not what it is supposed to be)
Step 2(optional): Right Click on the column > Duplicate Column (in case you want to keep the original column)
Step 3: Right Click on the column > Split Column > By Delimiter
Step 4: Select Space from drop-down > Ok
It will create two columns.
Step 5: Select the Date column > Transform > Extract > Last Characters > Enter 7
Step 6: Left Click on the 'ABC' icon in the column header > Select Date (It will transform it in the date column)
Step 7:Home> Close & Apply
Regards,
Vivek
If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂
https://www.vivran.in/
You can use Power Query or can try a new column using dax like
New date =date(year(right(table[sopdate],4)),month(mid(table[sopdate],8,2)),day(mid(table[sopdate],5,2)))
In case it gives some issue check what you get from each one of right and mid and correct the position
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges
Connect on Linkedin
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 |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |