Tip #1458: How to copy files between Dataverse tables

No names in this story have been changed to protect the innocent—or was it to protect the guilty? Or to expose the innocent? Anyway, read on.

When Megan V. Walker (her real name) asks a question, others listen because it’s often a non-trivial challenge.

I have two tables, both with a file column. I want to use Power Automate to move the file from TABLE A to the corresponding field on TABLE B.

(Easy, right? Not so fast! – g.d.)

Continue reading

Tip #1457: Format JSON for human consumption

It’s not a secret that these days, data float around in automation using the JSON format. Submit a form, get JSON back. It is not human- (or any other carbon-based life form-) friendly.

{ "First Name": "Nancy", "Last Name": "Davolio", 
"Age": 42, "Influencer": true, "Customer": 
"Balderdash Primary School",  "CustomerPONo": 
"PO100042810",  "SalesOrderNo": "sy039978" }

My fellow Power Automate Gymnast, Amey “CI-J” Holden, asked if a code-only connector can be created to help produce nicely formatted emails containing form submission results. And it occurred to me that the JSON Extractor CoC can be used to accomplish the task in three steps:

Continue reading

Tip #1455: Experiment with parallel branches

While experimenting in a flow trying to find that elusive expression or action, I often catch myself doing this:

A partial screenshot of a Microsoft Power Automate flow, displaying a sequence of actions in a vertical layout. The process begins with a 'Manually trigger a flow' step at the top, denoted by a play button icon. Below it are four subsequent actions, each with a gear icon and labeled sequentially as 'Try Something', 'Try Something Else', 'Maybe This Will Work', and 'Yes Bet $100 This Is The One I Need!'. Each action is connected by a vertical line with a plus symbol, indicating potential insertion points for additional steps. The flow suggests a troubleshooting or iterative problem-solving approach within the automation.

The problem is, of course, I don’t bother setting Run After conditions and the result often looks like this:

Screenshot of a Microsoft Power Automate flow run, with a vertical sequence of steps. The first step, 'Manually trigger a flow,' is marked with a green checkmark, indicating it ran successfully. The second step, 'Try Something,' shows a red exclamation mark, signifying a failure in the flow. The subsequent steps, 'Try Something Else,' 'Maybe This Will Work,' and 'Yes Bet $100 This Is The One I Need!' are each marked with a no-entry symbol, indicating they were skipped due to the failure of the preceding action.

Now I have to edit, figure out what’s wrong, run again and again as I didn’t get to even try three other steps. If you find yourself doing the same mind-numbing experimental activities, make it a habit to use parallel branches!

Screenshot displaying a portion of a Microsoft Power Automate flow with options to modify the workflow. At the top, there's a 'Manually trigger a flow' step, depicted with a blue rectangle and a play icon. Below this step, there are two icons: a plus sign for 'Add an action' and a branching arrow for 'Add a parallel branch'. To the left, the first action in the workflow titled 'Try Something' is shown with a purple rectangle. Below, indicated by a plus sign, is a placeholder for another action, and further down is a second purple rectangle labeled 'Try Something Else', suggesting these actions are part of a sequence.

Add a parallel branch or two and rearrange the existing flow.

Screenshot of a Microsoft Power Automate flow diagram with parallel branches. The process starts at the top with a 'Manually trigger a flow' step, represented by a blue rectangle with a play button icon. From this initial step, four parallel branches extend horizontally. Each branch has its own action step: 'Try Something,' 'Try Something Else,' 'Maybe This Will Work,' and 'Yes Bet $100 This Is The One I Need!,' indicated by purple rectangles with corresponding gear icons. Plus signs are present between the steps and at the end of each branch, indicating potential points to add more actions or conditions to the flow.

The execution nails one of the actions as a solution in a single run!

Screenshot of a Microsoft Power Automate flow with four parallel actions following a successful 'Manually trigger a flow' step. The first and second actions, 'Try Something' and 'Try Something Else', are marked with red exclamation marks indicating failures. The third action, 'Maybe This Will Work', is marked with a green checkmark, signifying successful execution. The final action, 'Yes Bet $100 This Is The One I Need!', is again marked with a red exclamation mark, denoting a failure.

You can continue branching out, of course, on any level:

Screenshot depicting a complex Microsoft Power Automate flow with multiple levels of parallel branches. The flow is initiated by a 'Manually trigger a flow' step at the top. This leads to a first level of four parallel branches, each containing a single action. Beneath the third action, a second level of branching occurs, with two more actions that branch out further. In total, there are six actions in a two-tiered parallel structure, demonstrating the flow's capability to run multiple actions simultaneously at various stages.

PS. Credit where credit’s due: juggling parallel branches is much easier in the new Power Automate editor.

PPS. Use parallel branches for production runs as well. Don’t tell anyone but executing things in parallel is usually faster.

Tip #1453: Open the tools like a pro

Today’s tip is from Eric Regnier. (Have a tip of your own? Send it to jar@crmtipoftheday.com)

Ever wondered if you had the latest and greatest version of the Plugin Registration Tool (PRT) or Configuration Migration Tool (CMT)? And always had to Google Bing Copilot it to find and download the tool? You can now just pac it!

From any console like VS Code or PowerShell, just type pac tool prt for PRT and pac tool cmt for CMT.

You can also launch Power Platform Admin Center (PPAC) and the Marker portal from pac, run pac tool admin and pac tool maker, respectively.

For more info on pac CLI and how to install it: https://learn.microsoft.com/power-platform/developer/cli/introduction. Happy Power Platforming!

Tîpp Jäår $0.02 + GST

Sad child gif
Mac users in the meantime

Tip #1452: Compare dates in Power Automate


Always use formatDateTime(), substring(), convertFromUtc(), convertTimeZone() to drop the time part and ensure the dates are in the same timezone.


The datetime isn’t of any special type in Power Automate; it’s just a string representation of a timestamp in an ISO format. Yes, the format is sortable, so in theory, you could compare just the values. However, if you have a date input parameter in your flow, it comes as a date without any time part, so you might miss that birthday when comparing it to utcNow()!

To further complicate matters, datetime values include both the time portion and a timezone indicator. What is today for you can be yesterday or tomorrow for other people. Time difference can be up to 25 hours (don’t get me started on that Samoa vs American Samoa trip again). And do you really want to compare seconds if you’re after just the date?

Life becomes much easier if you follow two rules:

  1. Bring timestamps into the same timezone.
  2. Extract and compare just the date portion.

The following functions come to the rescue (yes, I am aware there are actions doing the same thing).

  • formatDateTime(date, 'yyyy-MM-dd') – just the date portion
  • substring(datetime, 0, 10) – just the date portion
  • convertFromUtc(datetime, 'Samoa Standard Time‘, ‘yyyy-MM-dd’) – if your user in +13 timezone
  • convertTimeZone(datetime, 'UTC-11', 'Samoa Standard Time', 'yyyy-MM-dd') – convert American Samoa date prior to comparing to the date in Samoa (be very surprised)

Timezones always do my head in (would it be so hard to make them an enumeration?!). Here’s the current list: Default Time Zones | Microsoft Learn. Perhaps I should create a Code only Connector (CoC) for it, so I don’t have to remember the list?

Tip #1451: Aggregates in Power Automate

There is no easy way to calculate aggregates in Power Automate (except count, of course, which technically, is an aggregate). You do have a few options available to you: loop over the array and apply hideous formulas forgotten since the statistics/SQL course, use Graph API and bring magic of Excel formulas, use third-party connectors (which is not a bad option if you already paying for them). If your data coming from Dataverse you can use FetchXml. SharePoint data can be wrestled with CAML aggregations (yes, it’s a real thing). You can probably even cook some joujou with xml transforms.

Enter Code-Only Connectors (CoC). For a walkthrough watch Hour 16 “Mastering Custom Code in Power Platform Connectors” at Power Platform 24 Conference (double speed recommended, my speech patterns can be painfully slow after excessive travel). Here’s some magic we can do with just code by using Aggregate and CSV To JSON CoCs.

Aggregate array of numbers

Input is an array of primitive values that can presumably be converted to numbers.

Screenshot of an Aggregate action configuration. Values property is set to a formula creating an array of random numbers. Property called "Property" is blank indicating that the input is an array of primitive values.

Output is a set of Excel-like aggregate functions: Sum, Avg, StDev, Median, Min, Max, Product and a bonus ModeSngl (the most frequent value in the sequence).

Runtime output of the Aggregate action. Standard aggregate values like Sum, Avg, Min, Max, etc are included as output properties.

Wait, there is more!

Aggregate array of objects

When you have an array of objects and need some aggregates on a property, it’s an extra effort to extract the array holding just those numeric values. Our connector can take an array of objects, all you need to do is to tell it which property to aggregate.

This example uses sample CSV data containing some contact information including the age. The first action is a simple CSV set.

The image is a screenshot depicting a segment of a Microsoft Power Automate flow. It consists of three sequential actions aligned vertically. The first action at the top is titled "CSV Input" and is marked by a purple icon resembling a file or document. The second action is titled "Csv To Json" and is represented by an orange icon with a gear, indicating a transformation or processing step. The third and final action displayed is titled "Aggregate on Array of Objects" accompanied by a smiley face emoji, suggesting an operation that handles a collection of data structures.
Screenshot of CSV sample data

Second action takes that CSV and converts it to JSON:

Screenshot of a runtime output of the CSV to JSON action. Output contains a standard JSON object with properties corresponding to the input CSV columns

And the last step brings it home by taking the JSON object ‘as is’ and calculating aggregates on the ‘Age’ property.

The image is a screenshot of a Power Automate action titled "Aggregate on Array of Objects 😎". The input fields are Values and Property. Values is set to the output of the previous step which is a JSON object. Property set to the text "Age" indicating that this property should be used to calculate the aggregates.
The image is a screenshot of an Power Automate action output titled "Aggregate on Array of Objects 😎". It displays a JSON response structure with various fields. The "statusCode" is 200, indicating success, and there's a "headers" object containing HTTP headers such as "Transfer-Encoding", "Vary", "Request-Context", and "Date".  Inside the "body" object, there are key-value pairs for aggregation results: "Sum" is 367, "Avg" (average) is 36.7, "Max" (maximum) is 52, "Min" (minimum) is 27, "StdDev" (standard deviation) is approximately 8.12, "Variance" is approximately 66.01, "Median" is 36.5, "ModeSngl" (single mode) is 29, and "Product" is a very large number, displayed in scientific notation as 3479971800000000.

There are plenty of plans to add other Code Only Connectors (CoCs). There are some decisions to be made: how to compose a single connector with all methods included, how to validate syntax, is it possible to create a test harness, shall we publish these as independent custom connectors or just leave it like that?

Want to get involved? Drop email to jar@crmtipoftheday.com and/or go straight to the source https://github.com/georged/code-connectors.

Tip #1450: Create Dataverse app users like a boss using pac cli

In another universe we did talk about Tip #545: Create non-interactive user like a boss. Since then, the product name has changed at least 27 times, Marketing Pilot and Parature came and went, Dataflex and CDS left their mark but, most importantly, we’ve got ourselves app users based on service principals.

It’s been quite a painful manual process creating those users: in Azure portal create an app registration, create a secret, add API permissions – NO, STOP DOING IT PEOPLE, IT’S NOT NEEDED, forget to copy the secret, go back 2 steps, generate new secret, switch to Dataverse admin, create an app user, assign appropriate roles (“appropriate” != “system administrator”). Switching between admin centers, tedious is the word to describe the experience.

But tedious it is no more and there is no PowerShell in sight! Behold, administrators!

pac admin create-service-principal \
   -n "LOLZ App" -r "Basic User" 

# -n name and -r role, respectively

The result:

@georged ➜ /workspaces $  -n "LOLZ App" -r "Basic User"
Connected as doug@aperturescience.click
Creating Entra ID Application 'LOLZ App'... Done
Creating Entra ID Service Principal... Done

Connected to... Candlestick Maker
Registering Application 'D4E4C8F1-9A3B-4D0A-8E7C-2E5A4F6D1B9D' with Dataverse... Done
Creating Dataverse system user and assigning role... Done

Application Name         LOLZ App
Tenant Id                3F2504E0-4F89-11D3-9A0C-0305E82C3301
Application Id           6B29FC40-CA47-1067-B31D-00DD010662DA
Service Principal Id     F47AC10B-58CC-4372-A567-0E02B2C3D479
Client Secret            pO~8Q~Really?~Z2sdwvDwgM_HowsThatWorkingOutForYou?_aT2
Client Secret Expiration 03/24/2025 09:17:05 +00:00
System User Id           0e68ef5a-bfe9-ee11-a204-dead4beeff1c

@georged ➜ /workspaces $ 

That’s it! Read Microsoft Power Platform CLI admin command group – Power Platform | Microsoft Learn as the official source of truth.

I got a bit excited and didn’t check the references before writing the above. Awesome Carl de Souza got their first. Creating Service Principals Really Easily Using Pac Cli. Unlike me, Carl has patience to produce screenshots and detailed instructions, go check it out.

Like a boss meme

Tip #1449: Formula columns love calculated columns

Formula columns are great, just read Work with Dataverse formula columns. But there are some limitations in the Power Fx functions you can use, as well as with the return types..

  • Need a formula column returning a whole number? Make it a decimal with 0 precision.
  • Need to produce currency? Make a formula to return a decimal, then convert it to currency using the good old calculated column where you can define the return data type instead of inferring.
  • Need a choice? Your choice can only be either yes or no. But the same trick applies. Do your magic in a formula column, then create a calculated column and go through the painful if-then-else process ad nauseam to cover all possible return values. To be honest, I’d rather eat a frog and write a plugin.
Witch Mountain meme with the following dialog:

two characters in a car having a technical discussion, which could be imagined as:

Character 1 (top panel, speaking seriously): “Our Dataverse telemetry indicates formula columns aren’t being leveraged for generating monetary returns.”

Character 2 (middle panel, with a questioning expression): “Maybe it’s because currency formula columns can’t be produced?”

Character 1 (bottom panel, looking puzzled or skeptical): [No text, just the character's reaction]

Personally, I think it’s all because, ironically, Power Fx in formula columns does not support Power function. Source: trust me, dude.