Excel · Power BI · Power Query

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.

Thanks to Matt Masson of the dev team for providing the official position on this.

9 thoughts on “Can You Use Power Query In Power BI Or Excel To Write Data To A Data Source?

  1. But it is a good news! in many organizations datasets come in a period of time basis. The online procedures do the updates. The reports come later…

    1. Once the data is in Excel you can even use the Microsoft Flow add in or a Zapier add in to move that data to wherever you want, but that’s more of a capability of Excel rather than Power Query itself..
      Power Query per sé, inside Excel and Power BI Desktop, shouldn’t be used to upload data or do any “actions” against a data source.
      Those type of “actions” will be available inside of Logicapps, flow and others. More on the why Power Query inside Excel and Power BI wasn’t designed for these over here: https://social.technet.microsoft.com/Forums/en-US/ad808464-8819-45ac-a015-b53938095aae/how-to-use-webmethodput?forum=powerquery#fda41c1f-6ddc-4dec-8634-0f7bf06d5a53

      1. Hi Nick, we have added a new feature in our October Update (https://www.acterys.com/acterys-october-update/) that allows you to edit any RDBMS table from Excel. The full write back features (top-down/bottom-up, relative changes etc). from Power BI and Excel are only available in relational models created with the Acterys Modeller as they are reliant on a properly structured star schema and Acterys meta data handling.

  2. Well, right now you can use Dataflows or CDS-A and the results are written to ADLS v2, so “technically” the answer should be “it depends” 🙂

Leave a ReplyCancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.