Return Date field from Text

[Anonymous]

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

[jdbuchanan71 · Accepted solution]

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'

[mahoneypat · Accepted solution]

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

[mahoneypat]

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

[Anonymous]

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

[Anonymous]

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?

[v-lionel-msft · Accepted solution]

Hi @Anonymous ,

1. Use Dax.

2. Use M query.

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.

[mahoneypat · Accepted solution]

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

[jdbuchanan71]

@Anonymous 

Try something like this.

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

[Anonymous]

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)

[jdbuchanan71 · Accepted solution]

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'

[Anonymous]

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.

[Anonymous]

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))

[pranit828]

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