Forum Discussion
moltra
Helper IV
Helper IVPower 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 mi...
FrankAT
Community Champion
Community ChampionHi 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
moltraI 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?
- 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.