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

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
v-frfei-msft
Community Support
Community Support

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.

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?

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.

 

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

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.
amitchandak
Super User
Super User

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.

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.

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.

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

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.