Forum Discussion
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 '...
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..
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"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"I like the offset index approach and the join. I will use that for sure. Much easier than what I butchered up.
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).
Thanks MarcelBeug , I was hoping you would reply so I could see how to do it properly. I will study this now :)
