Power Query - replacing text in a new column

0
votes

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.

2
excelpowerquery

2 Answers

1
votes

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)
1
votes

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"