Power Automate actions produce JSON outputs. Depending on the connector you’re using, you can oftentimes reference these using the built-in dynamic content cards that appear in the UI. But what happens when you want to reference something that’s NOT in the dynamic content card list?
Option 1: Use PowerFX to reference values
You’d use this option when you want to use a very specific thing in an action or trigger output and that thing does not already have a dynamic content card built into the connector. An example of this virtually anything from a HTTP action and many of the AI Builder text returns.
For example, when I run an “HTTP” action to call Azure OpenAI, the output looks like this. I only really care about the prompt response, which is nested under body -> choices -> message -> content, and I need to extract that text in order to do meaningful things with it, like insert it in a field, or send it in an email.
To get that response text, you can reference it like this – I would usually use a “compose” action for this, but you could put it in a variable too.
first(body('HTTP_to_AzureOpenAI')['choices'])['message']['content']I used first() here, because “choices” in this particular output is an array with multiple objects; it’ll throw an error if I don’t use it. You’ll note that the action name is referenced in single quotes and uses underscores instead of spaces. This is the pattern you use for any action references. You essentially work your way down the JSON structure via the key names in square brackets.
How come I sometimes see question marks in these expressions?
You’ll often find examples of expressions that have question marks between the square brackets. The question marks are used to avoid the step throwing an error if it hits a null value when reaching for what you are trying to get. Instead, it’ll just output null.
Here’s the same expression, but with the question marks to handle nulls. Practically speaking, this particular call would never have a null value, so we don’t really need it here, but you get the idea. 🙂
first(body('HTTP_to_AzureOpenAI')?['choices'])?['message']?['content']When do you use outputs() instead of body()?
The output for a step contains both the headers and the body. Headers are going to contain metadata about what happened, the date/time it ran, and other bits and pieces. The body is going to be where your meaty data is. So, if you want something from the header, use outputs(), if you want something from the body, use body().
Additionally, most of this is just shorthand anyways, so there’s a longer version of most things that looks something like actions(‘Action_name_here’).outputs.body as an example… I find it easier to use the shorthand notation.
The headers can have some useful into in them. Here’s an example of what a reference to a header value looks like:
outputs('Action_name_goes_here')['headers']['header_property_name_here']Here’s a real example; this would get me the upstream response duration for the HTTP action. Here’s the JSON output:
Expression to get the upstream duration:
outputs('HTTP')['headers']['cmp-upstream-response-duration']How to use expressions to record error messages
You can use this same technique for error handling to extract the error code and text to log or send in a notification. The errors follow a regular structure as far as I can tell, but if you want to see the JSON you can just force a flow failure and look at the outputs. In this case, I fed an invalid plan ID to the List Buckets step to view the error code (a successful action won’t have error data included in the output).
Here’s what we’re after; this particular error doesn’t have an error code but you see where we’re going with this:
We can compose the error message or drop it directly into an email, create an item in a logging SharePoint list, or add a row to an Excel file with it; e.g. :
Usually you’d put this in a parallel branch under the action you’re concerned with so that you can set the “run after” for the actions to be “after failure”. That’ll make it so the action is only logged or emailed if the step fails.
Here’s the format for the error references:
outputs('Action_name_goes_here')?['error']?['code']
outputs('Action_name_goes_here')?['error']?['message']We’re referencing it from the compose step here, but if you’re referencing it from an action body just add the body reference:
outputs('Action_name_goes_here')?['body']?['error']?['code']
outputs('Action_name_goes_here')?['body']?['error']?['message']When do you use items()?
The items() function will usually come up when you’re referencing something that is a part of an “apply to each” loop. The items in a loop are the rows it’s looping over, and you can use items() to reference field values on the current item. The syntax is similar, e.g.:
items('apply_to_each_loop_name_here')?['fieldname']You don’t have to reference the item directly by name, because that’s in the loop’s context – it knows what it’s looping over already.
Referencing by position
In addition to named keys, you can also reference positions for arrays. Note it’ll work only for arrays though – you’ll get an error if you try this with objects. This article gives a good description of the difference between an array and an object.
So if you have an array in a variable, e.g. [“foo”, “bar”], you could reference foo by using the number for the first position, which is 0:
variables('arrayVarNameHere')[0]“Query a Power BI Dataset” & referencing or looping over rows
There’s an action in Power Automate to query a dataset in Power BI. This can be useful for sending automated notifications or performing other automation on the content of a dataset. Its output is “tables”, of which there supposedly can be multiple, but in normal use you will only ever have one table. However, because there can be multiple, you want to get the first result and table in references. The output of this action is an array, so we need to use the index reference [0] to get the first – I tried using first() and it gave an error.
The connector already has a dynamic content card for “first table rows”, which I think is literally that. There’s some confusion in the community over the ambiguous name, where some people think it only has the first x rows, but I think it’s just a paraphrasing of the fact that there could be multiple tables.
Anyways, I occasionally have issues trying to loop over the outputs of this action, so after coming at it from a few angles I am leaning towards just referencing the rows directly with an expression. You can use this expression in a loop to loop over query output rows, though if you want to reference specific fields in the table you may want to parse it first.
body('Query_a_Power_BI_Dataset_action_name_here')?['results'][0]?['tables'][0]?['rows']Option 2: Parse JSON
Use the Parse JSON action when you are wanting to reference multiple fields in an output, or assign “type” values like date or integer to our step outputs. The latter can be really useful if you’re trying to feed data into another action that only allows specific value types (e.g. booleans). Parse JSON gives you dynamic content cards for things in your output. You can find the action by searching the name:
Parse JSON lets you paste in any JSON output to create the schema for you, meaning you don’t have to hand-code it yourself, but it’s not 100% reliable – always look at what the schema output is when you give it an example to make sure it makes sense. Use the “generate from sample” button to do generate the schema if you don’t want to hand-craft it:
Where do I get a JSON sample?
You can get a sample by copying the output from your desired action from your flow history. From your flow page, click on the date/time of a flow run to open it, then expand the action you want an output for.
Usually you’ll want the body, so copy that to paste into the schema generator:
Note that you don’t HAVE to use the body, if whatever step you want to parse outputs something else, you can parse that too. Just make sure that whatever card you feed Parse JSON matches the sample you give it for the structure. You could even feed Parse JSON a nested element with an expression from the first part of this article along with the nested bit of JSON sample if you wanted to. Paste whatever you want to use as the sample into the Payload box (not the schema box) and click “done”.
Check your “types” and “required” in the generated schema
Each of the keys in the sample will have been assigned a type: string, integer, number, boolean being the most common. Make sure they match what you want to output in your flow – for example, if you feed parse JSON a sample number like 1.00, which happens often in currency, it might assign the field “integer”, which will make the parsed value be 1. This sounds like not a big deal, but if you’re generating invoices, and numbers could actually be input with decimal values, it will output whole numbers – that’s no good, so double check it.
It will also very often set any field that is not null in your sample to required. You can simply erase the “required” designation for anything that shouldn’t be required – it’ll be towards the bottom. Required means that if it’s null, the parse action will fail. If you remove something from required, the value of the parsed field will simply be null instead.
Make sure your commas are correct if you’re removing entries, if you typo here you’ll invalidate the JSON.
Now you can use it!
Under the parse step, you’ll now be able to reference the parsed JSON as dynamic content cards. The cards you can see/use may change depending on the action you’re using (they’re not always accessible for some reason…), but you should now be able to loop over the “rows” at each of the layers of your parsed JSON if you want to, or use the “create CSV” action if your structure is flat.
If you try to use a card that is nested under other properties, Power Automate will often auto-add a loop for you. You can get around the extra loops by using an expression to insert a first() around the dynamic card value if it only ever has one value anyway (this is useful for things like approver comments with approval actions if you only have one approver – it always likes to put these in unnecessary loops!).