Forum Discussion
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 '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
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..
15 Replies
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 :
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"}) })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"
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..
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?
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!
