cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vtamilamudhan
Helper II
Helper II

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

 
3 ACCEPTED SOLUTIONS

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'

View solution in original post

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

Hi @vtamilamudhan ,

 

1. Use Dax.

v-lionel-msft_0-1597975574035.png

2. Use M query.

v-lionel-msft_1-1597975678090.pngv-lionel-msft_2-1597975693629.png

 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

12 REPLIES 12
mahoneypat
Super User
Super User

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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.

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


The data I feed in Query editor, will be replaced on a daily basis. I am importing fresh data frequently in the same input sheet.

In that case, will the suggested steps to change the format be retained?

Hi @vtamilamudhan ,

 

1. Use Dax.

v-lionel-msft_0-1597975574035.png

2. Use M query.

v-lionel-msft_1-1597975678090.pngv-lionel-msft_2-1597975693629.png

 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

jdbuchanan71
Super User
Super User

@vtamilamudhan 

Try something like this.

 

Date = DATE ( MID ( 'Table'[Completion Date],7,4 ) , LEFT ( 'Table'[Completion Date],2), MID ( 'Table'[Completion Date],4,2 ) )

jdbuchanan71_0-1597957367023.png

 

 

Thanks for your prompt response.

What is the logic behind giving the column index number and range lookup such as  (7,4) or (4,2)

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'

View solution in original post

I tried with your syntax too

 

Compl_Date_Custom = DATE(MID('Curriculum Item Status Detail ('[Completion Date],7,4),LEFT('Curriculum Item Status Detail ('[Completion Date],2),MID('Curriculum Item Status Detail ('[Completion Date],4,2))
 
Still its failing and NOT able to generate the data.

Hi,

 

Still I am still not able to retrieve the output.

 

I used my logic to return MM/DD/YYYY

 

= DATE(LEFT('Curriculum Item Status Detail ('[Completion Date],2),MID('Curriculum Item Status Detail ('[Completion Date],4,2),MID('Curriculum Item Status Detail ('[Completion Date],7,4))
 

Hi @vtamilamudhan 

 

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





PBI_SuperUser_Rank@1x.png


Hope it resolves your issue? 
Did I answer your question? Mark my post as a solution!

Appreciate your Kudos, Press the thumbs up button!!
Linkedin Profile

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors