Power BI data Cleaning verify data point value has increased over last data point.

[moltra]

I have multiple data points that are totalized values, at times there is times a data is missed.  I am trying to find a way to verify that each data point is greater than the one prior and then if missing data point is found, the data point is corrected by taking the next good reading and the prior good reading and then dividing the value by the number of missed points to get the change each data point and adding this to the prior good point to fill in the missing data.  There is 3 data fields that would have to be used in the data point classification.  Date, name and value.  There are multiple Names in the name column. 

I have been looking at R and the Library MICE to do this, but cannot figure it out. 

Date Time		Name		Value
6/24/2020 07:00:00		Datapoint1		1000
6/24/2020 08:00:00		Datapoint1		1010
6/24/2020 09:00:00		Datapoint1		1025
6/24/2020 10:00:00		Datapoint1		0
6/24/2020 11:00:00		Datapoint1		1045
6/24/2020 12:00:00		Datapoint1		1057
6/24/2020 13:00:00		Datapoint1		1069

I m trying to figure out a way for Power BI to fix the missing / incorrect datapoint at time 6/24/2020 10:00:00.  What I would like to happen would be that it takes the change and divides it by the period to get the time change per period and then it adds it to the last good reading.

1045-1025 = 20

20/2 = 10

1025 + 10 = 1035

the missing datapoint is 1035 and I want to place that in the table in the value point for the affected Date time.

I initially put this help request in service not desktop.  Not sure how to move the origianal help request or how to delete it.

[moltra]

Date Time		Name		Value
6/24/2020 07:00:00		Datapoint1		1000
6/24/2020 08:00:00		Datapoint1		1010
6/24/2020 09:00:00		Datapoint1		1025
6/24/2020 10:00:00		Datapoint1		0
6/24/2020 11:00:00		Datapoint1		1045
6/24/2020 12:00:00		Datapoint1		1057
6/24/2020 13:00:00		Datapoint1		1069

I m trying to figure out a way for Power BI to fix the missing / incorrect datapoint at time 6/24/2020 10:00:00.  What I would like to happen would be that it takes the change and divides it by the period to get the time change per period and then it adds it to the last good reading.

1045-1025 = 20

20/2 = 10

1025 + 10 = 1035

the missing datapoint is 1035 and I want to place that in the table in the value point for the affected Date time.

I initially put this help request in service not desktop.  Not sure how to move the origianal help request or how to delete it.

[FrankAT]

Hi @moltra,

here is the M-code solution with your sample data:

// Table
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckksSVUIycxNVdJR8ksEU2GJOaWpSrE60Upm+kYm+kYGRgYKBuZWBgZABJQG6kgsyM/MKzEEcgwNgGJoSi1wKTXEUGqJS6mRKZpSQwPsStGNNDTEZaQJhpFGuJSamqMrNcal1MxSKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column3 = _t, Column5 = _t]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date Time", type text}, {"Name", type text}, {"Value", Int64.Type}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type1", "Index", 0, 1),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if [Value] = 0 then (#"Added Index"[Value]{[Index]-1} + #"Added Index"[Value]{[Index]+1}) / 2 else [Value]),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", Int64.Type}})
in
#"Changed Type2"

Regards FrankAT

[moltra]

my actual data has an object column that has over 50 different objects in it.  How would I incorperate this into your M-code?    Each object is unique.  I think that one way to do this would be to have something like a "for loop"  or what is compariable with M-code filtering the table by each unique object.   

[moltra]

I will have to test this, it might be over a week, on vacation starting Friday.

I do not understand where you are getting the Json from?