Share Data from Power BI using Power Automate and Excel

[Chihiro]

I recently had a client approach me with request that involved sharing Power BI data with non-licensed users. The client did not have support from IT department and did not have budget for dedicated premium license.

Initially recommended having multiple Power BI Pro license as cheaper alternative, but the client had issues getting C-suits to consistently login and navigate to required report, as well, the client requested it be in Excel file so users can add their notes and inputs.

Objective:
Send email on schedule and give access to matrix data in Excel file.

Issues:
Power BI has export function and can leverage Power Automate to export data with single click, but this was bit too manual for the client. Power BI API was not an option as the client didn't have support from IT department and needed something that worked in existing config. Power Automate's "Run a query against a dataset" action doesn't support matrix visual DAX query and will return error "More than 1 result set has been encountered. Only 1 result set is supported".

Solution:
Pass JSON object to Office Scripts hosted on Excel file stored in OneDrive for Business, from Power Automate and write data to Excel file. Then use PIVOTBY() function to reshape data into matrix.

Steps:
1. First make sure that semantic model is published to Power BI service. This requires Power BI Pro license (only one is required).

2. In Power Automate, create Flow using manual trigger. Add "Run a query against a dataset" action. Establish connection using credential for Power BI Pro.

3. Write DAX query in Query text field, to summarize data and return result set. If you are not familiar with DAX query syntax, you can create table visual in Power BI Desktop. Then use Optimization ribbon tool, run Performance analyzer, recording "Refresh visuals" action. Navigate to table visual desired and copy DAX query. Note that you will need to remove "VAR __DS0PrimaryWindowed" line and replace EVALUTE target with "__DS0Core".

Sample Query:

// DAX Query
DEFINE
    VAR __DS0FilterTable =
        FILTER(
            KEEPFILTERS(VALUES('Calendar'[Date])),
            AND('Calendar'[Date] >= DATE(2025, 1, 1), 'Calendar'[Date] < DATE(2026, 1, 1))
        )

    VAR __DS0Core =
        SUMMARIZECOLUMNS(
            ROLLUPADDISSUBTOTAL(ROLLUPGROUP('Calendar'[YearMonth], 'User'[Name]), "IsGrandTotalRowTotal"),
            __DS0FilterTable,
            "NewLogo", '02 - ClosedDate_Measure'[NewLogo]
        )

EVALUATE
    __DS0Core

ORDER BY
    [IsGrandTotalRowTotal] DESC, 'Calendar'[YearMonth], 'User'[Name]

4. Save Flow and run a test to ensure the result is returned.

5. Add "Parse JSON" data operation. Use ['body/firstTableRows'] from "Run query..." action as content and use content copied from test run to generate schema.

6. Use "Filter array" data operation to remove Total rows, by keeping rows that show '[IsGrandTotalRowToal]' is equal to false.

7. Use "Select" data operation to rename and map columns to desired format without DAX syntax.

8. Create Excel file in OneDrive and prepare dedicated sheet for storing data. Add Office Scripts to receive JSON object and process. If unfamiliar with Office Scripts and JSON object handling, see link for comprehensive tutorial.

Sample Office Scripts:

function main(workbook: ExcelScript.Workbook, jsonData: object[]) {
    // Main function to clean range and place JSON data in range
    let selectedSheet = workbook.getWorksheet("Data");
    selectedSheet.getRange("A:C").clear(ExcelScript.ClearApplyTo.contents);
    const columnNames = getPropertiesFromJson(jsonData[0]);
    selectedSheet.getRangeByIndexes(0, 0, 1, columnNames.length).setValues([columnNames]);
    const myval = jsonData.map(row => convertJsonToRow(row));
    selectedSheet.getRangeByIndexes(1, 0, myval.length, columnNames.length).setValues(myval);
}

// Get Column Names from JSON obj
function getPropertiesFromJson(obj: object) {
    const propertyArray: string[] = [];

    // Loop over each property in the object and store the property name in an array.
    for (let property in obj) {
        propertyArray.push(property);
    }

    return propertyArray;
}

// Convert JSON obj values into array
function convertJsonToRow(obj: object) {
    const array: (string | number)[] = [];

    for (let value in obj) {
        array.push(obj[value]);
    }

    return array;
}

9. Use "Run script" Excel action to pass output from "Select" data operation to the script.

10. Save and test. Check that data in Excel is correct.

11. In separate sheet, use PIVOTBY() formula to summarize and aggregate data. Here, you can combine it with LET(), BYROW(), LAMBDA() and other functions to suite your need (ex: using BYROW() & LAMBDA() to replace blank value with desired output).

12. Generate sharing link from Excel file with desired security level.

13. Add last step in Flow to send email to recipients with link generated above as body.

14. Test, then replace manual trigger with Recurrence.