Forum Discussion

lornafnb's avatar
lornafnb
Helper I
4 years ago
Solved

Multiple date formats in column

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 su...
Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    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

Vijay_A_Verma's avatar
Vijay_A_Verma
Most Valuable Professional
4 years ago

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's avatar
lornafnb
Helper I
4 years ago

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

    • lornafnb's avatar
      lornafnb
      Helper I
      4 years ago

      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's avatar
        Vijay_A_Verma
        Most Valuable Professional
        4 years ago

        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