Forum Discussion
Cleaning bad time series data
Community SupportHi Anonymous ,
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 and post the link here. Do mask sensitive data before uploading.
Please read this post to get your answer quickly: How to Get Your Question Answered Quickly.
Best Regards,
Amy
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 sum (4 million in below), into clean data column.
| 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 |
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".
yeah it is already, that's just cut and paste formatting from excel.