This post covers a pattern for exporting filtered supplier data to CSV from a Power Pages portal in the following setup:
- Page
/suppliersrenders the user interface where filters are applied to request required data. Example:powerpages.com/suppliers?status=active&country=norway&withpackages=1 - Data API requests are made to
api/suppliers— a Liquid web template withapplication/jsonas MIME type, acceptingrequest.queryparameters to fetch filtered data from Dataverse. - To export CSV, the user clicks a button that requests
api/suppliers/export. The web template behind that page accepts the same query params from the URL. The page is restricted to theProcurementteam only. - The
Exportbutton is rendered only to procurement members. The/csvweb template short-circuits the response with an error for anyone else.
Two approaches are covered. The first — a single Liquid web template with nested FetchXML queries — works fine for small datasets but hits a hard wall at scale. The fix is a pre-joined Dataverse table populated by a Power Query dataflow, giving you a single flat FetchXML query that returns fast regardless of filter complexity.
The CSV download mechanics will be covered in a follow-up post.
The initial approach — nested FetchXML in Liquid
The supplier search pulls from multiple Dataverse tables: aa_lfa1 (vendor master), aa_aribasupplier (Ariba sync data), aa_package (simplified UNSPSC category), aa_finance (financial risk data), and aa_agreement (framework agreements). For paginated search results this is fine — 10 records at a time, each with a handful of inner FetchXML calls linked by aa_sapid.
For a CSV export the same pattern looks like this:
{%- fetchxml suppliers -%}
<fetch count="500" distinct="true" page="1">
<entity name="aa_lfa1">
<attribute name="aa_lfa1id"></attribute>
<attribute name="aa_sapid"></attribute>
<attribute name="aa_name"></attribute>
<attribute name="aa_countryname"></attribute>
<attribute name="aa_city"></attribute>
<attribute name="aa_vat_no"></attribute>
<attribute name="aa_status"></attribute>
<filter type="and">
{% if request.params['country'] %}
<condition attribute="aa_countryname" operator="begins-with" value="{{request.params['country']}}"></condition>
{% endif %}
{% if request.params['name'] %}
<condition attribute="aa_name" operator="like" value="%{{request.params['name']}}%"></condition>
{% endif %}
{%comment%} ...more filter conditions... {%endcomment%}
</filter>
</entity>
</fetch>
{%- endfetchxml -%}
[
{%- for supplier in suppliers.results.entities -%}
{
"sap_id": "{{supplier.aa_sapid}}",
"name": "{{supplier.aa_name}}",
{%- fetchxml aribaSups -%}
<fetch><entity name="aa_aribasupplier">
<attribute name="aa_prequallevel" />
<link-entity name="aa_lfa1" from="aa_sapid" to="aa_sapid" link-type="inner">
<filter><condition attribute="aa_lfa1id" operator="eq" value="{{supplier.aa_lfa1id}}"></condition></filter>
</link-entity>
</entity></fetch>
{%- endfetchxml -%}
"prequal": "{{aribaSups.results.entities[0].aa_prequallevel}}",
{%- fetchxml packages -%}
<fetch><entity name="aa_package">
{%comment%} ...join to aa_masterpackagelist... {%endcomment%}
</entity></fetch>
{%- endfetchxml -%}
"packages": "{{packages...}}",
{%comment%} ...other fetches for agreements, finance, etc... {%endcomment%}
}{% unless forloop.last %},{% endunless %}
{%- endfor -%}
]
The problem is the math: 500 suppliers × 6 inner FetchXML calls = 3,000 sequential Dataverse queries in a single request. In practice, 500 records takes over 2 minutes — right at Power Pages' request timeout ceiling. The export either times out or returns partial data.
distinct="true" on the outer fetch adds further overhead. It's needed to collapse duplicates caused by join-based filters, but forces a SELECT DISTINCT across all those joins before pagination even starts.
The fix — pre-join with a Power Query dataflow
Instead of joining at query time in Liquid, join once on a schedule using a Power Query dataflow and write the result to a flat Dataverse table. The export template then hits a single table with no inner fetches.
Step 1 — set up the aggregation queries
Each related table needs to be aggregated to one row per vendor before joining, otherwise the joins fan out rows and produce duplicates.
Packages (aa_package_agg) — concatenate all package codes per vendor:
let
Source = CommonDataService.Database("dataverse.crm.dynamics.com"),
Nav = Source{[Schema = "dbo", Item = "aa_package"]}[Data],
Cols = Table.SelectColumns(Nav, {"aa_masterpackagelist_lookupname", "aa_lfa1"}),
Expanded = Table.ExpandRecordColumn(Cols, "aa_lfa1", {"aa_sapid"}, {"sap_id"}),
Grouped = Table.Group(Expanded, {"sap_id"}, {
{"packages", each Text.Combine(List.Sort(List.RemoveNulls(List.Transform([aa_masterpackagelist_lookupname], Text.From))), ","), type text}
})
in
Grouped
Other tables are added as separate dataflow queries and aggregated where relevant — the pattern is the same: select columns, filter nulls, group by aa_sapid, aggregate with Text.Combine or List.Max depending on the field type.
Step 2 — the main export query
With the aggregated queries in place, the main query is a series of left outer joins starting from the vendor master:
let
#"Merged queries" = Table.NestedJoin(aa_lfa1, {"aa_sapid"}, aa_aribasupplier_agg, {"aa_sapid"}, "aa_aribasupplier", JoinKind.LeftOuter),
# ...other joins here...
#"Expanded aa_agreement" = Table.ExpandTableColumn(#"Merged queries 3", "aa_agreement", {"aa_agreement_active_count"})
in
#"Expanded aa_agreement"
Load only this final query to a Dataverse table (e.g. aa_suppliersummary). Set the other queries to not load. In the destination settings, use merge with aa_sapid as the primary key — this means a failed refresh won't leave you with an empty table.
Step 3 — the export web template
The Liquid template is now a single FetchXML against the flat table with all your existing filter conditions. No inner loops, no per-row queries:
{%- assign userRole = 'standard' -%}
{%- assign procurementTeamId = 'a3a5a17e-4b64-ab11-bfe3-000d3a48fb2a' -%}
{%- assign procurementTeamMember = entities.team[procurementTeamId]['teammembership_association'] | where: 'internalemailaddress', user.emailaddress1 | select: 'internalemailaddress' -%}
{%- if procurementTeamMember and procurementTeamMember != empty -%}
{%- assign userRole = 'procurement' -%}
{%- endif -%}
{%- if userRole == 'procurement' -%}
{%- assign query = request.query | split: '&' -%}
{%- fetchxml suppliers -%}
<fetch count="5000" page="1" returntotalrecordcount="true" {% if request.params.aribaquest %} distinct="true" {% endif %}>
<entity name="aa_suppliersummary">
<attribute name="aa_sapid"></attribute>
<attribute name="aa_name"></attribute>
<attribute name="aa_countryname"></attribute>
<attribute name="aa_city"></attribute>
<attribute name="aa_vat_no"></attribute>
<attribute name="aa_status"></attribute>
<attribute name="aa_packages"></attribute>
<attribute name="aa_prequal_level"></attribute>
<attribute name="aa_pending_prequal"></attribute>
<attribute name="aa_rating"></attribute>
<attribute name="aa_last_eval_year"></attribute>
<attribute name="aa_agreement_active_count"></attribute>
<filter type="and">
{%- if request.params['country'] -%}
<condition attribute="aa_countryname" operator="begins-with" value="{{request.params['country']}}"></condition>
{%- endif -%}
{%- if request.params['name'] -%}
<condition attribute="aa_name" operator="like" value="%{{request.params['name']}}%"></condition>
{%- endif -%}
{%comment%} ...other query filters... {%endcomment%}
</filter>
{%- if request.params.aribaquest -%}
{%- assign param = request.params.aribaquest | split: '|' -%}
<link-entity name="aa_aribasupplerqueststatus" from="aa_sapid" to="aa_sapid" link-type="inner">
<filter type="and">
<condition attribute="aa_qfullname" operator="eq" value="{{ param[0] | xml_escape }}" />
{%- if param.size == 2 -%}
<condition attribute="aa_qstatus" operator="eq" value="{{ param[1] | xml_escape }}" />
{%- endif -%}
</filter>
</link-entity>
{%- endif -%}
</entity>
</fetch>
{%- endfetchxml -%}
[
{%- for supplier in suppliers.results.entities -%}
{
"sap_id": "{{supplier.aa_sapid}}",
"name": "{{supplier.aa_name | replace: '"', '\"'}}",
{%comment%} ...other fields... {%endcomment%}
"packages": "{{supplier.aa_packages}}"
}{%- unless forloop.last -%},{%- endunless -%}
{%- endfor -%}
]
{%- else -%}
{"error": "Not assigned to procurement group."}
{%- endif -%}
The team membership check uses the pattern covered in Power Pages: how to get contact's Dataverse team membership in web template with Liquid — export is gated to procurement team members only, returning a JSON error for anyone else.
One more optimization — conditional distinct
Some filters add a link-entity inner join (e.g. aribaquest), which can fan out rows if a vendor has multiple matching records. distinct="true" collapses those back to one row per vendor, but forces a SELECT DISTINCT on every request even when no join is active. Since aa_suppliersummary is a flat table, distinct is only needed when such a join is in play:
<fetch count="5000" page="1" returntotalrecordcount="true" {% if request.params.aribaquest %} distinct="true" {% endif %}>
Only pay the DISTINCT cost when the join is actually active.
The result
- Before: 500 records, 6 inner FetchXML calls per row, ~2 minutes, frequent timeouts
- After: 5,000 records, single FetchXML against a flat table, 2 seconds
The 5,000 record limit is Dataverse's hard FetchXML ceiling per page, but that's sufficient for this corporate scenario. With client-side pagination (incrementing the page param from JS) you can page through larger sets if needed. The CSV generation itself will be covered in the next post.
The tradeoff is data freshness — the export reflects the last dataflow run rather than live Dataverse data. For a supplier directory that syncs on a schedule anyway, that's an acceptable tradeoff.
