Trying to update an SQL table from within Power BI

Question

I am attempting to update an SQL table from within Power BI. I have many automated reports around the company where I work, and my goal is for them to report back when they are viewed so I know if one isn't updating properly. I am able to do this with my custom applications, but not yet for power bi.

I wrote a simple script and was able to run it in the query editor and it returned no errors and successfully updated the table in question:

update [ReportStatus] set
    [ViewedUTC] = GetUTCDate()
output inserted.[ViewedUTC] [Output] 
where [Name] = 'ServerMonitor'

When I click "Close & Apply" I receive the error message that I cannot use a nested update statement (though this is not nested) in a select statement (this is not a select statement) where it is not the immediate source of rows for the insert statement (this is not an insert statement and it is the immediate source of rows.

I've googled this extensively and while others have the same error message, I was unable to find one that was attempting to update and did so successfully. Any help would be greatly appreciated.

Thanks! Lexen

Answer 1

See here for a response from Microsoft people.

Can You Use Power Query In Power BI Or Excel To Write Data To A Data Source?

The short answer: no.

The long answer:

It is, technically, possible to use Power Query (in either Power BI or Excel) to write data to a data source; for example, here’s an old post I wrote showing how to run a UPDATE statement to update data in a table in SQL Server. However because the Power Query engine may evaluate a value or statement more than once when a query is executed, it is therefore not safe or supported to use it to try to create, insert, update, delete or otherwise change data in a data source – it should only be used to read data. You may see some functions listed in the M function reference that suggest otherwise but they aren’t supported either, and indeed may not work at all.

Answer 2

It is a nested update, because Power BI will compose query like this:

select * from (update [ReportStatus] set [ViewedUTC] = GetUTCDate() ...) SourceQuery

It does that, because it needs to add joins and conditions at runtime. Obviously the above query is not valid.

To get information about report usage, look at report's metrics (go to the workspace and click the dots icon next to the report or dashboard):

For more information read Monitor usage metrics for Power BI dashboards and reports article. Also take a look at Track user activities in Power BI.

Generally speaking, updating database contents like this is not a good idea. In the best case, reporting is a read only operation (e.g. you could offload it to a secondary read-only replica). Also it will not work for imported and live connections.

Answer 3

PBI is a business analytics tools as such it does not allow you to add values to your database, you can only extract and analyze your information in different ways, it's a Business Inteligence progam as such not for inputs but for data analytics.