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"})
})
  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    9 years ago

    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's avatar
      MarcelBeug
      Community Champion
      9 years ago

      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's avatar
        Phil_Seamark
        Microsoft Employee
        9 years ago

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