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.