I need to allow my model to detect if there have been any data deleted from SQL. I have incremental refresh set up on ‘Load date’ column in my dataset. However, after the 1st initial load in power bi service, I have deleted some of the data in SQL but the data is still visible in Power BI service.
I only have one date column in my dataset. In some cases we have incorrect sales data sent to us. The process behind the scenes is to remove the incorrect data from SQL and replace with the new date, however if the data has already loaded to Power BI service, I have no way to remove.
Does anyone have any advice on this topic, other than having to constantly republish the file when there is incorrect data?
For that i have suggestion which once i think to use but that report never goes live.
you Should have one primary key in your table or you need to make anything unique for that table and when delete happen that unique value also deleted .
Just import one more table only containing that unique key and in M query u can perform a check that you main table only contain primary key from your check table.
Hope i am clear if not let me know i try to explain you with example.
AFAIK, there’s no way to have Power BI aligned perfectly to your SQL source, but here’s a few thoughts:
If I was going to provide a purely Power BI solution, I’d max out the scheduled refreshes for your license type (8x per day Pro, 48x per day Premium) and ensure that the report consumers are aware that data should be up-to-date only as per the latest update (e.g., if a Pro license, then data could be 3 hours old [24/8]).
If I was going to provide a purely SQL solution, I’d construct the database to use logical deletions instead of physical deletions (this way there’s a “record” of each deletion), and configure the Power BI report to filter out records where [IsDeleted] = 1 and only show the records where [IsDeleted] = 0. Again, though, with max scheduled refresh in Power BI, the report consumers must be aware that data should be viewed as 3 hours old (Pro). Your SQL DBA should be able to describe how deletions are handled in your specific environment.
It might be possible with a streaming dataset to achieve what you want, but I don’t have experience with streaming datasets. I’ll leave it to others to comment.
Greg
I get what you are saying here. Regarding the SQL solution. If the incorrect data is loaded into Power BI service as 0 because at the time it is ‘correct’, but then the error is realised and it changes to 1, how can this be picked up in Power BI? If the data is already in there, that 0 wont change to 1? Unless I am wrong, the detect data changes only works on a date column. Am I wrong?
Hi @Nurry90. I think we’re getting deeper and deeper without getting clearer, so here’s another attempt.
For SQL to be able to return only “correct” records, it needs some sort of flag to use in the “WHERE”clause of the query (I had used an IsDeleted = 0 in my first response). So to permit SQL to return only “correct” records and thereby Power BI to display only “correct” records, the “incorrect” records need to be flagged somehow in the SQL database, and the act of “correcting” them must reset this flag.
As Power BI is the interface you’re rightly asking for a how-do-you-do-it in Power BI, but this looks like a pure SQL issue to me. Please follow-up with your SQL DBA on the particulars of how an “incorrect” record is flagged as “correct” in your database/environment.
Greg
Hi @Nurry90, due to inactivity, a response on this post has been tagged as “Solution”. If you have any concern related to this topic, please create a new thread.
