Forum Discussion
Return Date field from Text
Hello Experts,
Date value needs to arrive Date value from this text field - 06/25/2020 01:28 PM America/Indianpolis
The value existing in Column is called Completion date
I have written the formula as = LEFT('Curriculum Item Status Detail ('[Completion Date],(FIND(" ",'Curriculum Item Status Detail ('[Completion Date],1)-1))
I am still getting Error
MID is a function that pulls part of a text string. (7,4) tells the engine to start at the 7th character and get 4 characters which returns '2020'
Yes. That's the great thing about Power BI. Your data transformation steps and DAX expressions (columns) are saved and re-applied at each refresh.
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
Hi Anonymous ,
1. Use Dax.
2. Use M query.
Best regards,
Lionel ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
12 Replies
- jdbuchanan71
Super User
Anonymous
Try something like this.
Date = DATE ( MID ( 'Table'[Completion Date],7,4 ) , LEFT ( 'Table'[Completion Date],2), MID ( 'Table'[Completion Date],4,2 ) )Hi Anonymous
DATE(<year>, <month>, <day>)
Year - (7,2) MID will extract 4 characters from 7th position
month - (2) Left will extract 2 characters from left position
Day - (4,2) MID will extract 2 characters from 4th position
For more indepth detail refer - https://docs.microsoft.com/en-us/dax/date-function-dax
- mahoneypat
Microsoft Employee
This is much easier to do in the query editor. Just highlight this column and choose Extract on the Transform tab, then Text Before Delimiter. Enter a space and hit ok. Then change to Date format.
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
The data will be dynamically replaced. Hence, after applying the your suggested steps. Subsequent attempt, when I upload new data. The steps, which we performed were getting wiped out.
Hence, I preferred to move the data to the REPORT tab and apply the logic I want to.
Please suggest if you have any better option to retain the settings, even after uploading new data every time or syntax to fix my requirement.
- mahoneypat
Not sure what you mean, All of the data will be reprocessed at each refresh (both the query and the calculated column suggestions). The steps remain in the query. Can you clarify?
Regards,
Pat