Filling Data Gaps, Conditionally

[jdata]

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 'gap' row matches that of the row from which the data is copied down? 

Thanks!

Table currently ooks like this:

A  1/1/2016  8      2

A  1/2/2016  10  

A  1/3/2016  7      3

A  1/4/2016  6

B  1/1/2016  5      5

B  1/2/2016  4      6

B  1/3/2016  8

B  1/4/2016  6

C  1/1/2016  5

C  1/2/2016  9      1

C  1/3/2016  4     

C  1/4/2016  6      2

I'd like it to Look like this:

A  1/1/2016  8      2

A  1/2/2016  10    2

A  1/3/2016  7      3

A  1/4/2016  6      3

B  1/1/2016  5      5

B  1/2/2016  4      6

B  1/3/2016  8      6

B  1/4/2016  6      6

C  1/1/2016  5      null

C  1/2/2016  9      1

C  1/3/2016  4      1

C  1/4/2016  6      2

[Vvelarde · Accepted solution]

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

[davidataylor]

@Vvelarde , 3 years later and your solution helped me today.  Thank you.

[shoeshirt]

You are awesome-I had a totally different dataset but was able to use this DAX expression to fill data

based on a start date.

Thank you, thank you, thank you!!!

[EF]

Hi @Vvelarde ,

I am trying to use this code. It is working to fill blanks with data from earlier rows of Column D (from your example) connected to the correct ID (column A), however it is not taking it from the most recent date (column B). All the blanks are being filled with the same value from one earlier date. I need each blank to be filled with the value from the row above (where ID is the same and Date is equal or earlier).

Example:

Column: A B C D

              A, 1/1/18,Q,9

              A,2/3/18,Q,8

              B,12/3/17,Q,5

              B,1/1/18,Q,2

              B,3/3/19,Q,4

              A,1/1/20,Q,null

              B,1/1/19,Q,null

Expected outcome for column D

              A, 1/1/18,Q,9

              A,2/3/18,Q,8

              B,12/3/17,Q,5

              B,1/1/18,Q,2

              B,3/3/19,Q,4

              A,1/1/20,Q,8

              B,1/1/19,Q,4

However I am getting random fill in such as:

              A, 1/1/18,Q,9

              A,2/3/18,Q,8

              B,12/3/17,Q,5

              B,1/1/18,Q,2

              B,3/3/19,Q,4

              A,1/1/20,Q,9

              B,1/1/19,Q,2

Any advice?

Thanks!

[Deepakshi]

Hi @Vvelarde : Thanks for posting the dax solution online. I was trying to use the same solution for the exact same problem, but my lastnonblank value doesn't change for the same value in column A. For example: In Column D my 2 doesn't change to 3 for A in Column A, it remains 2 even though there's a new value 3 for A. I didn't change anything in the query. I am applying the query to a another calculated column though which uses the same solution to fill up conditionally and that works fine. Any idea what could be going on? 

[jdata]

Thanks @Vvelarde. 

When I run this formula I am almost getting the solution: In the 5th row from the bottom, we should have a value of 6 in the New D column, but we are getting a null.  Why is this?   Thanks.

[Vvelarde]

@jdata

Can you check if the Last B don't have a space in the end, i think is not equal to other Bs

[jdata]

Yes there was a space.  Nice catch @Vvelarde!!

Thank you!!

Jonathan 

[Phil_Seamark]

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]

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"})
})

[Phil_Seamark]

Here is a slightly better version in Power Query that I did more for myself to work through something cool @MarcelBeug showed me yesterday

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY+7DcAgDAVXsagjgfmFlCFjIPZfIxI2PKC8M2e5NfMSsWXrHWeiQuN50y81Xg07ItCg9Jb5ABPV5IHqHk8ynGBmPIrZ/swFBegsf0cZaCYfSTJMOJaBr+66vf8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type","Column1",Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv),{"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6", "Column1.7", "Column1.8", "Column1.9", "Column1.10", "Column1.11"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}, {"Column1.3", type date}, {"Column1.4", type text}, {"Column1.5", Int64.Type}, {"Column1.6", type text}, {"Column1.7", type text}, {"Column1.8", type text}, {"Column1.9", type text}, {"Column1.10", type text}, {"Column1.11", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Column1.2", "Column1.4"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Column1.1", "Column1.3", "Column1.5", "Column1.7", "Column1.8", "Column1.9", "Column1.6", "Column1.10", "Column1.11"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Reordered Columns",{"Column1.7", "Column1.8", "Column1.9", "Column1.6", "Column1.10"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Column1.11", "Fillcol"}, {"Column1.1", "Column1"}}),
    StartingPoint = #"Renamed Columns",
    NewFillCol= List.Generate(
                        () => [Index=0,
                               Col1=StartingPoint [Column1]{0},
                               FillCol=StartingPoint[Fillcol]{0}] ,
                        each [Index] < Table.RowCount(StartingPoint),
                        each [Index=[Index] + 1,
                              Col1=StartingPoint [Column1]{Index} , 
                               FillCol=if Col1 = [Col1] then 
                                        if StartingPoint [Fillcol]{Index} = null then [FillCol] else StartingPoint [Fillcol]{Index}
                                       else StartingPoint [Fillcol]{Index}] ,
                        each [FillCol]
                        ),
    Records = Table.ToRecords(StartingPoint),
    CombinedTable = Table.FromColumns({Records,NewFillCol},{"Records","NewFillCol"}),
    #"Expanded Records" = Table.ExpandRecordColumn(CombinedTable, "Records", {"Column1", "Column1.3", "Column1.5"}, {"Records.Column1", "Records.Column1.3", "Records.Column1.5"})

in
    #"Expanded Records"

[MarcelBeug]

Thanks @Phil_Seamark

Otherwise this would be my solution in this case (in which your table creation is copied with pride):

let
    // These steps are to create the initial table
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY+7DcAgDAVXsagjgfmFlCFjIPZfIxI2PKC8M2e5NfMSsWXrHWeiQuN50y81Xg07ItCg9Jb5ABPV5IHqHk8ynGBmPIrZ/swFBegsf0cZaCYfSTJMOJaBr+66vf8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type","Column1",Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv),{"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6", "Column1.7", "Column1.8", "Column1.9", "Column1.10", "Column1.11"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}, {"Column1.3", type date}, {"Column1.4", type text}, {"Column1.5", Int64.Type}, {"Column1.6", type text}, {"Column1.7", type text}, {"Column1.8", type text}, {"Column1.9", type text}, {"Column1.10", type text}, {"Column1.11", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Column1.2", "Column1.4"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Column1.1", "Column1.3", "Column1.5", "Column1.7", "Column1.8", "Column1.9", "Column1.6", "Column1.10", "Column1.11"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Reordered Columns",{"Column1.7", "Column1.8", "Column1.9", "Column1.6", "Column1.10"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Column1.11", "Fillcol"}, {"Column1.1", "Column1"}}),

    // Now we have the initial table, we add 2 indices to join the table with itself,
    // such that the previous Column1 value will be on the same row as the current Column1 value
    #"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 0, 1),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1",{"Index"},#"Added Index1",{"Index.1"},"Previous",JoinKind.LeftOuter),
    #"Expanded Previous" = Table.ExpandTableColumn(#"Merged Queries", "Previous", {"Column1"}, {"Previous.Column1"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Previous",{{"Index", Order.Ascending}}),

    // If first row for Column1 value is null, then replace with "" to block the filldown
    #"Added Custom" = Table.AddColumn(#"Sorted Rows", "NewFillcol", each if [Fillcol] = null and [Column1] <> [Previous.Column1] then "" else [Fillcol], Int64.Type),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"NewFillcol"}),

    // Now turn the "" back to null
    #"Replaced Value" = Table.ReplaceValue(#"Filled Down","",null,Replacer.ReplaceValue,{"NewFillcol"}),

    // Finishing touches
    #"Removed Columns2" = Table.RemoveColumns(#"Replaced Value",{"Fillcol", "Index", "Index.1", "Previous.Column1"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns2",{{"NewFillcol", "Fillcol"}})
in
    #"Renamed Columns1"

[Phil_Seamark]

I like the offset index approach and the join.  I will use that for sure.  Much easier than what I butchered up.

[MarcelBeug]

Indeed.

I use it regularly in my solutions and if you've done it a few times, you have it arranged within 10 seconds with just a few clicks.

For 1 particular case I created a video some time ago, in which this is clearly illustrated during the first minute (after the first minute the video becomes too specific for that particular case).

[Phil_Seamark]

Thanks @MarcelBeug ,  I was hoping you would reply so I could see how to do it properly.  I will study this now 🙂