Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Delete date value from text column

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.

Need Help
  • m_dekorte's avatar
    m_dekorte
    2 years ago

    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

2 Replies

  • m_dekorte's avatar
    m_dekorte
    Resident Rockstar
    2 years ago

    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's avatar
      Anonymous
      Not applicable
      2 years ago

      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.