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 (essentially an index column) based on storage locations, and in that order, the records are mostly grouped by date (as in, all the records from a particular date are one after another). The null dates can be in the middle of these groups or at the end of the group. Here is an example:

 

 

IndexCategoryItemDate

1

AAlpha10/20/2020
2AOmega 
3ADelta10/20/2020
4BAlpha11/14/2022
5BOmega 
6CAlpha01/01/2020
7CBeta01/01/2020
8COmega 
9CGamma10/14/2021
10CBeta10/14/2021
11DOmega 
12DAlpha03/10/2014

 

"Omega" records are the ones I cannot match with a date and need to assume based on the other values. I need something that would do the following:

  • For Row 2, the date should be filled in as 10/20/2020 because Row 1 & Row 3 are both 10/20/2020 for the same category
  • For Row 5, the date should remain null because Row 4 & Row 6 are not the same category
  • For Row 8, the date should remain null becuase Row 7 & Row 9 are not the same date (though they are the same category)
  • For Row 11, the date should remain null because Row 10 & Row 12 are neither the same category nor the same date

To summarize, the only time I can assume is when the row above and below the null value are both the same category and the same date.

Any advice would be much appreciated!

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

7 Replies

  • 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
        2 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

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

    Hi,

    Another solution with FillUp  and FillDown

     

    let
    Source = Your_Source,
    #"Added Custom" = Table.AddColumn(Source, "Custom", 
            each if [Item]<>"Omega" then [Category]&"-"&Text.From([Date]) else null),
    #"Duplicated Column" = Table.DuplicateColumn(#"Added Custom", "Custom", "Custom2"),
    #"Filled Down" = Table.FillDown(#"Duplicated Column",{"Custom"}),
    #"Filled Up" = Table.FillUp(#"Filled Down",{"Custom2"}),
    #"Added Custom1" = Table.AddColumn(#"Filled Up", "Date2", 
            each if [Custom]=[Custom2] and [Date]=null then Date.From(Text.End([Custom],10)) else [Date], type date)
    in
    #"Added Custom1"

    Stéphane 

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

    Hi, cah2035 the one with List.Generate

    let
    Source = your_table,
    count = Table.RowCount(Source),
    rows = List.Buffer(Table.ToRecords(Source)),
    g = List.Generate(
        () => [i = 0, r = rows{0}],
        (x) => x[i] < count,
        (x) => 
            [i = x[i] + 1,
            r = 
                if not List.Contains({"", " ", null}, rows{i}[Date]) then rows{i} else 
                if List.AllTrue({x[r][Category] = rows{i + 1}[Category]?, x[r][Date] = rows{i + 1}[Date]?}) 
                    then Record.TransformFields(rows{i}, {"Date", each x[r][Date]}) else rows{i} ],
        (x) => x[r]
    ),
    z = Table.FromRecords(g)
in
    z