Custom Column - Find character in text and rearrange text

This should work as is but depends on your location. This (and note the data type is text to start with)

Select By Locale:

Then set to Date, and English (United States)

It becomes this:

Full code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtI1MNM1MleK1YlWMtM3stQHiSnFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Date", type date}}, "en-US")
in
    #"Changed Type with Locale"

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

Hi Anonymous ,

If you want to use a custom column, you can try this logic in power query. Copy and paste the below code in a blank query. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtI1MNM1MleK1UHiWoC5eaU5OTgYZvpGlvog5TiFYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if Text.Contains([Date], "/") then Text.AfterDelimiter([Date], "/", 1) &"-"&
Text.BeforeDelimiter([Date], "/") &"-"&
Text.BetweenDelimiters([Date], "/", "/") else [Date]),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type date}})
in
    #"Changed Type"

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂