cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

How to populate a column with the most recent value above when values inbetween are all zero

Hello,

 

I have a date column which has been poorly filled out... the date value for that particular date is entered in the first cell then a number of activities are then recorded (a time activity log) in the following rows. the next date is then entered and all the values inbeteen are zero. the number of rows between each new date value varies.

 

I want to make it so that every row has the corresponding date. i.e pull down the most recent non-zero value from above. the dates are also increasing as we go down the table.

 

A solution in a power BI format would be great or even excel!

 

Thanks

 

Alex

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: How to populate a column with the most recent value above when values inbetween are all zero

Hi @AlexValerio ,

 

Before that, we should insert the index column in power query like that.

ind.PNG

 

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

9 REPLIES 9
Highlighted
Super User IX
Super User IX

Re: How to populate a column with the most recent value above when values inbetween are all zero

The information you have provided is not making the problem clear to me. Can you please explain with an example.

Can you share sample data and sample output.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
New Member

Re: How to populate a column with the most recent value above when values inbetween are all zero

Date (current format)New Date (What i want it to be like)
01/05/202001/05/2020
001/05/2020
001/05/2020
02/05/202002/05/2020
002/05/2020
002/05/2020
002/05/2020
03/05/202003/05/2020
003/05/2020
04/05/202004/05/2020

 

The left column is my current format and i want it to look like the righ column where the date is pulled down unti;l the next date is in a cell.

Highlighted
Super User IX
Super User IX

Re: How to populate a column with the most recent value above when values inbetween are all zero

This option work in Edit Query or data transformation mode. make sure you use replace value to replace 0 with blank. That is also there on right-click

https://www.excelcampus.com/library/fill-down-blank-null-cells-power-query/

 

Again a dax code. here you can 0 in place of blank

https://community.powerbi.com/t5/Desktop/Fill-blanks-with-previous-value/td-p/492501

 

Appreciate your Kudos.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Super User IX
Super User IX

Re: How to populate a column with the most recent value above when values inbetween are all zero

@AlexValerio , do above reply helped you. If note please let us known the issue and mark me @



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Community Support
Community Support

Re: How to populate a column with the most recent value above when values inbetween are all zero

Hi @AlexValerio ,

 

1.Replace 0 to null in power query.

f2.PNG

2.Fill down.

fill1.PNG

 

M code for you reference.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUNzDVNzIwMlDSQebE6gDliBMyQjbBCFM5WULGyIYaYypHEzJBVo7gxMYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Date (current format)" = _t, #"New Date (What i want it to be like)" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date (current format)", type text}, {"New Date (What i want it to be like)", type date}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","0",null,Replacer.ReplaceValue,{"Date (current format)"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"Date (current format)"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Filled Down",{{"Date (current format)", type date}})
in
    #"Changed Type1"

 

 

Alternatively, we can achieve that by dax. Before that, insert an index in power query and c reate a calculated column as below.

Column =
VAR i = 'Table (3)'[Index]
VAR minindex =
    CALCULATE (
        MAX ( 'Table (3)'[Index] ),
        FILTER (
            'Table (3)',
            'Table (3)'[Index] < i
                && 'Table (3)'[Date (current format)] <> BLANK ()
        )
    )
RETURN
    IF (
        'Table (3)'[Date (current format)] = BLANK (),
        CALCULATE (
            MAX ( 'Table (3)'[Date (current format)] ),
            FILTER ( 'Table (3)', 'Table (3)'[Index] = minindex )
        ),
        'Table (3)'[Date (current format)]
    )

22.PNG

 

Pbix as attached.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Highlighted
New Member

Re: How to populate a column with the most recent value above when values inbetween are all zero

Hey Frank,

@v-frfei-msft 

 

I want to keep zeros as zeros as they also have value in my data.

 

I tried your second option howwver i get a "Token Eof expected" error on the i on the second line of the code (in red)

VAR i = 'Table (3)'[Index]

Do you know why this is?

Highlighted
Community Support
Community Support

Re: How to populate a column with the most recent value above when values inbetween are all zero

Hi @AlexValerio ,

 

Before that, we should insert the index column in power query like that.

ind.PNG

 

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

Highlighted
New Member

Re: How to populate a column with the most recent value above when values inbetween are all zero

 

Hey Frank, i already had that column added, see screenshot below.. out of curiosity, what does that i mean?  should i be re-typing to code  or is copy and paste fine?

 

error message.PNG

Highlighted
Community Support
Community Support

Re: How to populate a column with the most recent value above when values inbetween are all zero

Hi @AlexValerio ,

 

We should create calculated column in data view, Not custom column in power query. Please close and aplly and create the calculated column as the picture below.

 

2.PNG

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors