Forum Discussion

jdata's avatar
jdata
Frequent Visitor
9 years ago
Solved

Filling Data Gaps, Conditionally

  Hi, I have a table that looks like the one below.  One of the data columns has some blanks that I would like to fill.  How can I fill downward, but only when the identifier (first column) of the '...
Tips and Tricks
  • Vvelarde's avatar
    Vvelarde
    9 years ago

    jdata

     

    A Dax solution .

     

    Would be a new calculated column:

     

    NewD =
IF (
    Table1[D] = BLANK (),
    CALCULATE (
        LASTNONBLANK ( Table1[D], Table1[D] ),
        FILTER ( ALLEXCEPT ( Table1, Table1[A ] ), Table1[B] <= EARLIER ( Table1[B] ) )
    ),
    Table1[D]
)

     

    The Columns Are A,B,C,D in the order of your sample data.. 

Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
9 years ago

Hi jdata

 

Have you tried the FILL DOWN feature in the Query Editor?

 

Just select the column you'd like filled and use this function.

 

BEFORE :

 

 

AFTER :

 

Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
9 years ago

I realised I missed the criteria about using the 1st column resetting the fill down

 

Here is a first stab at a really ugly way of doing it.  I'm sure it can be made dynamic

 

= Table.Combine(
{Table.FillDown(Table.SelectRows(#"Renamed Columns", each [Column1] = "A"),{"Column1", "Fillcol"}),
Table.FillDown(Table.SelectRows(#"Renamed Columns", each [Column1] = "B"),{"Column1", "Fillcol"}),
Table.FillDown(Table.SelectRows(#"Renamed Columns", each [Column1] = "C"),{"Column1", "Fillcol"})
})