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

[cah2035]

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:

Index	Category	Item	Date
1	A	Alpha	10/20/2020
2	A	Omega
3	A	Delta	10/20/2020
4	B	Alpha	11/14/2022
5	B	Omega
6	C	Alpha	01/01/2020
7	C	Beta	01/01/2020
8	C	Omega
9	C	Gamma	10/14/2021
10	C	Beta	10/14/2021
11	D	Omega
12	D	Alpha	03/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!

[AlienSx]

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

[slorin]

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 

[BA_Pete · Accepted solution]

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]

@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]

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]

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

[BA_Pete]

No problem, happy to help.

Don't forget to give a thumbs-up on any posts that have helped you 👍

Pete