Using Expand Query in Power Automate with CDS

Power Automate plus Common Data Service

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.

Power Automate solution created within  Power Apps maker site.
Power Automate solution created within Power Apps maker site
Create new Power Automate flow within solution.
Create new Power Automate flow within 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).

Power Automate "List Records" action
Power Automate “List Records” action

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”.

Power Automate - Create HTML Table action
Power Automate – Create HTML Table action

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.

Open XrmToolBox and launch the “FetchXML Builder” from Microsoft MVP, Jonas Rapp.

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.

XrmToolBox - FetchXML Builder
XrmToolBox – FetchXML Builder

The next step is pretty cool. Within the FetchXML Builder tool you can view the Power Automate Parameters.

XrmToolBox - FetchXML Builder - Power Automate Parameters
XrmToolBox – FetchXML Builder – Power Automate Parameters

Once this is selected, a small window will pop up with the values you will need for Power Automate.

XrmToolBox - FetchXML Builder - Power Automate Parameters - Expand Query
XrmToolBox – FetchXML Builder – Power Automate Parameters – Expand Query

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.

Power Automate - Expand Query values
Power Automate – Expand Query values

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.

Power Automate - Expand Query values entered
Power Automate – Expand Query values entered

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.

Power Automate - HTML Table - Account Name available
Power Automate – HTML Table – Account Name available

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.

Power Automate - HTML Table - Contact Name available
Power Automate – HTML Table – Contact Name available

Yes! The Contact’s “Full Name” is visible and available to select.

OK… What about the Owner information?

Power Automate - HTML Table - Owning User Name and Email available
Power Automate – HTML Table – Owning User Name and Email available

Now let’s finish updating the “Create HTML Table”.

Power Automate - HTML Table updated
Power Automate – HTML Table updated

Testing the Flow

Now that we have the flow set up, let’s test it to see the output from the HTML Table.

Power Automate - HTML Table output
Power Automate – HTML Table output

As you can see, the table contains the information we need.

To further validate this, I put the output into an Excel file.

Excel list of the HTML Table output
Excel list of the HTML Table output

In Conclusion

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!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

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