M Mysteries: The Mysterious Type Action—An M-Internal Means to Write Data Modifications to External Systems

Power Query is great for reading, combining and computing data, but it’s not meant for writing data modifications—like inserts, updates or deletes—back to the source. Correct?

Yes and no.

What?!

If you are a non-internal user, then yes, Power Query is intended to be read only: it does not expose functionality meant for inserting, updating or deleting data on remote systems. But this doesn’t mean Power Query lacks this capability: to the contrary, it has hidden, internal support for performing data modifications!

Facilitating this functionality seems to be the purpose of the undocumented type action. You may never have heard of this type before. It’s not mentioned in the language specification. Very little related to it is exposed in Query Editor. It’s hidden away, not intended for general consumption. If you want to use Power Query to take action—a.k.a. to play with values of this type—you’ll need to use the Power Query SDK to create a project, then in the project’s properties, set “Run As Action” to true.

Welcome to a whole new world, a hidden universe!

Of course, since (almost) any action-taking code you write is limited to “living” in the SDK, you’re not going to be able to put actions to work for practical business purposes (that is, unless your business users all use the SDK, which isn’t ordinarily the case). Nonetheless, like most new things discovered in a new world, actions are fascinating to explore.

A subset of Power Query’s data connectors support actions. I don’t have an exhaustive list of action-enabled connectors, but based on testing, at minimum, Sql.Database and OData.Feed are action-enabled. Not every action-supporting connector supports the same level of actionality (i.e. the same level of action-related functionality), so what’s covered below may or may not work, or may not work with the same level of sophistication, if a different data connector is used than the one demoed.

Let’s try some actions with Sql.Database!

Insert

To write new data to a table-based data source, use TableAction.InsertRows, passing it a reference to the database table that should receive the insert, a type describing the data you’re about to insert and a Power Query table of the data to insert.

let
  Data = Sql.Database("server", "database"){[Name = "SomeTable"]}[Data],
  InsertAction = TableAction.InsertRows(
    Data, 
    #table(type table [ID = number, Column1 = text, Column2 = text], 
    {{1, "1","2"}})
  )
in
  InsertAction

A mismatch between column types as described in the table type you provide and how those columns are typed in the external data source’s schema may cause validation errors. For example, Sql.Database complains if the M type describes a particular column as “text” when on the database it is of a number type. (In contrast, OData.Feed doesn’t appear as strict on this point.)

InsertRows can be used with identity columns. If an identity column is targeted by the insert, the T-SQL that’s generated for it will include the appropriate SET IDENTITY INSERT on and off statements. For example, executing the above against a SQL table whose column ID is defined as “INT NOT NULL PRIMARY KEY IDENTITY” results in the following T-SQL being executed:

set identity_insert [dbo].[SomeTable] on
insert into [dbo].[SomeTable] ([ID], [Column1], [Column2])
output [inserted].[ID], [inserted].[Column1], [inserted].[Column2]
values
(1, '1', '2')
set identity_insert [dbo].[SomeTable] off

Update

Updating table rows is done via TableAction.UpdateRows. Each update is defined by a key-value pair-styled list. In each pair, the key specifies the name of the column to update; the value is a single argument function which will be invoked once per existing row of data. Each time it’s invoked, it’s passed a record containing the current row and is expected to return the new value for the specified column in that row.

If a single update is to be performed, its key-value definition can be directly passed as the second argument to TableAction.UpdateRows. If multiple updates are to be made, then the second argument should be a list of the key-value pairs defining the various updates.

let
  Data = Sql.Database("server", "database"){[Name = "SomeTable"]}[Data],
  UpdateAction = 
    TableAction.UpdateRows(
      Data,
     {
       {"Column1", each if [ID] < 3 then "hi" else "bye" },
       {"Column2", each "something else" }
     }
   )
in
  UpdateAction

The above is query folded into the following T-SQL:

update [dbo].[SomeTable] set [Column1] = case
    when [ID] < 3
    then 'hi'
    else 'bye'
end, [Column2] = 'something else'
output [inserted].[ID], [inserted].[Column1], [inserted].[Column2]

The level of sophistication allowed in the function that computes a column’s updated value appears to be limited.

The previous example updates all rows. Frequently, only a subset of rows should be updated. To achieve this, filter the table passed to TableAction.UpdateRows by using Table.SelectRows, as appropriate.

let
  Data = Sql.Database("server", "database"){[Name = "SomeTable"]}[Data],
  FilteredTable = Table.SelectRows(Data, each [ID] < 5),
  UpdateAction = 
     TableAction.UpdateRows(
        FilteredTable,
        {
          {"Column1", each if [ID] < 3 then "hi" else "bye" },
          {"Column2", each "something else" }
        }
     )
in
  UpdateAction

The above query folds to:

update [dbo].[SomeTable] set [Column1] = case
    when [ID] < 3
    then 'hi'
    else 'bye'
end, [Column2] = 'something else'
output [inserted].[ID], [inserted].[Column1], [inserted].[Column2]
where [ID] < 5

Delete

Deletes are performed by using TableAction.DeleteRows. Like updates, the rows to delete can optionally be constrained by first filtering the table passed to DeleteRows.

let
  Data = Sql.Database("server", "database"){[Name = "SomeTable"]}[Data],
  FilteredTable = Table.SelectRows(Data, each [ID] >= 10),
  DeleteAction = TableAction.DeleteRows(FilteredTable)
in
  DeleteAction

Folds to:

delete [dbo].[SomeTable]
output [deleted].[ID], [deleted].[Column1], [deleted].[Column2]
where [ID] >= 10

Results Returned

Each of the preceding action method returns a value of type action. When processed by the mashup engine, an action value performs its action and returns data (usually, the data affected by the action or some other meaningful value representing the action’s outcome, such as the number of rows affected).

With Sql.Database, a delete action returns the rows which were deleted. For inserts and updates using this connector, the action’s output is the inserted or updated data (respectively), from the database’s perspective.

These values may be different from the ones in the “data to be inserted” table you provided (for insert actions) or which were produced by your “compute updated values” functions (for update actions). For example, if you include the value “1.25” in your “to be inserted” data but that value is written into a column whose type in the database is INT, the server will truncate 1.25 to 1 when it saves it. The saved value of “1” (not the 1.25 you originally specified) is what will be returned in the output from TableAction.InsertRows. Another example: If rows you attempt to insert are ignored by the database server due to its IGNORE_DUP_KEY option being on, the ignored rows won’t be included in the action’s result because they weren’t actually inserted.

The output of an action may look like a table (or a number or text, etc., depending on the action), but is always of type action. This means that standard library functions which expect their input to be of a type like table, number or text can’t be used on the data returned by an action. So, even though the above TableActions all return what look like tables, their output can’t be passed to a method like Table.RowCount because it expects to be passed a value of type table, not of type action.

Action Sequences

Actions can be composed into a sequence that will be executed in order, thanks to Action.Sequence. This method’s single argument is a list. Each element can be either an action or an action-generating function.

The latter are single-argument functions which will be is passed the result of the previous action in the sequence (or null, if the function is first in the sequence) and should returns an action. This allows a step in the sequence of actions to adapt itself based on the output from its predecessor.

Here’s a simple example (no action generators involved) showing two different updates being sequenced:

let
  Data = Sql.Database("server", "database"){[Name="SomeTable"]}[Data],
  FilteredTable = Table.SelectRows(Data, each [ID] < 5),
  UpdateAction1 = 
	TableAction.UpdateRows(
	   Table.SelectRows(Data, each [ID] < 5), 
	   {"Column1", each [Column2] }
    ),
  UpdateAction2 = 
    TableAction.UpdateRows(
       Table.SelectRows(Data, each [ID] > 8), 
       {"Column1", each "Revised" }
    )
in
  Action.Sequence({UpdateAction1, UpdateAction2})

In contrast, below, the second step in the sequence uses an action generator to dynamically determine the action to perform, based on whether the first action modified at least 3 rows. If at least that many rows were updated, then the second action executed is UpdateAction2; if not, it’s Action.DoNothing. (The latter, when processed, does nothing and returns null.)

let
  Data = Sql.Database("server", "database"){[Name = "SomeTable"]}[Data],
 FilteredTable = Table.SelectRows(Data, each [ID] < 5),
 UpdateAction1 = 
   TableAction.UpdateRows(
     Table.SelectRows(Data, each [ID] < 5), 
     {"Column1", each [Column2] }
   ),
   UpdateAction2 = 
	 TableAction.UpdateRows(
	   Table.SelectRows(Data, each [ID] > 8), 
	   {"Column1", each "Revised" }
  )
in
  Action.Sequence({
    UpdateAction1, 
    (previousResults) => 
        if Table.RowCount(previousResults) >=3 
        then UpdateAction2 
        else Action.DoNothing
  })

Of note, the values passed to these action-generating functions are not of type action; instead, these input values are typed as what they really are (e.g. a table input will be of type table, etc.). This means that inside an action generator, you can use normal standard library functions on the provided input. If the previous action in the sequence outputs a table, the action generator that follows it will receive a value of type table and so can use functions that expect tables, like Table.RowCount, on it.

The result returned by Action.Sequence will be those output from the last action in the sequence.

Error Handling

Trying to use try (including try…otherwise) directly in an action sequence causes problems. Action.Sequence expects its input list elements to all be actions or action-generating functions. M’s try returns a record, which is neither an action or a function, so its presence causes an error. Even if this weren’t a showstopper, try can’t catch all errors raised from inside an action—which means that even a try…otherwise whose otherwise returns an action is problematic.

// errors because the first element is neither an action or an action-generating function (instead, it's a record)
Action.Sequence({ 
  try FirstAction, 
  SecondAction 
})

// works if FirstAction does not error; but if it does, may not catch the error
Action.Sequence({ 
  try FirstAction otherwise Action.DoNothing, 
  SecondAction
})

But there is hope! The action equivalent to try is Action.Try. It accepts an action as its argument and returns a record whose format is identical to what try returns, with one difference: it’s of type action.

// if SomeAction errors, returns [HasError = true, Error = {error details}]
// otherwise returns [HasError = false, Value = {value output by SomeAction }]
Action.Try(SomeAction ) 

Unlike try/try…otherwise , Action.Try is supposed to catch any error raised by the action it wraps.

Since Action.Try returns an action, it works just fine in an action sequence.

Action.Sequence({ Action.Try(FirstAction), SecondAction })

Arbitrary Values

Action.Return creates an action that, when processed, simply outputs the value passed into it.

Below, Action.Return is used to create an action that is effectively equivalent to Action.DoNothing.

Action.Return(null)

A more practical use for this method is to manipulate or override the results returned by a predecessor action before they are passed to a later action generator.

For example, say it’s desirable for a particular action to be treated as though it returned no rows if it errors. To make this happen, that action can be wrapped in an Action.Try then followed by an “adapter” action generator that returns the value from the preceding action, if it was successful, and an empty table, if that action errored.

let
  …
  TryWrappedUpdate = Action.Try(UpdateAction1),
  ErrorsToEmptyTableAdapter = (input as record) => 
    if input[HasError] = true 
    then Action.Return(#table(null, {})) 
    else Action.Return(input[Value]),
  ConditionalUpdate = (previousResults as table) => 
    if Table.RowCount(previousResults) >=3 
    then UpdateAction2 
    else Action.DoNothing
in
  Action.Sequence({ 
    TryWrappedUpdate, 
    ErrorsToEmptyTableAdapter, 
    ConditionalUpdate
})

Arbitrary Queries and Requests

There are times where you may want to create your own actions that execute queries or requested you define. ValueAction.NativeStatement and WebAction.Request are your friends. They’re close cousins of Value.NativeQuery and Web.Contents, respectively, but from the action side of the family (i.e. they create actions).

Let’s say you want an action that invokes a stored procedure. Not a problem:

let
  Source = Sql.Database("server", "database")
in
  ValueAction.NativeStatement(Source, "exec SomeStoredProc")

Why type action?

The presence of these methods raises an interesting question: Why are they needed? Why not simply use Value.NativeQuery and Web.Contents, wrapping their output with Action.Return, if needed. For that matter, why is type action needed in the first place? Why not have a Table.UpdateRows method that returns a value of type table containing the updated rows instead of TableAction.UpdateRows which returns an action—and why not have similar table methods for inserts and deletes?

Type action is undocumented, and I don’t have any internal knowledge here, so what I’m about to share is just a guess that could be way off—but maybe there are at least a couple of reasons for type action:

• To defeat M’s laziness. M is party lazy. Actions shouldn’t be. With a normal table, M only needs to do as much work as is necessary to compute the data you actually read. With actions, M performs the specified action, whether or not you read any of its results. So, due to M’s laziness, a method like Table.UpdateRows might not always do (all) the work you’d expect it to; actions don’t have this behavior.
• To avoid multiple executions. Behind the scenes, M’s streamed data types (like table) can be streamed multiple times during a single evaluation of an expression. In contrast, data modifying code should be executed exactly as many times as you specify, never more due to M’s internal operations. Using a separate type (action) segments data modifying code apart to (presumably) avoid the potential for multiple executions where you only expect one.

Internal Only

Actions are intended for Microsoft’s internal use. Apparently, behind the scenes, Microsoft (either in the past or at present) uses Power Query actions to write data in some scenarios, like Power Apps. They currently don’t plan to make action functionality available to the general public.

There’s scant evidence of this functionality’s existence in normal consumer-facing Power Query tooling, like Query Editor. In the latter, you can create type values for action (e.g. you can execute “type action“). Also, you can attempt to execute WebAction.Request, but from what I’ve seen it will error with “Expression.Error: We haven’t been given permission to perform actions against this resource.” Otherwise, the remaining action-related functionality seems to be under lock and seal, except if you use the SDK.

Conclusion

Even though the internal nature of actions means they have no practical use to the average Power Query user, they’re still quite interesting to explore—both for the fun of it and for the educational benefits (like to see how Microsoft implemented write functionality in what otherwise—at least, as far as we know!—is a read-only language).

I hope you’ve enjoyed our journey into the world of action!

Before we leave the topic, there is one additional action-related function available in the SDK: ValueAction.Replace. According to its metadata, it “creates an action that replaces the content of a value with the specified value.” However, I haven’t yet been able to figure out how to use it so didn’t cover it above. If you know how it works, I’d be most appreciative if you left a comment below. Thanks!