Custom Column - Find character in text and rearrange text

[Anonymous]

Hi,

I have a text column that contains dates in two different formats e.g. 2022-06-28, 6/29/2022. When I try to format column as date, the values with "/" in the date returns an error "DateFormat.Error: We couldn't parse the input provided as a Date value"

I was thinking of creating a custom column to find where text contains "/" and rearrange text e.g. 6/29/2022 will change to 29-06-2022

Can this be done? Appreciate any help please @rohit_singh @edhans 

[rohit_singh]

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! 🙂

[Anonymous]

Thanks @rohit_singh , that worked. Is it possible to add to the query "if blank, return blank"?

At the moment, it's returning "Error" for blank cells

[edhans · Accepted solution]

By the way @Anonymous  - the locale setting I gave you doesn't mean that is where you are. It is for translating data that is not from where you are, and why using English US will work here.

[Anonymous]

@edhans that worked perfectly. Much easier solution. Sorry I misinterpreted your response. 
Many thanks again for your help. Also thank you to @rohit_singh for your alternative solution, appreciate it.

[edhans]

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.

[Anonymous]

@edhans unfortunately that didn't work for me - same error showing

[edhans]

You used english New Zealand @Anonymous - I specifically used en-US in the locale change. It is because NZ doesn't recoginize 6/29/2022 as a valid date. Only the US does that. Change the function above to have en-US at the end in your formula bar, or go back to my original reply and follow the directions  where I used "English (United States)"