DEV Community: Mark Nanneman

How to Visualize Data in Power Automate and Export a Graph Image without Power BI

Mark Nanneman — Mon, 10 Feb 2025 22:53:52 +0000

Believe it or not, you can graph data in Power Automate and display it as an image without using Power BI or some third-party connector or API endpoint.
The trick is using Excel Online Scripts.

Create Excel Online File

In this example I’ll create an Excel Online file in my SharePoint site.

Create a Table to Hold Your Data

I’ll make a simple table with the headers “state,city,population”.

I name the table “CityDataTable”.

Write a Script

The script is written in Typescript. You can use Chat GPT or another AI Assistant to help. In my experience they are pretty good but you always need to fix or tweak a few things.
Click on “Automate” then “New Script”.

Here is the code I used (generated by Chat GPT and cleaned up by me):



function main(workbook: ExcelScript.Workbook, inputChartName: string, inputData: { state: string, city: string, population: number }[]) {
    // Define the worksheet and table name
    const sheetName = "Sheet1"; // Change this to match your sheet name
    const tableName = "CityDataTable"; // Change this to match your table name

    // Get the worksheet
    let sheet = workbook.getWorksheet(sheetName);
    if (!sheet) {
        throw new Error(`Worksheet '${sheetName}' not found.`);
    }

    // Get the table
    let table = sheet.getTable(tableName);
    if (!table) {
        throw new Error(`Table '${tableName}' not found.`);
    }

    // Delete all existing rows from the table
    let rowCount = table.getRowCount();
    for (let i = rowCount - 1; i >= 0; i--) {
        table.deleteRowsAt(i, 1);
    }

    // Convert inputData to an array of arrays for table insertion
    let values = inputData.map(item => [item.state, item.city, item.population]);

    // Add new rows to the table
    if (values.length > 0) {
        table.addRows(-1, values);
    }

    // Delete existing charts
    let charts = sheet.getCharts();
    for (let chart of charts) {
        chart.delete();
    }

    // Define data range for the chart
    let dataRange = table.getRange();

    // Create a new Treemap chart with the data range
    let chart = sheet.addChart(ExcelScript.ChartType.treemap, dataRange);
    chart.setPosition("E1", "P20"); // Adjust position as needed
    chart.setWidth(1280); // Set width to 1280 pixels
    chart.setHeight(720); // Set height to 720 pixels

    // Set chart title
    let chartTitle = chart.getTitle();
    chartTitle.setText(inputChartName);
    chartTitle.setVisible(true);

    // Enable data labels and show category names
    let dataLabels = chart.getDataLabels();
    dataLabels.setShowCategoryName(true);

    // Get chart name and type
    let chartName = chart.getName();
    let chartType = chart.getChartType(); // Returns an enum value

    // Get chart image
    return {
        chartImg: chart.getImage(),
        chartName: chartName,
        chartType: chartType.toString() // Convert enum to string for readability
    }
}

Build a Flow

How you build your flow depends on your use case. The trigger will depend on whether you want to use this in a Scheduled Email Report or a Power App.

Trigger

In this case for demonstration purposes I’ll make it a public HTTP GET endpoint with no authorization.

I add some Relative Path Parameters for “type” and “count”.
“Type” will drive how the chart image is delivered (HTML or a file download).
“Count” is how many rows from my Dataverse Table I will visualize in the chart.

List Rows

Next I’ll list rows from my “Cities” table. The row count will be the “count” from the Relative Path Parameter. I’m sorting the rows by “Population” descending so that the largest cities appear first.

Select Data for Array to Pass to Excel Script

I use a Select action to select and form the array as I need it for my Excel Online Script.

Run Script (with Excel Online)

Add a “Run script” action.

Point the “Run script” action to the Excel File you created and populate the Script’s inputs.

For the “inputData” input, which is the array input, I sort the output of the Select action by “state”.
sort(body('Select_Cities,_States_and_Pops'),'state')

Compose a File Content Object

The “Run script” will return a Base64 encoded string for the Chart Image.

I construct an object with this in a Compose like the following.

{ "$content": @{outputs('Run_script')?['body/result/chartImg']}, "$content-type": "image/png" }

Return the Image in an HTTP Response Action

Since my flow is just to demonstrate different ways of returning the image, I have a condition based on the “type” parameter in the trigger url.

File Download Response

If the trigger type is “file” it will return a file that the browser will download.

To give the downloaded file a name, use a “Content-Disposition” header.

Here I construct a file name with a formatted date time string to make sure it’s unique.
{ "Content-Disposition": "attachment; filename=@{outputs('Run_script')?['body/result/chartName']} @{convertFromUtc(utcNow(),'Central Standard Time','yyyy-MM-dd hh-mm tt')}.png", "Content-Type": "image/png" }

The body is just the file content object I constructed in the Compose above.

It will download a file when the Trigger URL is loaded in a browser tab.

HTML Image Response

To return the chart image in the browser I set the “content-type” to “text/html” and build a simple HTML image element in the Body.

Encode the File Content Object created above with dataUri()

Test

Save the flow then copy the URL from the trigger.

Paste the URL in a browser tab and update the {type} and {count} parameters, then load.

Here I paste the trigger URL in a browser tab and try it with a count of 20 and then 300.

Using OData Lambda Expressions to Filter on Nested Collections and Related Tables in Power Automate

Mark Nanneman — Sat, 08 Feb 2025 21:22:17 +0000

I’m ashamed to admit this, but I just recently learned this really cool feature.

You can create “Lambda expressions” in OData filters to filter records by items that appear in a nested collection (array) or in a related table.

Here are some pages with official documentation on the subject:

Filter rows using OData (Microsoft Learn)

Basic Tutorial (OData.org)

How it Works

To use the Lambda expression in an OData filter, you specify the nested table column name (or relationship name for a related table) with a forward slash.

Then you use either any() or all() depending on whether you want any match to suffice or if you need the expression to result in true for every item in the collection.

Next you supply a name for each item in the collection (like an inline variable name). Usually it’s a single letter, but you could use longer strings. Put a colon after the name.

Finally you write the expression to be evaluated for each item in the collection.

<related_collection_name>/any(x:<expression to evaluate on all items>)

You can reference columns/properties in the collection with forward slashes.

E.g.

<related_collection_name>/any(x:x/<property_name_in_related_collection> eq '<some_string>')

Get Outlook Contacts Example

Here I’m using the “Get contacts (V2)” action to list contacts. I want to filter it for the contact with a specific email address.

Unfortunately, normal OData expressions won’t help because the email address for each contact is stored in a nested table called “emailAddresses” in a property called “address”.

Here Lambda comes to the rescue.

emailAddresses/any(e:e/address eq 'john.doe@email.com')

Dataverse Examples

Lambda expressions are very useful when filtering Dataverse tables for records that have certain child records. It saves you from having to create a longer FetchXML filter to accomplish the same thing.

Filter Accounts to Which Certain Contacts are Linked (N:1)

Here I want to list Accounts that have certain child Contacts. I’ll look for any Account with a Contact that has “Harkonnen” in the “fullname” field.

First I need to get the name of the relationship between Accounts and Contacts.

This can be found in make.powerapps.com by selecting the table and navigating to the “Relationships” list.

For the OData Lambda filter, I just need to type in the relationship name followed by a “/” then any(c: contains(c/fullname,'Harkonnen'))

contact_customer_accounts/any(c:contains(c/fullname,'Harkonnen'))

I could also use another field, such as last name.

contact_customer_accounts/any(c:contains(c/lastname,'Harkonnen'))

And I could also use a different expression than contains, for example a straight equals expression.

contact_customer_accounts/any(c:c/lastname eq 'Harkonnen')

Here I find one account, “House Harkonnen” that has child contacts with the lastname of “Harkonnen”.

Filter Records by Numeric Column on Related Table (N:1)

You can also use numeric comparison operators like “Less than” or “Greater than or Equal to” on columns on related tables with the Lambda ODatA expression.

I have a “States” table with USA States, and a “Cities” table that is linked to the “States” table, with population data for each city.

So if I wanted to list all states with a city that has a population of a million or more, I can do it easily with Lambda.

mln_City_mln_State/any(c:c/mln_population ge 1000000)

When it runs, I get these states:

Filter Users by Security Role (N:N)

You can also easily filter a table by records with an N:N relationship, which can be very helpful in some situations.

In this example, I’ll list all Users with a “System Administrator” Security Role.

The “Security Role” table has an N:N relationship with the “User” table. It’s called “systemuserroles_association”.

The lambda expression looks like this:

systemuserroles_association/any(r:r/name eq 'System Administrator')

I also add in a filter to only return Users with a “Main Phone” number, so that I can filter out non-human users.

systemuserroles_association/any(r:r/name eq 'System Administrator') and address1_telephone1 ne null

It works!

Get Number Set Statistics with an Excel Online Script in Power Automate

Mark Nanneman — Sat, 08 Feb 2025 21:09:16 +0000

There are a few methods to find the Sum, Mean and Mode of arrays of numbers in Power Automate by using Child Flows or Low Code Instant Plug-Ins and Power Fx expressions.

One of the better (and fastest) ways to do this using a simple TypeScript code executed with the Excel Online “Run script” action.

If you don’t know JavaScript, it’s not too hard to generate what you need using an AI Assistant.

Create an Excel Online File to Host Your Scripts

I created one in a SharePoint Site documents folder and named it “Power Automate Scripts”

Add New Script

Click “Automate” then “New Script”

Write Your TypeScript

I tried out ChatGPT to generate my TypeScript. It didn’t work right away. Excel did not like some of the syntax, but it was fairly easy to fix by telling ChatGPT the errors that Excel reported.

I also experimented with Grok and DeepSeek, both of which gave similar results but also contained some errors. The GPTs tend to want to write a script that reads a range on your Excel sheet, so you have to specify that they are taking inputs from the “Run Script” action in a Power Automate Cloud Flow.

Here is the final working TypeScript.



function main(workbook: ExcelScript.Workbook, inputArray: number[]): {
    modes: number[],
    maxFrequency: number,
    inputLength: number,
    minValue: number,
    maxValue: number,
    mean: number,
    standardDeviation: number,
    variance: number,
    sum: number
} {
    if (inputArray.length === 0) {
        throw new Error("Input array is empty");
    }

    // Count occurrences of each number
    const frequencyMap: Record<number, number> = {};
    inputArray.forEach((num) => {
        frequencyMap[num] = (frequencyMap[num] || 0) + 1;
    });
    // Find the maximum frequency
    const maxFrequency = Math.max(...Object.values(frequencyMap));

    // Find all numbers that have the max frequency
    const modeNumbers = Object.keys(frequencyMap)
        .filter((key) => frequencyMap[parseFloat(key)] === maxFrequency)
        .map((key) => parseFloat(key));

    // Find min and max values
    const minValue = Math.min(...inputArray);
    const maxValue = Math.max(...inputArray);

    // Calculate sum
    const sum = inputArray.reduce((sum, num) => sum + num, 0); // Added sum calculation

    // Calculate mean
    const mean = inputArray.reduce((sum, num) => sum + num, 0) / inputArray.length;

    // Calculate variance
    const variance = inputArray.reduce((sum, num) => sum + Math.pow(num - mean, 2), 0) / inputArray.length;

    // Calculate standard deviation
    const standardDeviation = Math.sqrt(variance);

    return {
        modes: modeNumbers,
        maxFrequency,
        inputLength: inputArray.length,
        minValue,
        maxValue,
        mean,
        standardDeviation,
        variance,
        sum
    };
}

Paste Your TypeScript into the Excel Code Editor and Save

Check that it finds no errors in the code, name the script and save.

Add the “Run script” Action in a Power Automate Flow

Add the Excel Online (Business) Connector “Run Script” action.

Locate your Excel file

Select the script you just added from the drop down:

Pass in the simple array of numbers you want it to analyze

Test

Here I will use a large SharePoint List that has over 20k items with an “Amount” column.

I list all the records using pagination.

I select only the amount column into a simple array.

The simple array is passed into the “Run script” action and returns the following:

{
        "result": {
            "modes": [
                9593.63
            ],
            "maxFrequency": 3,
            "inputLength": 23343,
            "minValue": 1.26,
            "maxValue": 9999.54,
            "mean": 4986.318588870353,
            "standardDeviation": 2882.714208802088,
            "variance": 8310041.209629446,
            "sum": 116395634.82000065
        },
        "logs": []
    }

Double check the results in an Excel export of the SharePoint List:

Calculate Frequency and Mode of a Value in a Collection in Power Apps

Mark Nanneman — Mon, 27 Jan 2025 16:34:25 +0000

The mode of a number or data set can be a useful statistic. There are some situations when the mean or average isn’t as helpful as the mode. The mode can give you the idea of a trend or most popular choice. Another example I’ve encountered is needing to identify the typical price of a good or service from large data sets in which there might be some variance due to discounts or human error.

Getting statistics like Mode and Standard Deviation are easy enough in Power BI or Excel. In Power FX there is a Standard Deviation function, StdP().

There’s no built in way to calculate the Mode in Power Automate or Power Apps, but here’s how you can do it.

Step One – Calculate Frequency of Distinct Items in Collection

In this example I’m using a collection called “gbl_col_prices” with two columns, price and title.

To get a distinct collection of the titles in the collection, I use Distinct()

Step Two – Add a Frequency Count Column

Use the AddColumns() function. The new column is called freq. The value for the new column is calculated by CountRows() on a filter of the original collection filtered by title = Value.

Value represents the Distinct Title from the previous step in the expression.

Then add a Sort() to sort by the new freq column in descending order.

Displayed in a gallery, the new collection of distinct titles with frequency counts now looks like this:

Step Three – Calculate Max Frequency

Use a Max() function on the distinct collection with added frequency count column we created in the steps above.

Max(<your_distinct_frequency_collection>,freq)
In my example, I display this in a text control like this:

Step Four – Find Modes

For most purposes, you can just filter your frequency collection for all items that match the max frequency, and take the first item to get your mode.

Technically, a set can have multiple modes, and you might want to display these, or take the middle mode.

In this example, I have a further break down where I check for frequencies of distinct prices for a select product, and display all the modes found in a gallery.

Expression for displaying all modes found in a gallery:

Expression for displaying first, middle and last mode found in a text control:

Thanks for reading!

If you found this post helpful please give it a like. Power Platform Developer | [LinkedIn | Wordpress | YouTube: Mark’s Power Stuff | Buy me a coffee

Cancel All Running Flows with One Click in Power App

Mark Nanneman — Sat, 25 Jan 2025 22:37:51 +0000

Cancel All Running Flows with One Click in Power App

January 25, 2025

The Problem

Have you ever had a runaway flow? I know I have.

Back in the early days, I would occasionally make a mistake resulting in an infinite loop, or a child flow being triggered a ridiculous amount of times.*

*Okay, TBH I occasionally still do this when I’m horsing around in my development environment

Sometimes the actions in the runaway flow start hitting throttle limits and they slow down, resulting in many flows stuck in a running status. This can impact the performance of your entire ecosystem and be very bad.

Canceling all the runs used to be a tedious processing of selecting them in batches of 20, or saving a copy of the runaway flow and then deleting it.

The Solution

Fortunately, Microsoft has added a “Cancel all flow runs” button.

This is great. Even better, there is an API endpoint you can use to cancel all flow runs for a selected flow. You can even find the link when you list your “workflows” (Processes) table in Dataverse:

Here is the endpoint:

https://<your_domain>.crm.dynamics.com/api/data/v9.1/workflows(<flow_name>)/Microsoft.Dynamics.CRM.CancelAllCloudFlowRuns

All you need to do is send an authorized POST to the url, and you will trigger the immediate cancellation of all running flows for the flow named in the url. You can do this in Power Automate or Power Apps with an Invoke an HTTP Request with Entra ID (preauthorized).

Example – Canvas App

Here’s how it works in a Power Apps Canvas App.

I have a Canvas App I’m building to manage Power Automate Cloud Flows in any environment. It uses the Dataverse “workflows” (Processes) table to list all flows in the environment, as well as HTTP calls to list all flow runs. In the screenshot below, I have opened up a flow called “Delay Flow Test” to view all the recent runs that belong to it.

The HTTP with Microsoft Entra ID (preauthorized) Invoke HTTP Action

I have added a “Cancel all flow runs” icon:

In its simplest version, the expression for “OnSelect” is as simple as this:

'HTTPwithMicrosoftEntraID(preauthorized)'.InvokeHttp("POST",$"/api/data/v9.1/workflows({loc_sel_flow.Process})/Microsoft.Dynamics.CRM.CancelAllCloudFlowRuns")

I’ve updated it to only run if there are running flows, and to send a notification. I’ve also moved the code to a confirmation popup to make sure the user wants to cancel all running flows.

In the end, this is what my code looked like:

This gets a count of the running flows that will be canceled, and performs a refresh of the select flow’s run history so that we can get immediate confirmation that the API action worked.

Make Sure Your “HTTP with Microsoft EntraID” connector is set up with a connection to your Dynamics Url, i.e. (https://<your_domain>.crm.dynamics.com)

Click Confirm to Invoke the HTTP and refresh flow runs

I click Confirm and receive this notification:

Flow history is immediately refreshed:

The flows that were submitted to the bulk cancellation API now show a “Cancelling” status.

Hope this helps!

If you found this post helpful please give it a like.

Power Platform Developer | WordPress | LinkedIn | YouTube: Mark’s Power Stuff | Buy me a coffee

Easily Get Numeric Dataverse Choice Set Value in Power Automate for a Text Value

Mark Nanneman — Mon, 13 Jan 2025 16:52:47 +0000

Sometimes in Power Automate you have a text value (string) and you want to use it to set a Choice Column in Dataverse. To do this, you need to get the numeric code value for that Choice Column Option. For large Choice Sets, it's impractical to use conditions. Fortunately there's a quick an easy way to get the required value using a Dataverse Web API endpoint.

Add an "Invoke an HTTP request" Entra ID action

We need to get a list of option values and their matching labels for our Choice Set in order to filter it based on the text on the submitted form. This can be done using a Dataverse Web API, which we can call with the "Invoke an HTTP request" action.

Add a connection and sign in

Use your Organization URL (first part of your url when you open a model-driven app) when creating the connection.

https://<organization-url>.crm.dynamics.com

You can also find it under "Developer Resources"

Construct a GET Method Request to the Solution Endpoint

We will use the "Retrieve Option Set" GET endpoint listed by Microsoft here.

/api/data/v9.2/GlobalOptionSetDefinitions(Name='<option-set-name>')?$format=application/json;odata.metadata=none You will need to put the logical name of your Dataverse Choice Set in the endpoint after "Name='".

You can find the logical name of your choice set in Dataverse.

It will look like this in your flow:

Filter the Returned Options Array for a Label that Matches the Text Submitted

When the "Invoke an HTTP request" runs the Dataverse Web API Endpoint will return a complex JSON object defining the Choice Set with another complex array of Options for it.

We need to filter the included "Options" array to find the item with a label that matches our search string, so that we can grab the "Value" property of that item.

From:

Select the "Options" array from the HTTP response like this:

body('Invoke_an_HTTP_request_-_Get_Choice_Set')?['Options'] First Parameter:

The label is stored in each Option item under the following path, which we use in the first parameter of the filter:

item()?['Label/UserLocalizedLabel/Label']
Second Parameter:

The second parameter is the search string, in our case the "State" name submitted on the form.

Code View:

Get the Value of the Item Returned by the Filter

The filter will return the entire JSON object of the item with a matching Label. We only need the "Value" property.

Use a compose to grab this.

first(body('Filter_array_-_Options'))?['Value']

Input this Integer on the "Add a new row" Action

Thanks for reading!

Power Platform Developer | LinkedIn: Mark Nanneman | Wordpress | YouTube: Mark’s Power Stuff | Buy me a coffee