Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jdata
Frequent Visitor

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

1 ACCEPTED SOLUTION
Vvelarde
Community Champion
Community Champion

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




Lima - Peru

View solution in original post

15 REPLIES 15
Vvelarde
Community Champion
Community Champion

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




Lima - Peru

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

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!!!

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!

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? 

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.

 

Capture.JPG

Vvelarde
Community Champion
Community Champion

@jdata

 

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




Lima - Peru

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

 

Thank you!!

 

Jonathan 

Phil_Seamark
Employee
Employee

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 :

 

filldown.png

 

AFTER :

 

filldown2.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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"

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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"
Specializing in Power Query Formula Language (M)

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


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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

Specializing in Power Query Formula Language (M)

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


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.