changing date format from text to date

[Anonymous]

Hello,

I am creating a dashboard to be able to show controls that are expiring withing a certain period of time. This is my code:

For this code to work with my data, the 'ITRMP Controls'[Expiration Date] has to be in a date format. Whenever I add this code to my data to try and sort my data this is the error I get:

Whenever I try to change the data type or format from text to date type I get an error saying "We can't automatically convert the column to date type"

Also, the expiration date in the excel sheet is in DMY (Day/ Month/ Year) format

I am fairly new to PowerBI, so please explain as simple and thoroughly as possible.

Thank you

[v-kkf-msft · Accepted solution]

Hi @Anonymous ,

You need to add a custom column to convert the date to dd/mm/yyyy format in Power Query Editor. Then convert the Custom column to date type.

= let mylist = Splitter.SplitTextByDelimiter("/")([Date])
in Text.Combine({mylist{1}, mylist{0},mylist{2}},"/")

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you it worked!

[HotChilli]

You have to find the correct way of getting the Expiration Date into a date type.  Power Query has lots of different functions to handle this.

Please provide some sample data to see what we're dealing with.