Before We Dive In
If you use Power Automate (Flow) and model-driven Power Apps, then (hopefully) you are aware of the “Common Data Service (current environment)” (CDS-ce) connector. Within this connector, there is an “Expand Query” option. This blog post will help explain how to use this.
Note: Check out Sara Lagerquist’s excellent blog called “CDS vs CDS: What Connector should I use in Power Automate?“. This will help you understand the difference between the Common Data Service connectors.
Let’s Dive In
Power Apps Solution
To get started, I have already created a solution within the Power Apps maker site called “PA Flows” (for “Power Automate Flows”). The next step would be to create a new flow within the solution.
Note: For this example, I have already started a flow with a Flow Button as the trigger. You can utilize whatever trigger you would like within your flow(s).
The Flow Setup
In the flow, I have a “List Records” action (renamed “List Opportunities”). In this example I am listing the Opportunities from Dynamics 365. I also have a simple ODATA “Filter Query” of “statecode eq 0” (0 = Active/Open).
Now, let’s check out what we can grab from the output of the “List Records” action. I would like to see if I can get the Opportunity’s:
- Associated Account’s name
- Associated Owner’s name and email address
- Associated Contact’s name
I inserted a “Create HTML Table” action to view the array output from the “List Records”.
As you can see above, the Account Name is not listed in the dynamic content when I search with “account”.
Using “Expand Query”
With the basic flow setup, let’s use the “Expand Query” option in the “List Records” action. First, you may be asking, “What do I put into this field?” Great question! And, there is a great solution.
As you can see below, I have created a simple FetchXML with “Opportunities” as the primary entity and have link entities to the Account, Contact, and User entities. For each entity I have added in a couple of fields.
The next step is pretty cool. Within the FetchXML Builder tool you can view the Power Automate Parameters.
Once this is selected, a small window will pop up with the values you will need for Power Automate.
Notice the “Expand Query” values listed? Keep in mind that right end of the field is chopping off what you see. Simply click on the “Expand Query” field and it will be copied to your clipboard. I have pasted the value into Notepad++ to show you the full value.
You can see the schema name of the related entity’s field followed by the selected field’s schema name.
Now, let’s see how this works inside Power Automate.
Back in Power Automate, I will paste in the newly copied “Expand Query” values into the “Expand Query” field on the “List Records” action.
With this field updated, this will now allow us to grab that information within our “Create HTML table” action.
First, let’s check to see if the “Account Name” is available. If you recall, this wasn’t available earlier.
As you can see above, the “Account Name” is now available!
What about the “Contact Name”? If you recall, we added this to the “Expand Query” field.
Yes! The Contact’s “Full Name” is visible and available to select.
OK… What about the Owner information?
Now let’s finish updating the “Create HTML Table”.
Testing the Flow
Now that we have the flow set up, let’s test it to see the output from the HTML Table.
As you can see, the table contains the information we need.
To further validate this, I put the output into an Excel file.
By leveraging the “Expand Query” option, we saved many steps within Power Automate. For example, if you wanted to get the associated Account’s name, you would use the “Get a Record” action. However, since the Opportunities are in a list (array), once you link the “Get a Record” to the “List Records”, an “Apply to each” will appear. You would have to loop through all the Opportunities to link each one with the correct Account to then get the Account Name.
Whew! What a chore!
“Expand Query” can help reduce the complexity of your flows, reduce the number of API calls made, and speed up your flows.
Until next time!