cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

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

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.

3 REPLIES 3
Highlighted
Solution Sage
Solution Sage

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

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"

 

09-07-_2020_00-44-34.jpg

 

Regards FrankAT

 

 

Highlighted
Helper III
Helper III

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

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?

Highlighted
Helper III
Helper III

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

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.   

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors