Forum Discussion

HamidBee's avatar
HamidBee
Icon for Power Participant rankPower Participant
4 years ago
Solved

Cleaning Data, Fill Down

I'd kindly like to ask how to remove the dates from the this column so that they are blank and then replace the blank cells with the region by filling down. So basically the dates should be replaced with the area names above.

 

 

Thanks in advance.

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi HamidBee ,

     

    Please try the following formula to directly tranform the column instead of adding a new custom column:

      #"Transformed Column"= Table.ReplaceValue(Table.TransformColumns(#"Changed Type",{"Column1",each Text.Remove(_,{"0".."9"})}) ,"//",null,Replacer.ReplaceValue,{"Column1"} ),

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    How I would do it

    1) make an extra helping column, depicting the rows with dates:

    Add conditional column (Helpcolumn) > if column1 contains "/" Then null Else OK

    2) add an extra column with the correct contents

    Add Conditional column (Cleaned): if Helpcolumn equals OK Then Column1 Else Helpcolumn

     With the last statement you just grabbed the normal information from your first initial column (Aberdeen etc) and gave null (blank values) when it's a date.

    Now, if you select the output column (Cleaned) and then hit Fill > Down, it should do exactly what you requested.

     

    to

     

    Cheers

    • HamidBee's avatar
      HamidBee
      Icon for Power Participant rankPower Participant
      4 years ago

      This is a good method and I think there may be a few ways to do it like this. I was however trying to find a way to do it without creating any columns although I'm not sure that it's possible. 

  • bcdobbs's avatar
    bcdobbs
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Have you tried the column from example in power query?

     

    Type the region names as they are next to a few regions and delete any attempt to replicate a date. If it looks like it works, inspect the M expression it writes for you.

    • HamidBee's avatar
      HamidBee
      Icon for Power Participant rankPower Participant
      4 years ago

      Could you please explain some of the steps I'm not quite sure what to do here.

       

      Thanks

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi HamidBee ,

     

    Please try the following formula to directly tranform the column instead of adding a new custom column:

      #"Transformed Column"= Table.ReplaceValue(Table.TransformColumns(#"Changed Type",{"Column1",each Text.Remove(_,{"0".."9"})}) ,"//",null,Replacer.ReplaceValue,{"Column1"} ),

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • HamidBee's avatar
      HamidBee
      Icon for Power Participant rankPower Participant
      4 years ago

      Thank you for your response. I'll give this ago and see how I Find it. My manager showed me a quick way to di it in Excel. Create a column numbered from one to however many rows you have. Sort the dates/string column by alphabetical order. The dates will all be grouped together, delete the dates and then sort again by the new ID column you created. It may be better in this instance to clean it in Excel just because the data set could be large. Having said that I really like your solution, I wish I could one day get to that level of coding.