Cleaning bad time series data

Question

I am trying to clean time series data, which is impact by upstream processes whcih result in extreme errors. Intially a large outlier will flow into the series, and then this move is reversed the next day when upstream issues are resolved, resulting in an accurate two day move, but inaccurate daily changes. Any tips on cleaning it? It is easy to filter and replace large values in powerquery with a zero, but what is correct is the running total after two days, and zeroing out would result in an incorrect total.&nbsp;For example, it would be something like below... how can i filter for outliers of a certain magnitude (100x), but dynamically alter the data such that&nbsp; 7/15 would be 0 and 7/16 would be 4mm? It would be relatively easy to implement this logic in excel, not so much in powerbi/powerquery.&nbsp;7/1/2019&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 19,590.027/3/2019&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; 1,561.337/5/2019&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; (1,500,003.00)7/8/2019&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; (25,000.00)7/9/2019&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; 35,000.007/10/2019&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; (2,000.00)7/11/2019&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; 180,000.007/12/2019&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; 250,000.007/15/2019&nbsp;&nbsp; (520,000,000,000.00)7/16/2019&nbsp;&nbsp;&nbsp;&nbsp; 520,004,000,000.007/17/2019&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; 320,000.007/18/2019&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; 650,000.007/19/2019&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; (250,000.00)

v-xicai · Answer

Hi Anonymous&nbsp;,&nbsp;I am not sure what desired result would you want, could you please share your sample data and desired output screenshots for further analysis? You can also upload sample pbix to OneDrive&nbsp;and post&nbsp;the link here. Do mask sensitive data before uploading.&nbsp;Please read this post to get your answer quickly: How to Get Your Question Answered Quickly.&nbsp;Best Regards,Amy&nbsp;

anonymous · Answer

Hi Amy,I did come up with a solution, but it isn't very flexible, and only catches one blip in the data. Basically, I filtered for outliers, zeroing out bad rows. Then I created a cummulative sum of the outliers , placing the final&nbsp;sum (4 million in below), into clean data column.&nbsp;DateRaw DataClean Data7/1/201919590.0219590.027/3/20191561.331561.337/5/2019-1500003-15000037/8/2019-25000-250007/9/201935000350007/10/2019-2000-20007/11/20191800001800007/12/20192500002500007/15/2019-5.2E+1107/16/20195.20004E+1140000007/17/20193200003200007/18/20196500006500007/19/2019-250000-250000

anonymous · Answer

How about if you force the column in datatype "Fixed decimal number" and then do the aggregation?

Step: In the Edit queries, click on the field, go to modelling tab and under data type tab choose "fixed decimal number".

anonymous · Answer

yeah it is already, that's just cut and paste formatting from excel.

Forum Discussion

Cleaning bad time series data

4 Replies

Related content

how to append calculated values to current time series

DAX for comparing time series data

Digital Twin Builder (preview): Bounding storage growth for an incremental time-series

DAX measures Clean-up in data model?

Time series

7/1/2019	19,590.02
7/3/2019	1,561.33
7/5/2019	(1,500,003.00)
7/8/2019	(25,000.00)
7/9/2019	35,000.00
7/10/2019	(2,000.00)
7/11/2019	180,000.00
7/12/2019	250,000.00
7/15/2019	(520,000,000,000.00)
7/16/2019	520,004,000,000.00
7/17/2019	320,000.00
7/18/2019	650,000.00
7/19/2019	(250,000.00)

Date	Raw Data	Clean Data
7/1/2019	19590.02	19590.02
7/3/2019	1561.33	1561.33
7/5/2019	-1500003	-1500003
7/8/2019	-25000	-25000
7/9/2019	35000	35000
7/10/2019	-2000	-2000
7/11/2019	180000	180000
7/12/2019	250000	250000
7/15/2019	-5.2E+11	0
7/16/2019	5.20004E+11	4000000
7/17/2019	320000	320000
7/18/2019	650000	650000
7/19/2019	-250000	-250000