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

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