Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Filling Data Gaps, Conditionally

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Filling Data Gaps, Conditionally

03-24-2017
04:51 PM

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

Solved! Go to Solution.

1 ACCEPTED SOLUTION

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-24-2017
05:03 PM

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 15

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-24-2017
05:03 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-13-2020
01:07 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-02-2020
07:38 AM

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!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-13-2018
04:35 PM

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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-29-2017
10:00 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-29-2017
10:10 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-24-2017
04:58 PM

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 :

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-24-2017
05:14 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-24-2017
06:22 PM

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"

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-24-2017
06:55 PM

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)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-24-2017
07:05 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-24-2017
07:15 PM

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)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-24-2017
06:58 PM

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

Featured Topics

Top Solution Authors

User | Count |
---|---|

286 | |

92 | |

68 | |

62 | |

52 |

Top Kudoed Authors

User | Count |
---|---|

289 | |

102 | |

75 | |

64 | |

64 |