update column value based on value of another column.

[Traceout]

Hello, 

I have a snapshot process to track customer traffic that runs every 15 minutes. On a rare occasion the process fails and either has a value of 'null' or a wrong number. I have a column that says whether the process is complete or error. Is there a way to use the prior completed value if there is an error. 

SNAPSHOT ID	SNAPSHOT TIME	SNAPSHOT STATUS	SNAPSHOT CUSTOMERS
1	17-JAN-24 01.15.00	COMPLETE	500
2	17-JAN-24 01.30.00	ERROR	NULL
3	17-JAN-24 01.45.00	ERROR	2 "NOT CORRECT VALUE"
4	17-JAN-24 02.00.00	COMPLETE	520

In the above table is there a way to create a new column or something that would put SNAPSHOT ID values 2 and 3 as 500 to match the most recent completed snapshot?

Like this: 

SNAPSHOT ID	SNAPSHOT TIME	SNAPSHOT STATUS	SNAPSHOT CUSTOMERS
1	17-JAN-24 01.15.00	COMPLETE	500
2	17-JAN-24 01.30.00	ERROR	500
3	17-JAN-24 01.45.00	ERROR	500
4	17-JAN-24 02.00.00	COMPLETE	520

The power bi report refreshes 8 times a day so pulls multiple rows at the same time. 

Thanks for any assistance you may be able to give. 

Example of my linechart with a bad 0 in it creating a bad user experience. 

[tackytechtom · Accepted solution]

Hi @Traceout ,

Here a possible solution in Power Query.

Before:

After:

Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZY7BCoAgEER/RfZcoavSOWQvYRqLdbH+/zda6qSdZhjeg6kVDAxg5nFd0ohOaTMZP2ktY8jbHqmQVC/DPVTAnrX6Y4k5s2Q6YnxJ25POtySqC1IuKmRmCkWdSzzogld2rYxi/i+hXLof", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"SNAPSHOT ID" = _t, #"SNAPSHOT TIME" = _t, #"SNAPSHOT STATUS" = _t, #"SNAPSHOT CUSTOMERS" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"SNAPSHOT ID", Int64.Type}, {"SNAPSHOT TIME", type text}, {"SNAPSHOT STATUS", type text}, {"SNAPSHOT CUSTOMERS", type text}}),
    #"Replace Values" = Table.ReplaceValue(#"Changed Type",each [SNAPSHOT CUSTOMERS],each if [SNAPSHOT STATUS] = "ERROR" then null else [SNAPSHOT CUSTOMERS],Replacer.ReplaceValue,{"SNAPSHOT CUSTOMERS"}),
    #"Filled Down" = Table.FillDown(#"Replace Values",{"SNAPSHOT CUSTOMERS"})

in
    #"Filled Down"

Let me know if this solves your query 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/