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 ...
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

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.