Delete date value from text column

[Anonymous]

Hello Power BI Community,

I have a question on how to remove a date value from a column. Due to the less than optimal layout of the data in the original file, I need to do some data manipulation to extract the data from an Excel file that is manually updated.

Because of how I need to manipulate the data (Fill up, use first row as headers, unpivot, etc.) I need to remove the value in the first row of the first column. This is a date value and is of the format dd-mm-yy. It is the only value in this column that has dashes and I would like a way to replace this value with a null value so I can use the fill up command to fill from the second row to the first row and use that as headers.

Thank you for any help you can provide.

[m_dekorte · Accepted solution]

Hi @Anonymous 

Give something like this a go.

let
  Source = Table.FromColumns(
    {
      {#date(2023, 12, 19), "", null, "HeaderRow1", "HeaderRow2"}, 
      {"", "", "Period", "P1", "P2"}
    }
  ), 
  ReplaceValue = Table.ReplaceValue(
    Source, 
    each [Column1], 
    each if [Column1] is date then null else [Column1], 
    Replacer.ReplaceValue, 
    {"Column1"}
  )
in
  ReplaceValue

I hope this is helpful

[Anonymous]

Awesome! That worked. 

As a back up solution, I changed the type of Column1 to Text and looked for the "-".

= Table.ReplaceValue( #"Changed Type1", each [Column1],
each if Text.Contains( [Column1], "-" ) then null 
else [Column1],
Replacer.ReplaceValue, {"Column1"}
)

Thank you very much for your help.