Old data vs new data comparison

[jereaallikko]

Hi all,

I have data coming from direct query with M -language code from a company internal database. The data contains information about product testing, same products are tested continuously again and again. The idea is to follow number of total tests and their progress/statuses/results. I would like to create historical comparisons and progress bars of number of test cases over time in charts. (e.g. which products are tested most). The problem is, that the database overwrites the old data & timestamps, and does not create new rows of data (old data is replaced).

An example data in the database 24.5.2021 :

Product	Created at	Last Test	Last Passed	Last Updated	Status
Mobile	5.5.2021 13:20:33	23.5.2021 10:55:12	23.5.2021 10:55:12	23.5.2021 10:55:48	Passed
Laptop	13.5.2021 10:12:45	20.5.2021 14:09:58	20.5.2021 14:09:58	20.5.2021 14:10:37	Passed
Camera	19.5.2021 10:14:28	23.5.2021 10:56:54	23.5.2021 10:56:54	23.5.2021 10:56:54	Passed
TV	19.5.2021 10:16:02	23.5.2021 10:57:08	23.5.2021 10:57:08	23.5.2021 10:57:08	Passed

• TOTAL RUN CASES 4
  • 1 Mobile
  • 1 Laptop
  • 1 Camera
  • 1 TV

Data in the database 25.5.2021

Product	Created at	Last Test	Last Passed	Last Updated	Status
Mobile	5.5.2021 13:20:33	23.5.2021 10:55:12	23.5.2021 10:55:12	23.5.2021 10:55:48	Passed
Laptop	13.5.2021 10:12:45	25.5.2021 09:43:50	25.5.2021 09:43:50	25.5.2021 09:43:51	Passed
Camera	19.5.2021 10:14:28	24.5.2021 18:12:08	24.5.2021 18:12:08	24.5.2021 18:12:08	Passed
TV	19.5.2021 10:16:02	24.5.2021 18:12:55	24.5.2021 18:12:55	24.5.2021 18:12:55	Passed

• TOTAL RUN CASES 7 (4 from yesterday, 3 cases re-run, marked in red)
• 1 Mobile
• 2 Laptop (1 on yesterday's data + 1 today)
• 2 Camera (1 on yesterday's data + 1 today)
• 2 TV (1 on yesterday's data + 1 today)

A bit difficult to explain, but hopefully someone understands the issue. Please don't hesistate to ask, if there is more information needed. 

Best regards,

Jere

[v-xiaotang]

Hi @jereaallikko 

Where do old data and timestamps get overwritten? In your company database?  or in Power Bi? If the former, it would be better to export data from your company database everyday supposing company database can't stop overwriting, then get exported data through Power BI. If the latter, maybe there is something wrong with your M-code, i.e it only gets today's data from company database. 

Best Regards,

Community Support Team _ Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[jereaallikko]

Hi @v-xiaotang 

It is overwritten in the company database. There are parameters for each field, which are overwritten everytime product is re-tested. 

[selimovd · Accepted solution]

Hey @jereaallikko ,

you could do that with incremental refresh. So daily you could just load the latest data and grow your dataset like that. Make sure to add a column with the load date, to be able to handle the snapshots.

The problem is if a load fails or you have to do changes that will reset the dataset you don't have any chance to reload the whole data. For that reason I would try to handle that in the source.

For example, create a stored procedure that is executed daily to copy the data from the source to another table. Like that you can manage the snapshots in the new table. The advantage is, that if you have to reload your data, you have the whole history in the SQL server. Also if you want to use the data in a different way in the future.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[jereaallikko]

Hi @selimovd 

I managed to overcome the issue with another internal company database, where all the test results are saved. But this solution could be used if there were no other database.

Thanks for the answer.

Jere

[selimovd]

Hey @jereaallikko ,

thanks for the reply. If you have a database that has all the data that's the best solution. I'm happy you can use that table 😊

If you think my approach would be an alternative approach I would ask you to mark it as solution. This helps the next person to find a viable solution for a similar problem.

Thank you and best regards

Denis