Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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.

1 ACCEPTED SOLUTION
m_dekorte
Super User
Super User

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

View solution in original post

2 REPLIES 2
m_dekorte
Super User
Super User

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
Not applicable

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors