cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
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
15 REPLIES 15
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
Frequent Visitor

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

Frequent Visitor

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

Helper II

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

Thanks!

New Member

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?

Frequent Visitor

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.

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
Frequent Visitor

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

Thank you!!

Jonathan

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 :

AFTER :

Proud to be a Datanaut!

Employee

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

Proud to be a Datanaut!

Employee

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"```

Proud to be a Datanaut!

Community Champion

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
#"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),

// 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)
Employee

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

Proud to be a Datanaut!

Community Champion

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

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

Proud to be a Datanaut!

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors