Forum Discussion

Gilson_Souza's avatar
Gilson_Souza
Frequent Visitor
4 years ago

Fill blank values based in the next/last value

Hi, everyone! I need some help, please.

I need to fill the blank values with the next (or last) License Plate value:

 

This is what I have:

 

DateLicense PlateValue
01/07/2022ABC123A22
02/07/2022ABC123B22
03/07/2022ABC123L22
04/07/2022ABC123 
05/07/2022ABC123B22
06/07/2022ABC123 
07/07/2022ABC123 
08/07/2022ABC123L22
09/07/2022ABC123 
10/07/2022ABC123C22
01/07/2022CPC322 
02/07/2022CPC322K99
03/07/2022CPC322 
04/07/2022CPC322D99
05/07/2022CPC322 
06/07/2022CPC322 
07/07/2022CPC322G99
08/07/2022CPC322 
09/07/2022CPC322D99
10/07/2022CPC322D99

 

This is what I need:

 

DateLicense PlateValue
01/07/2022ABC123A22
02/07/2022ABC123B22
03/07/2022ABC123L22
04/07/2022ABC123L22
05/07/2022ABC123B22
06/07/2022ABC123B22
07/07/2022ABC123B22
08/07/2022ABC123L22
09/07/2022ABC123L22
10/07/2022ABC123C22
01/07/2022CPC322K99
02/07/2022CPC322K99
03/07/2022CPC322K99
04/07/2022CPC322D99
05/07/2022CPC322D99
06/07/2022CPC322D99
07/07/2022CPC322G99
08/07/2022CPC322G99
09/07/2022CPC322D99
10/07/2022CPC322D99

 

I could use Power Query's fill option, but some values ​​would be filled incorrectly.

 

Thank you very much.

Need Help

4 Replies

  • marcelsmaglhaes's avatar
    marcelsmaglhaes
    Icon for Super User rankSuper User
    4 years ago

    Hey Gilson_Souza ,

    I think you should do this using fill down. But at first, you need to transform the blank values into null values, and after that, you can use the fill down option. 

     

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

     

    Regards,

    Marcel Magalhães

     

     

     

    • Gilson_Souza's avatar
      Gilson_Souza
      Frequent Visitor
      4 years ago

      Hi, Marcel. Thanks for your help and sorry the delay, I was sick this week.

       

      I can't use fill down cause the first value of a particular car could be empty like this:



      10/07/2022ABC123C22
      01/07/2022CPC322 
      02/07/2022CPC322K99

       

      CPC322 can't have X22 values, only X99, so the filled value would be K99, not C22.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    I do not see any logic in your solution.  If you paste this data in an MS Excel file, then why should there not be K99 in cell C14 (why are yuo showing D99 there).  This is just one that i noticed - there could be more as well.  Please review all and clarify.

    • Gilson_Souza's avatar
      Gilson_Souza
      Frequent Visitor
      4 years ago

      If the first value of a car is empty I need to get the value below instead of the value above

      That's what happens to C13 cell: