Multiple date formats in column

[lornafnb]

Good day - my first post on the Forum! :)-

I'm reading from an Excel file, and the format of the date column is either 2021/11/08, or March '22 (in this case, it can default to 2022/11/01).

I suppose I could manually standardize/update it, as it is not a large file, but I would like to get to know it in code.

All help appreciated - please keep in mind I'm a beginner.

[watkinnc]

Have you tried selecting the column and clicking Parse Date under the Transform-Date option? Or a custom column using Date.FromText?

--Nate

[Vijay_A_Verma]

Since you are beginner, I will suggest that you insert a new custom column and put following formula where [Date] needs to be replaced with your column

= Date.From(if Text.Contains([Date],"'") then "1"&Text.Replace([Date],"'","") else [Date])

If you want to do in place column replacement, then insert following statement where Source needs to be replaced with your previous step.

= Table.ReplaceValue(Source,each [Date],each Date.From(if Text.Contains([Date],"'") then "1"&Text.Replace([Date],"'","") else [Date]),Replacer.ReplaceValue,{"Date"})

 See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtQ3NNQ3sFCK1YlW8k0sSs5QiCk1MDAyNzJSio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    Custom1 = Table.ReplaceValue(Source,each [Date],each Date.From(if Text.Contains([Date],"'") then "1"&Text.Replace([Date],"'","") else [Date]),Replacer.ReplaceValue,{"Date"})
in
    Custom1

[lornafnb]

Hello, thank you very much for your response.

1. I have tried the first option, but it gives me an error - would you mind if I send you the file here? I'm not sure how to attach it here.

2. How do I create a blank query (3rd point in your reply)?

So much appreciated. Thanks

[Vijay_A_Verma]

1. Make sure [Date] is replaced with your column name.

2. Ideal will be to upload the file without confidential/sensitive data to a cloud storage service such as Onedrive/Google Drive/Dropbox/Box (Onedrive preferred) and share the link here.

3. For Blank query - Right click on left side pane - New Query - Blank Query

[lornafnb]

1. My Field name is 'Live date' so my new column looks like this..

Date.From(if Text.Contains([Live date],"'") then "1"&Text.Replace([Live date],"'","") else[Live date])

2. The [Live date] is currently a text field.

3. The New field is also a text field - the "replace" part in the script above works...(although it is still Text format..

4. However the dates that were (correct) dates before, produce the error.

I suspect it might be a Data type error?

[Vijay_A_Verma]

Can you click on one of the errors and post the error here? To have the column in date format, use below step (Replace Source with your previous step)

= Table.AddColumn(Source, "Custom", each Date.From(if Text.Contains([Live date],"'") then "1"&Text.Replace([Live date],"'","") else[Live date]), type date)

 OR you can select the Custom column - Transform menu - Detect data type

[lornafnb]

I'm somewhat confused.

The order that I add the column is:

1) Transform

2) Add Column - Customer Column

3) I paste your line of code in there - get the below...(Then you mention I should replace Source with my previous step - I dont understand.)

4. If I expand the Custom column, I get this...

Then I select the Date Effective field only and I get an Error...

All the Steps I have taken up to here is 

I dont know where I am going wrong

Thanks

[Vijay_A_Verma]

Do following -

1. Paste your sample data as table here. Refer to this - How to provide sample data in the Power BI Forum - https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...

2. Go to advanced editor, copy the code, in this text box press </> above, paste the code

Press Reply. 

[lornafnb]

Ok let me try..here is sample data from my Excel file

22-Jun-21

08-Nov-21

08-Nov-21

08-Nov-21

08-Nov-21

08-Nov-21

March '22

March '22

March '22

March '22

Here is the exact code I am using 

Table.AddColumn(Source, "Date Effective", each Date.From(if Text.Contains([Live date],"'") then "1"&Text.Replace([Live date],"'","") else[Live date]), type date)

My column name in the Excel file is [Live date] .

Hope this helps

[Vijay_A_Verma]

See the code below where I have changed Text.Contains([Live date],"'") to Text.Contains(Text.From([Live date]),"'")

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Result = Table.AddColumn(Source, "Date Effective", each Date.From(if Text.Contains(Text.From([Live date]),"'") then "1"&Text.Replace([Live date],"'","") else [Live date]))
in
    Result

[lornafnb]

1. your first statement - 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

1. do I paste it into the Applied step Source? 

2. The next part - I assume that is the new calculated column - please explain the change?

Thankful

[Vijay_A_Verma · Accepted solution]

Leave everything, in a custom column put this. That's it. 

= Date.From(if Text.Contains(Text.From([Live date]),"'") then "1"&Text.Replace([Live date],"'","") else [Live date])

 If you want to convert this column to a date, select column - Transform tab - Detect data type

[lornafnb]

Stunning - it works. Please may you explain what made it work?

Appreciate your assistance.

[lornafnb]

I'm so sorry - I am really stupid with this.

1. The error message that the values cannot be converted to text - so your thinking that it needs to be converted to date is correct.

2. In your suggestion - it starts with Table.Addcolumn....why is that - I still have a query, not a table...

thanks

[lornafnb]

Unfortunately my organization prevents using any of these services