1

I am new to Power Query in Excel and my question is:

I have a text column with date in this format "09-Feb-17 A". To remove the text " A" and populate the date info in a new column (custom column?), I have used this code:

= Table.AddColumn(#"Changed Type", "Start Date", each Replacer.ReplaceText([Start], " A",""))

Problem is some of the dates are in correct format i.e. without " A". For those dates I get an error:

Expression.Error: We cannot convert the value #date(2019, 1, 11) to type Text. Details: Value=11/01/2019 Type=Type

Is there any way to solve this issue within power query?

Thanks in advance.

B.B
  • 35
  • 7

2 Answers2

2

Perhaps

  • Change the column Data type to Text (dates --> date time)
  • Extract the portion of the string prior to the space
  • Optional Change column Data type to Date
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Dates", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.BeforeDelimiter([Dates]," ")),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type date}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Dates"})
in
    #"Removed Columns"
Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
  • Thanks Ron but couldn't get it to work - obviously due to my limited knowledge. – B.B Jan 08 '19 at 13:17
2

You can use try otherwise to deal with both data types:

= Table.AddColumn(#"Changed Type", "Start Date", each try Date.FromText(Replacer.ReplaceText([Start], " A","")) otherwise DateTime.Date([Start]), type date)

Or this, which will extract the date before the first space, irrespective of which (or how many) characters follow:

= Table.AddColumn(#"Changed Type", "Start Date", each try Date.FromText(Text.BeforeDelimiter([Start], " ")) otherwise DateTime.Date([Start]), type date)
Olly
  • 7,749
  • 1
  • 19
  • 38