Forum Discussion

cah2035's avatar
cah2035
Frequent Visitor
3 years ago
Solved

For a null value, check if the value above and below are equal and if so, fill in the null value

Hi all,   I have to fill in dates for a huge number of rows. Most of the dates I was able to match with another table, for a decent amount, I was not. My records are in a particular order (essentia...
Need Help
  • BA_Pete's avatar
    BA_Pete
    3 years ago

    H cah2035 ,

     

    You could try this. Not sure how performant it will be over a large dataset, but should do what you need:

    -1- Add index starting from zero (called [IndexFrom0]).

    -2- Add new custom column like this:

     

    if [Date] = null
    and PreviousStepName[Date]{[IndexFrom0] - 1} = PreviousStepName[Date]{[IndexFrom0] + 1}
    and PreviousStepName[Category]{[IndexFrom0] - 1} = PreviousStepName[Category]{[IndexFrom0] + 1}
then PreviousStepName[Date]{[IndexFrom0] - 1}
else [Date]

     

    Example output:

     

    Pete

BA_Pete's avatar
BA_Pete
Icon for Super User rankSuper User
3 years ago

H cah2035 ,

 

You could try this. Not sure how performant it will be over a large dataset, but should do what you need:

-1- Add index starting from zero (called [IndexFrom0]).

-2- Add new custom column like this:

 

if [Date] = null
    and PreviousStepName[Date]{[IndexFrom0] - 1} = PreviousStepName[Date]{[IndexFrom0] + 1}
    and PreviousStepName[Category]{[IndexFrom0] - 1} = PreviousStepName[Category]{[IndexFrom0] + 1}
then PreviousStepName[Date]{[IndexFrom0] - 1}
else [Date]

 

Example output:

 

Pete

  • cah2035's avatar
    cah2035
    Frequent Visitor
    3 years ago

    BA_Petethank you! This worked perfectly. I wasn't sure if there was a way to "offset" like you can in Excel but this acheives that.

     

    What exactly is the function of {[IndexFrom0] + 1}  in general?

    • BA_Pete's avatar
      BA_Pete
      Icon for Super User rankSuper User
      3 years ago

       

      In Power Query, row numbers are identified behind the scenes by a zero-base integer list so, for example, row 5 of your category column can be identified as PreviousStepName[Category]{4} etc. Creating the [Index0] column effectively materialises this integer list for us to be able to dynamically identify row numbers as we scan down the table. Therefore, when we use PreviousStepName[ColumnName]{[IndexFrom0] + 1}, we're actually saying "Give me the value from the query table I'm transforming (PreviousStepName) in the [ColumnName] column where the row number is one greater than the current row being scanned", so it allows us to zero in on a specific cell in the table and grab that value.

       

      Hope this makes sense.

       

      Pete

      • cah2035's avatar
        cah2035
        Frequent Visitor
        2 years ago

        Thank you, I didn't know you could do that so that will be very helpful in the future!