Salesforce Report Export via Analytics API

Overview

Runs every morning at 6:00 AM Eastern. Calls the Salesforce Analytics API to execute a saved report, parses the tabular grand-total fact-map rows out of the JSON response, and appends each row (plus a run-date stamp) to an Excel table hosted on SharePoint. The Excel table is a persistent, append-only source that Power BI can connect to directly — no Salesforce connector needed on the BI side.

Out-of-the-box Power BI Salesforce reports auth to Salesforce per-user and are opaque to anyone without licensed Salesforce seats. This flow externalises the report payload into a Microsoft-native destination, so BI downstream (Power BI, Excel, Fabric) can operate on the data without Salesforce credentials. It also captures a timestamped history: every run appends new rows rather than overwriting, so you get day-over-day trending for free.

Use Case

Sales operations and executive reporting teams that need to surface Salesforce report data inside the Microsoft BI stack without granting Salesforce seats to every consumer. By using the Salesforce Analytics API through SendHTTPRequest and persisting results to an Excel table on SharePoint, the flow creates a self-serve, append-only history that Power BI, Excel, and Fabric can read directly. A preflight SharePoint call fails the run early when the workbook is mis-pathed, conserving the daily Salesforce API quota.

The flow is ideal for teams that:

• Sales and revenue operations teams who want Salesforce report data in Power BI without Salesforce-licensed consumers
• Executive reporting where day-over-day trending of report metrics is required
• Organisations standardising BI on Microsoft 365 / Fabric and wanting to avoid per-user Salesforce auth on dashboards
• Advanced makers comfortable with Salesforce's SendHTTPRequest action and parsing the Analytics API JSON fact-map

Flow Architecture

Environment Variables

Connectors & Connections

Note — All connections are referenced as solution connection references; the flow is portable between environments as long as a connection is mapped at import time.

Customization Guide

Almost every realistic variant of this flow can be implemented by changing environment variable values. A few cases require small edits inside the flow definition — those are called out explicitly below.

Change the schedule cadence

Edit the Recurrence_Daily_6AM_Eastern trigger's schedule / frequency / timeZone to run hourly, weekly, or in a different time zone.

Capture more report columns

Extend the item object on Append_Row_To_Excel_Table with additional Col6..ColN keys and matching dataCells?[n]?['label'] selectors, then add matching column headers in the Excel table.

Project multiple aggregates

aggregates?[0] is the first aggregate on each row. For multi-aggregate reports, add AggregateValue2, AggregateValue3 entries (and matching Excel table columns) pointing to aggregates?[1], aggregates?[2], etc.

Retarget a summary or matrix report

Tabular reports use the T!T factMap key. Summary and matrix reports use grouping keys such as GroupingKey_0!T. Swap the factMap path in Compose_Grand_Total_Fact_Rows when retargeting a non-tabular report.

Rename the Col* headers

The default Col1..Col5 labels are positional placeholders. Rename them in the Excel table header to match the actual field names returned by the Salesforce report — the flow does not need any change.

Key Expressions

The flow is intentionally light on Power Fx / WDL gymnastics — the heaviest expressions are the branch-name concatenation and the approval outcome check. They are listed below in the order they appear in the flow.

concat('/services/data/', variables('varSalesforceApiVersion'), '/analytics/reports/', variables('varSalesforceReportId'), '?includeDetails=true')

coalesce(body('Parse_Salesforce_Report_Response')?['factMap']?['T!T']?['rows'], createArray())

{
  "ReportRunDate":   "@{utcNow()}",
  "ReportId":        "@{variables('varSalesforceReportId')}",
  "Col1":            "@{coalesce(items('Foreach_Report_Row')?['dataCells']?[0]?['label'], '')}",
  "Col2":            "@{coalesce(items('Foreach_Report_Row')?['dataCells']?[1]?['label'], '')}",
  "Col3":            "@{coalesce(items('Foreach_Report_Row')?['dataCells']?[2]?['label'], '')}",
  "Col4":            "@{coalesce(items('Foreach_Report_Row')?['dataCells']?[3]?['label'], '')}",
  "Col5":            "@{coalesce(items('Foreach_Report_Row')?['dataCells']?[4]?['label'], '')}",
  "AggregateValue":  "@{coalesce(items('Foreach_Report_Row')?['aggregates']?[0]?['label'], '')}"
}