PowerApps RAW!

Set Your Preferred Solution for the Dataverse MCP

2026-03-26T10:01:53.289-04:00

I was working with the Dataverse MCP server yesterday and noticed that when I created new tables through it, they were all getting the default cr*** prefix from the Common Data Services Default Publisher. The MCP doesn’t give you an option to select a publisher or a prefix when creating table customizations — it just uses whatever the environment default is.

If you’re like me and want your tables to have a consistent, meaningful prefix tied to your own publisher, there’s a simple fix: set a Preferred Solution.

What’s a Preferred Solution?

By default, any customizations you make outside the context of a specific solution land in the Common Data Services Default Solution. The publisher on that solution has a randomly assigned prefix, so your tables end up with names like cr8a3_project instead of something clean like contoso_project.

When you set a Preferred Solution, any new objects you create — including tables created through the Dataverse MCP — automatically use the publisher and prefix from that solution. No extra configuration needed on the MCP side.

Setting Your Preferred Solution

You can set your preferred solution in two ways.

Option 1: When Creating a New Solution

When you create a new solution, there’s a checkbox right on the creation form to Set as your preferred solution. Just check it and you’re good to go.

Option 2: From the Solutions List

If you already have a solution you’d like to use, go to the Solutions area in Power Apps, select your unmanaged solution, and click Set preferred solution on the command bar.

Once it’s set, you’ll see a preferred solution indicator in the Solutions area confirming which solution is active.

That’s It

Now when you create tables through the Dataverse MCP, they’ll automatically get the prefix from your preferred solution’s publisher. No need to rename anything after the fact or manually move tables between solutions.

For more details on preferred solutions and solution publishers, check out these Microsoft Learn articles:

Give it a try — it’s a small change that keeps your environment clean from the start!

Fixing PAC CLI “non-recoverable error” in GCC High and DoD by enabling telemetry

2026-02-01T14:47:30.443-05:00

If you use the Power Platform CLI (PAC CLI) in GCC High or DoD environments, you may run into a sudden crash that looks like this:

Sorry, the app encountered a non-recoverable error and will need to terminate. The exception details have been captured and will be forwarded to the development team, if telemetry has been enabled. Session Id: 6e212345-df11-22a7-b633-42380ed22acd, Exception Type: System.NullReferenceException

It’s especially confusing because the message mentions telemetry like it’s only about reporting diagnostics, but in the scenario I hit, the crash was tied to telemetry being disabled.

This post explains the pattern, how to confirm if you’re affected, and the quick fix.

What I observed

In my environment:

PAC CLI 1.34.1 worked reliably
The crash started happening on versions after 1.34.1
This happened in GCC High (and is relevant for DoD tenants as well)
I first noticed it while downloading and uploading Power Pages (Portals) sites, but I later found it can happen with other commands too. The portal commands were just the first place it showed up consistently.

The surprising trigger: telemetry disabled

If PAC CLI telemetry is disabled, some commands can hit a failure path that results in the CLI terminating with a System.NullReferenceException and the “non-recoverable error” message.

So while the message says telemetry would help forward the exception details, in this case telemetry being disabled was part of what caused the crash behavior.

Check your telemetry status

Run:

pac telemetry status

If it reports telemetry is disabled, you’re a prime candidate for this issue (especially if you’re on a PAC CLI version newer than 1.34.1 in GCC High or DoD).

Fix: enable telemetry and retry

Enable telemetry:

pac telemetry enabled

Then re-run the command that was failing.

In my testing, simply enabling telemetry made the same commands that were terminating start working normally.

Why the error is misleading

The message frames telemetry as “only” a mechanism to forward exception details. In practice, what I experienced is:

With telemetry disabled, the CLI can crash with a null reference exception
With telemetry enabled, the crash path doesn’t occur (or is handled correctly), and the command proceeds

So the wording is not wrong, but it doesn’t tell you that telemetry configuration may be directly related to the failure.

Quick troubleshooting checklist

If you see the “non-recoverable error” + System.NullReferenceException in GCC High or DoD:

Confirm your PAC CLI version (and note if you upgraded recently)
Check telemetry status
```
pac telemetry status
```
If disabled, enable it
```
pac telemetry enabled
```
Retry the exact command that failed

Wrap-up

If PAC CLI started crashing in GCC High or DoD with a “non-recoverable error” and System.NullReferenceException, check whether telemetry is disabled. Enabling telemetry is a simple change that can get you unstuck, even though the message makes it sound like telemetry only affects error reporting.

Bridging Clouds: Secure Pipelines from Azure DevOps to GCC High

2025-08-25T17:53:00.002-04:00

Introduction

The goal of this setup is to allow an Azure DevOps pipeline running in the Commercial cloud to move files (e.g., build artifacts, documentation, or deployment packages) into a Storage Account in GCC High. Because these are two different clouds, the connection must be established carefully to remain secure, compliant, and tenant-scoped.

Although this guide is written for Commercial → GCC High, the same approach can also be used for file transfers between Commercial environments or even across tenants within Commercial Azure. By relying on federated credentials instead of secrets, the process ensures secure, governed transfers that honor existing Azure AD (Entra ID) boundaries.

To achieve this, we use an Azure User Assigned Managed Identity (UAMI) in the target environment, link it with Workload Identity Federation from Azure DevOps (Commercial), and grant it the minimum necessary roles. This way, files can flow between environments without storing long-lived secrets or keys.

Key Placeholders to Fill In

This is the list of the placeholder for all of the azure resources and connection string we will need during the setup process. This can be helpful when going through the instructions below.

From GCC High Azure Portal (https://portal.azure.us/)

<<gcc-subscription-id>>
<<gcc-subscription-name>>
<<gcc-resource-group-name>>
<<gcc-storage-account-name>>
<<gcc-storage-container-name>>
<<gcc-container-name>>
<<gcc-managed-identity-name>>
<<gcc-managed-identity-client-id>>
<<gcc-federated-credential-name>>
<<gcc-tenant-id>>

From Azure DevOps / Commercial Azure Portal (https://portal.azure.com/)

<<commercial-issuer-url-from-ADO>>
<<commercial-subject-id-from-ADO>>
<<commercial-service-connection-name>>

Step 1 — Create a Storage Account in GCC High

In the GCC High Azure Portal, create a new Storage Account.
- Resource Group: <<gcc-resource-group-name>>
- Type: Blob Storage (Gen2)
- Name: <<gcc-storage-account-name>>
Note the subscription ID (<<gcc-subscription-id>>) and resource group name for later use.
Create a container within the storage acocunt.
- Name: <>

Step 2 — Create a Managed Identity in GCC High

In the GCC High Azure Portal, within the same Resource Group (<<gcc-resource-group-name>>), create a User Assigned Managed Identity (UAMI).
- Name: <<gcc-managed-identity-name>>
Copy the Client ID (<<gcc-managed-identity-client-id>>) and Object ID.

Step 3 — Assign Roles to the Managed Identity

At the Subscription level (<<gcc-subscription-id>>):
- Go to Access Control (IAM) → Add Role Assignment
- Role: Reader
- Assign to: <<gcc-managed-identity-name>>
At the Storage Account level (<<gcc-storage-account-name>> in <<gcc-resource-group-name>>):
- Add Role Assignment
- Role: Storage Blob Data Contributor
- Assign to: <<gcc-managed-identity-name>>

Step 4 — Begin Service Connection Setup in Azure DevOps (Commercial)

In the Commercial Azure Portal, go to Azure DevOps → Project Settings → Service Connections.
Create a new Azure Resource Manager service connection.
Configure the wizard as follows:
- Identity Type: App registration or Managed Identity (Manual)
- Credential: Workload Identity Federation
- Environment: Azure US Government
- Directory Tenant ID: <<gcc-tenant-id>> (from GCC High tenant)

Step 5 — Configure Federated Credentials (Exchange Between ADO & GCC High)

From the ADO wizard (Commercial), copy the:
- Issuer URL → <<commercial-issuer-url-from-ADO>>
- Subject Identifier → <<commercial-subject-id-from-ADO>>
In the GCC High Azure Portal → Managed Identity (<<gcc-managed-identity-name>> in <<gcc-resource-group-name>>):
- Go to Federated Credentials → Add Credential
- Scenario: Other
- Issuer: <<commercial-issuer-url-from-ADO>>
- Subject Identifier: <<commercial-subject-id-from-ADO>>
- Name: <<gcc-federated-credential-name>>
- Audience: keep default (api://AzureADTokenExchange)

Step 6 — Complete Service Connection in ADO (Commercial)

Back in ADO’s Service Connection Wizard:
- Scope level: Subscription
- Subscription ID: <<gcc-subscription-id>>
- Subscription Name: <<gcc-subscription-name>>
- Authentication → Client ID: <<gcc-managed-identity-client-id>>
Important: Uncheck Grant access permissions to all pipelines.
Click Verify and Save.

Step 6a — Approve pipeline to use the service connection

In ADO (Commercial) go to Project Settings → Service connections → <<commercial-service-connection-name>>.
Open Security.
Under User permissions, explicitly grant access to the project and/or the specific pipeline that will run this connection.
- Option A (recommended): Add the build pipeline or its build service account (e.g., Project Name Build Service (Organization)), set Use permission to Allow.
- Option B: Add the Project Contributors group if you want broader use.
Save.
Alternative: If you try to run the pipeline without this step, ADO will pause with “Needs approval.” You can approve it from the run page, but configuring Security first is cleaner.

Step 7 — Test the Connection

In ADO (Commercial), create a new repository with a single file README.md.
Create a new Pipeline pointing to this repo.
- Agent pool: windows-latest

Add an Azure File Copy task to the pipeline.

Source Path: README.md
Azure Subscription: <<commercial-service-connection-name>>
Destination: Azure Blob Storage
Storage Account: <<gcc-storage-account-name>> (in <<gcc-resource-group-name>>)

Container: <<gcc-container-name>>

Here is an example pipline. Make sure to replace the placeholders in the AzureFileCopy@6 tasks based on the names you used earlier

# Example pipeline to copy README.MD file
 trigger:
 - master
 
 pool:
   vmImage: windows-latest
 
 steps:
 - script: echo Lets copy a file!
   displayName: 'Run a one-line script'
 
 - task: AzureFileCopy@6
   inputs:
     SourcePath: 'README.MD'
     azureSubscription: '<<commercial-service-connection-name>>'
     Destination: 'AzureBlob'
     storage: '<<gcc-storage-account-name>>'
     ContainerName: '<<gcc-storage-container-name>>'

Run the pipeline.
- If you did not complete Step 6a, approve the service connection usage when prompted.
- ✅ Verify README.md appears in the GCC High Storage Blob container.

Locking Down a Logic App (Consumption) with OAuth for Calls from Dataverse Plug-ins using Managed Identity

2025-08-22T13:01:00.001-04:00

Why I did this

I’m using managed identity to let a Dataverse plug-in call Azure resources without storing secrets. One of those calls hits a Logic App (Consumption) via the When an HTTP request is received trigger. I wanted to ensure the workflow can only be invoked by callers from my tenant using OAuth—no shared access signature (SAS) keys. (If you’re on Logic Apps Standard, you’d typically use App Service “Easy Auth”.)

Microsoft’s docs say you can require OAuth and (critically) you must disable SAS for request triggers in Consumption, otherwise a valid SAS bypasses OAuth. The official instructions work, but I found a simpler way to flip the SAS switch directly in code view.

What I changed

1) Disable SAS for the HTTP trigger (Consumption only)

Open the Logic App (Consumption) in the Azure portal.
Go to Development Tools ➜ Logic app code view.
In the workflow JSON, add the following sibling to "parameters" (top level) and Save:

"accessControl": {
  "triggers": {
    "sasAuthenticationPolicy": {
      "state": "Disabled"
    }
  }
}

This disables SAS so OAuth can’t be bypassed. (This is equivalent to what the docs recommend; adding it here is the fastest way.)

⚠️ Important:

After you save, the accessControl snippet will not appear again the next time you open code view. The setting still applies, it’s just hidden.
If you try to reapply the JSON snippet again later, it will delete your existing OAuth profile, and you’ll need to recreate it from scratch.

Tip: If you automate deployments, include this in your ARM/Bicep/Template spec rather than patching after the fact.

2) Add an OAuth policy on the Logic App

In the Logic App, go to Authorization.
Click + Add policy ➜ OAuth 2.0.
Enter:
- Issuer (iss): use your tenant issuer, e.g.
  https://sts.windows.net/<tenant-guid>/ (v1) or
  https://login.microsoftonline.com/<tenant-id>/v2.0 (v2)
- Audience (aud): the resource your token targets. In my case, I used https://management.azure.com.
Save.

The OAuth policy validates the token’s iss and aud claims on inbound calls to the request trigger.

Narrowing Down the OAuth To Specific Managed Identity

If you want to ensure that only a specific managed identity can access this logic app you can additional add a custom claim onto the OAuth profile. To do this add a custom claim in the OAuth Profile. Set the claim name as appid and set the value to the id of your managed identity. This will ensure that only that managed identity can call this Logic App. You can add multiple OAuth profiles to allow for more managed identities if you want.

What broke (and how I fixed it)

On first run I got issuer or audience mismatch errors. My Dataverse plug-in was acquiring a token whose issuer looked like:

https://sts.windows.net/<tenant-guid>/ (AAD v1 style)

…but I had configured the Logic App policy with:

https://login.microsoftonline.com/<tenant-id>/ (different issuer format)

To debug, I had my plug-in log the raw access token, then pasted it into https://jwt.ms to inspect the claims. I updated the Logic App policy to use the exact issuer from the token, and everything worked.

Quick refresher:

Issuer (iss) must match exactly, including trailing slash and version (v1 vs v2.0).

Audience (aud) must match the resource you requested when you got the token (e.g., https://management.azure.com for ARM).

Minimal plug-in tracing snippet (to see the token)

When you call your Logic App from the plug-in, capture and trace the bearer token you’re sending. For example:

// inside Execute(IServiceProvider serviceProvider)
var context = (IPluginExecutionContext)serviceProvider.GetService(typeof(IPluginExecutionContext));
var tracing = (ITracingService)serviceProvider.GetService(typeof(ITracingService));

// Acquire the token with managed identity
string accessToken = context.ManagedIdentityService.AcquireToken("https://management.azure.com");

// Output the full token (needed for jwt.ms inspection)
tracing.Trace("AccessToken: {0}", accessToken);

// …then send HTTP request with Authorization: Bearer {accessToken}

⚠️ Note: Output the entire token (not truncated) if you want to paste it into jwt.ms for inspection of iss and aud. Also make sure to remove this tracing before deployment.

References

Passing Pipeline Trigger Time to Data Flows in Azure Data Factory: Use Strings, Not Timestamps!

2025-08-18T16:19:00.002-04:00

When working with Azure Data Factory (ADF) and the Dataverse connector, passing the pipeline trigger time into a Data Flow can be trickier than expected.

The Scenario

You want to pass the pipeline’s trigger time—using the @pipeline().TriggerTime system variable—into a Data Flow. This is often needed for auditing, filtering, or other time-based logic.

The catch? You’re using Dataverse, which communicates over the Web API and handles datetime values as strings.

The Common Mistake

In Azure Data Factory, you might instinctively define the Data Flow parameter as a timestamp or date type. But ADF doesn’t have a dedicated datetime type—only date and timestamp. So you choose one of those, thinking it aligns with your goal.

Then you hit an error.
And to make matters worse, the error message doesn’t clearly explain the real issue—it can be vague or misleading, which only adds to the confusion. This tripped me up for a while, as I assumed the problem was elsewhere.

The Solution

Define the Data Flow parameter as a string.
In the pipeline, pass the @pipeline().TriggerTime system variable directly into this parameter using a pipeline expression.

This small change ensures compatibility with the Dataverse connector and avoids the cryptic conversion error.

Lesson Learned

Even though it’s tempting to use date or timestamp types when dealing with datetime values in ADF, the correct approach for Dataverse is to use strings. It aligns with how the Web API expects the data and helps you avoid hard-to-decipher errors.

Power Query: Driftless Merges using Table.Buffer

2025-08-11T13:36:00.002-04:00

What happened

Recently I was working on data where I needed to pick one best row per group, then merge that result with a lookup table. Here’s the head-scratcher I hit: the pick looked right in preview, but after the merge some groups showed different rows, like the merge had used the pre-pick data. What was happening is that Power Query re-evaluated and re-ordered things during the merge, which changed which row got selected. The fix was to freeze the picked result with Table.Buffer right after the pick so the merge used exactly those rows. I also made the lookup one row per key to avoid duplicate expands. After that, everything stayed stable on refresh.

Why and how `Table.Buffer` works

Why the drift happens

Power Query is lazy. It does not materialize intermediate steps until needed.
A Merge can push work back to the source (folding). That re-evaluation can change row order.
If your “pick one” depends on order, the selected row can change during the Merge.

What Table.Buffer does

Takes a snapshot of the current table and caches it in memory.
Preserves the row order you produced before buffering.
Acts like a barrier so later steps cannot change that selection.

Where to place it

// After you have exactly the rows and columns you want to keep
KeptCols   = Table.SelectColumns(YourPickedTable, {"GroupKey","ChosenCol1","ChosenCol2","TieBreaker"});
LeftFrozen = Table.Buffer(KeptCols);

// Then do your join
RightOne   = Table.Distinct(RightTable, {"RightKey"});
Merged     = Table.NestedJoin(LeftFrozen, {"GroupKey"}, RightOne, {"RightKey"}, "Right", JoinKind.LeftOuter);
Final      = Table.ExpandTableColumn(Merged, "Right", {"R1","R2"}, {"R1","R2"});

Use it when

You pick or rank one row per group, then Merge or Append
Post-merge results do not match the pre-merge preview
You branch the same picked result to multiple places and need consistency

Cautions

Holds the data in memory. Avoid on very large tables.
Steps after the buffer often will not fold back to the source. Use it only where correctness matters.

Demonstration of issue

Here is a side-by-side Power Query comparison showing why buffering matters. Both queries “pick” 4 rows before the merge. After the merge, the unbuffered query returns 5 rows and shows a changed pick, while the buffered query returns the expected 4 rows that match the pick.

Reproduce the issue and the fix

Create the three small tables with Enter data

In Power Query, use Home > Enter data, paste each block, check Use first row as headers, click OK. Name them Loans, StatusReference, and BranchDirectory.

Loans

BranchName,Item Type,State,CopyAddedDate,State Change Date,CopyId
Downtown Branch,Circulating,Checked Out,2024-01-10,2024-01-12,CP001
Downtown Branch,Circulating,Available,2024-02-01,2024-02-15,CP002
Uptown Branch,Circulating,Reserved,,2024-03-01,CP010
Uptown Branch,Circulating,Available,2024-04-01,2024-04-10,CP011
West Branch,Circulating,Checked Out,2023-12-01,2023-12-05,CP020
West Branch,Circulating,Lost,2022-05-01,2024-05-10,CP021
Reference Branch,Reference,Available,2024-06-01,2024-06-10,CP030
South Branch,Circulating,Reserved,,2024-07-01,CP040

StatusReference

State,Status,StatusOrder
Available,Available,3
Reserved,Reserved,2
Checked Out,Checked Out,1
Lost,Lost,0

BranchDirectory
(contains a duplicate on purpose)

branch_name,branch_id,branch_current_status,branch_inventory_date
Downtown Branch,11111111-1111-1111-1111-111111111111,Available,2024-02-15
Uptown Branch,22222222-2222-2222-2222-222222222222,Available,2024-04-10
Uptown Branch,22222222-2222-2222-2222-222222222223,Available,2024-04-10
West Branch,33333333-3333-3333-3333-333333333333,Checked Out,2023-12-05
South Branch,55555555-5555-5555-5555-555555555555,Reserved,

Query 1: pick per group, then merge (unbuffered)

let
    Source = Loans,

    Types = Table.TransformColumnTypes(Source, {
        {"BranchName", type text}, {"Item Type", type text}, {"State", type text},
        {"CopyAddedDate", type date}, {"State Change Date", type date}, {"CopyId", type text}}),

    Circulating = Table.SelectRows(Types, each [Item Type] = "Circulating"),

    JoinStatus = Table.NestedJoin(Circulating, {"State"}, StatusReference, {"State"}, "StatusReference", JoinKind.LeftOuter),
    ExpandStat = Table.ExpandTableColumn(JoinStatus, "StatusReference", {"Status","StatusOrder"}, {"Status","StatusOrder"}),

    AddPriority   = Table.AddColumn(ExpandStat, "Priority", each if [Status] = "Available" then 1 else 0, Int64.Type),
    SortedForPick = Table.Sort(AddPriority, {{"BranchName", Order.Ascending}, {"Priority", Order.Descending}, {"StatusOrder", Order.Descending}, {"State Change Date", Order.Descending}}),

    GroupPick  = Table.Group(SortedForPick, {"BranchName"}, {{"Pick", each Table.FirstN(_, 1), type table}}),
    ExpandPick = Table.ExpandTableColumn(GroupPick, "Pick", {"Status","CopyAddedDate","State Change Date"}, {"Status","Date","State Change Date"}),

    MergeBranch  = Table.NestedJoin(ExpandPick, {"BranchName"}, BranchDirectory, {"branch_name"}, "BranchDirectory", JoinKind.LeftOuter),
    ExpandBranch = Table.ExpandTableColumn(MergeBranch, "BranchDirectory", {"branch_id","branch_current_status","branch_inventory_date"}, {"branch_id","branch_current_status","branch_inventory_date"}),

    Final = Table.ReorderColumns(ExpandBranch, {"BranchName","Status","Date","State Change Date","branch_id","branch_current_status","branch_inventory_date"})
in
    Final

What you may see

Some picked rows change after the merge
Duplicate rows for a key that appears twice in the right table

Query 2: same logic with `Table.Buffer` to freeze the pick

let
    Source = Loans,

    Types = Table.TransformColumnTypes(Source, {
        {"BranchName", type text}, {"Item Type", type text}, {"State", type text},
        {"CopyAddedDate", type date}, {"State Change Date", type date}, {"CopyId", type text}}),

    Circulating = Table.SelectRows(Types, each [Item Type] = "Circulating"),

    JoinStatus = Table.NestedJoin(Circulating, {"State"}, StatusReference, {"State"}, "StatusReference", JoinKind.LeftOuter),
    ExpandStat = Table.ExpandTableColumn(JoinStatus, "StatusReference", {"Status","StatusOrder"}, {"Status","StatusOrder"}),

    AddPriority   = Table.AddColumn(ExpandStat, "Priority", each if [Status] = "Available" then 1 else 0, Int64.Type),
    SortedForPick = Table.Sort(AddPriority, {{"BranchName", Order.Ascending}, {"Priority", Order.Descending}, {"StatusOrder", Order.Descending}, {"State Change Date", Order.Descending}}),

    GroupPick  = Table.Group(SortedForPick, {"BranchName"}, {{"Pick", each Table.FirstN(_, 1), type table}}),
    ExpandPick = Table.ExpandTableColumn(GroupPick, "Pick", {"Status","CopyAddedDate","State Change Date"}, {"Status","Date","State Change Date"}),

    // Freeze the left side before merging
    KeptCols = Table.SelectColumns(ExpandPick, {"BranchName","Status","Date","State Change Date"}),
    Buffered = Table.Buffer(KeptCols),

    // Ensure the right side is one row per key
    BranchOne = Table.Distinct(BranchDirectory, {"branch_name"}),

    MergeBranch  = Table.NestedJoin(Buffered, {"BranchName"}, BranchOne, {"branch_name"}, "BranchDirectory", JoinKind.LeftOuter),
    ExpandBranch = Table.ExpandTableColumn(MergeBranch, "BranchDirectory", {"branch_id","branch_current_status","branch_inventory_date"}, {"branch_id","branch_current_status","branch_inventory_date"}),

    Final = Table.ReorderColumns(ExpandBranch, {"BranchName","Status","Date","State Change Date","branch_id","branch_current_status","branch_inventory_date"})
in
    Final

Expected outcome

Picked rows remain the same before and after the merge
No duplicate rows after expand when the right side is made distinct

How to View DLP Policies Applied to a Power Platform Environment

2025-05-07T18:29:00.003-04:00

To quickly see which Data Loss Prevention (DLP) policies are applied to a specific Power Platform environment, you can use a direct URL. This article shows you how to:

Find your environment ID
Use the DLP filter URL
View results in both the old and new Power Platform Admin Center interfaces

Step 1: Locate Your Environment ID

To get your environment ID:

Go to the Power Platform Admin Center.
Click Environments in the left-hand menu.
Select the environment you want to inspect.
Under the Details or Overview section, locate the Environment ID (a GUID string).

Step 2: Use the URL to View Applied DLP Policies

There are two URL formats, depending on which version of the Admin Center you’re using.

✅ New Admin Center:

https://admin.powerplatform.microsoft.com/security/dataprotection/dlp/environmentFilter/{environmentId}

🕹️ Old Admin Center:

https://admin.powerplatform.microsoft.com/dlp/environmentFilter/{environmentId}

Just replace {environmentId} with the actual ID from Step 1.

Example:

https://admin.powerplatform.microsoft.com/security/dataprotection/dlp/environmentFilter/f4834a12-5388-4f41-b755-cce6a52d38a0

These URLs take you directly to the list of DLP policies scoped to that environment, saving you time from clicking into each policy manually.

Summary

Using this method, you can:

Instantly view applied DLP policies per environment
Bypass manual filtering through policies
Easily audit security coverage

Pro tip: Save the URL format with a placeholder so you can quickly reuse it for other environments.

Harnessing Host Form Data with PCF Controls in Model-Driven Applications

2025-04-24T09:11:00.001-04:00

Introduction

This tutorial delves into integrating PowerApps Component Framework (PCF) controls with host form data within Microsoft Power Platform’s model-driven apps. This article will guide you through the necessary scripting to expose and consume formContext and globalContext from a custom table called new_Competitor. Aimed at enhancing both custom and Microsoft Form Component PCF controls, this approach ensures dynamic interactions with the host form data.

Disclaimer: It’s important to note that there are various methods to retrieve data within PCF controls, including the use of WebAPI. While WebAPI provides a versatile way to access data across different entities and contexts, the approach described in this tutorial focuses on directly integrating with host form data, which can be particularly beneficial in specific use cases where immediate context is crucial. This method allows for real-time data interactions that are essential for certain scenarios, providing a streamlined integration that may not always be achievable through WebAPI alone.

Setting up JavaScript on Host Form

To enable a PCF control to access data from its hosting form, JavaScript needs to be implemented on the host form. This will capture and expose crucial context data (formContext and globalContext) to the PCF control through global variables.

JavaScript Code Implementation

Here is code you can utilize within a webresource which has been loaded into your host form. This example loads the formContext and globalContext but you can load additional information here as you need.

var NEWCompetitor = window.NEWCompetitor || {};

(function () {
    "use strict";

    this.onLoad = function (executionContext) {
        var formContext = executionContext.getFormContext();
        var globalContext = Xrm.Utility.getGlobalContext();
        this.copyDataToWindowForPCF(globalContext, formContext);
    };

    this.copyDataToWindowForPCF = function (globalContext, formContext) {
        if (typeof parent['NEWCompetitorInfo'] === "undefined") {
            parent.NEWCompetitorInfo = { __namespace: true };
        }

        parent.NEWCompetitorInfo.formContext = formContext;
        parent.NEWCompetitorInfo.globalContext = globalContext;
    };

}).call(NEWCompetitor);

Deployment and Configuration

Add the Script: Include the JavaScript as a web resource linked to the new_Competitor table form.
Modify the OnLoad Event: Configure the entity form’s OnLoad event to trigger the onLoad function.

Getting Data from Host Form in Custom PCF Control

Once the host form data is made available globally, your custom PCF can access that information using window.NEWCompetitorInfo.formContext.

Implementing the Render Component

I typically use the renderComponent function in PCF to make sure that the host data is loaded before the overall PCF is loaded, so that I make sure I can access this from everywhere within the PCF. Within the renderComponent function is also where you might call the render of a React component.


private renderComponent(){
		var self = this;
		//@ts-ignore for this._context.mode.contextInfo
		if (!window.NEWCompetitorInfo.formContext){			
			setTimeout(() => {self.renderComponent()}, 500);
			return;
		}		
	}

Accessing in React

If you are using a React control within your PCF you can declare the namespace for the top-level form within your typescript as an any.

import * as React from "react";
import {IInputs} from "./generated/ManifestTypes";

export interface IProps {
    pcfContext: ComponentFramework.Context<IInputs>  
}

declare global {
    interface Window {
        NEWCompetitorInfo: any
    }
}

Getting Data from Host Form in Microsoft Form Component PCF

If you are using the Form Component to load editable form into a host form you may want to access information and interact with the host form. The onLoad function running on the host form as shown earlier gets us global variables we can use here as well. These variables will enable you to call JavaScript on the form inside the component and access information from the hosing form. More information about the Form Component can be found here Edit related table records directly from another table’s main form.

Setting up JavaScript in the Child Form

To access the formContext from the host form, use a webresource and event in the form hosted within the Form Component. This script will reference the global variable provided by the host form’s script.

var NEWCompetitorContact = window.NEWCompetitorContact || {};

(function () {
    "use strict";

    this.onLoad = function (executionContext) {
		// Access the formContext from the global variable set by the host form
		if (top.NEWCompetitorInfo && top.NEWCompetitorInfo.formContext) {
		    var formContext = top.NEWCompetitorInfo.formContext;
		    
		    // Utilize formContext for further operations within the Form Component
		    // Example: Accessing data, manipulating form fields, etc.
		    var someFieldValue = formContext.getAttribute("fieldname").getValue();
		    // Additional logic to manipulate or use the form data
		}
 };

}).call(NEWCompetitorContact);

By implementing this method, the Form Component within your model-driven app can dynamically interact with the data from the host form, making it more responsive and capable of handling complex scenarios based on live data inputs.

Copy Hidden Power Automate Expressions

2025-04-14T19:44:00.003-04:00

While recently walking a colleague through Power Automate, I was reminded of one of my long-standing frustrations: how difficult it is to copy expressions once they’ve been inserted into actions like a Compose, Filter Array, or For Each. For example:

workflow()['tags']['environmentName']
item()?['logicalName']

Once fields are wrapped in certain expressions or are in certain actions, you can no longer open them in the expression popup—you or easily copy them out to reuse elsewhere. That makes it frustrating when you want to replicate logic in another flow or simply document what you’ve written. There is a copy action functionality but that copies the entire action and sometimes you just want a specific expression.

The Workaround (Until Now)

To deal with this, I used to:

Add expressions to a comment or note in the action so I could reference them later
Open DevTools and inspect the DOM to grab the full title attribute that contains the expression

But neither of these approaches felt elegant. And since I work in the public sector, I can’t use browser extensions like a typical Power Platform developer might.

Bookmarklets to the Rescue

This reminded me of the old-school technique of using bookmarklets to extract information from web apps—something I used to do to grab entity IDs in Dynamics CRM. That got me thinking: why not use a bookmarklet or browser snippet to extract expressions in Power Automate?

So I built exactly that: a JavaScript snippet that works with both the classic and new editors in Power Automate.

Requirements

This tool only works when editing flows directly at https://make.powerautomate.com
It will not work if you’re in https://make.powerapps.com, because flows open in an iframe hosted on a different domain—which introduces cross-origin browser restrictions

Where This Is Useful

This tool is great when you’re:

Migrating expressions between flows
Refactoring actions and need to reference earlier logic
Working in shared environments and need to document flows
Reviewing or debugging large flows with embedded expressions

How It Works

The script listens for clicks on expression tokens within the flow designer. Once you click a token, it pops open a dialog with:

The full expression
A “Copy” button
A “Copy & Close” button
A “Close” button
And a “Turn Off” button to disable the click handler when you’re done

It supports:

v2 (classic editor) by inspecting the title attribute on legacy token spans
v3 (new editor) by locating the tooltip element associated with the clicked field

How to Use It

Option 1: Bookmarklet

Create a new bookmark in your browser
Edit the bookmark URL and paste the following code:

javascript:(function(){const e=new URLSearchParams(window.location.search).get("v3"),t=null===e||"true"===e;function n(e){const t=document.getElementById("expression-dialog");t&&t.remove();const n=document.createElement("div");n.id="expression-dialog",n.style.cssText="position: fixed; top: 20%; left: 50%; transform: translateX(-50%); background: white; border: 2px solid #0078d4; padding: 20px; z-index: 99999; box-shadow: 0 4px 12px rgba(0,0,0,0.2); font-family: sans-serif; border-radius: 8px; min-width: 400px;",n.innerHTML=`<div style="margin-bottom: 10px; font-weight: bold;">Power Automate Expression</div><input type="text" id="expression-input" value="${e.replace(/"/g,"&quot;")}" style="width: 100%; padding: 8px; font-size: 14px;" readonly /><div style="margin-top: 10px; text-align: right;"><button id="turnoff-btn" style="margin-right: 10px;">Turn Off</button><button id="copy-btn" style="margin-right: 10px;">Copy</button><button id="close-btn" style="margin-right: 10px;">Close</button><button id="copy-close-btn">Copy & Close</button></div>`,document.body.appendChild(n);const o=document.getElementById("expression-input");o.focus(),o.select(),document.getElementById("copy-btn").onclick=()=>{navigator.clipboard.writeText(e).then(()=>{const e=document.getElementById("copy-btn");e.innerText="Copied!",setTimeout(()=>e.innerText="Copy",1500)})},document.getElementById("copy-close-btn").onclick=()=>{navigator.clipboard.writeText(e).then(()=>{n.remove()})},document.getElementById("close-btn").onclick=()=>n.remove(),document.getElementById("turnoff-btn").onclick=()=>{t?document.removeEventListener("click",r,!0):document.removeEventListener("click",c,!0),n.remove(),alert("Expression click handler turned off.")}}function c(e){"SPAN"===e.target.tagName&&(e.preventDefault(),e.stopPropagation(),e=e.target.parentElement?.parentElement,e&&e.classList.contains("msla-token")&&n(e.title||"(no title found)"))}function r(e){"DIV"===e.target.tagName&&(e=e.target.closest('span[data-automation-id^="flow-token"]'),e&&(e=e.getAttribute("aria-labelledby")||e.getAttribute("aria-describedby"),e=document.getElementById(e)?.querySelector("span")?.textContent?.trim())&&(event.preventDefault(),event.stopPropagation(),n(e)))}t?(alert("Click on any token in the new editor (v3) to extract the expression. You can keep clicking."),document.addEventListener("click",r,!0)):(alert("Click on any expression token (v2) to extract the expression. You can keep clicking."),document.addEventListener("click",c,!0))})();

Navigate to https://make.powerautomate.com, open your flow, and click the bookmark
Then click on any tokenized expression to extract it!

Option 2: DevTools Snippet

Open your flow in Power Automate (must be at make.powerautomate.com)
Press F12 to open Developer Tools
Go to the “Sources” tab
Choose the “Snippets” pane and click "New snippet"
Paste in the full script
Right-click the snippet and choose "Run"

Here is the full code:

// Combined widget for both v2 and v3 Power Automate editors
(function() {
  const urlParams = new URLSearchParams(window.location.search);
  const v3Param = urlParams.get("v3");
  const isV3 = v3Param === null || v3Param === "true";

  function handleClickV2(e) {
    if (e.target.tagName !== "SPAN") return;
    const token = e.target.parentElement?.parentElement;
    if (!token || !token.classList.contains("msla-token")) return;

    e.preventDefault();
    e.stopPropagation();

    const expression = token.title || "(no title found)";
    showDialog(expression);
  }

  function handleClickV3(e) {
    if (e.target.tagName !== "DIV") return;
    let token = e.target.closest('span[data-automation-id^="flow-token"]');
    if (!token) return;

    const describedById = e.target.getAttribute("aria-labelledby") || e.target.getAttribute("aria-describedby");
    if (!describedById) return;

    const tooltipEl = document.getElementById(describedById);
    const expression = tooltipEl?.querySelector('span')?.textContent?.trim();
    if (!expression) return;

    e.preventDefault();
    e.stopPropagation();

    showDialog(expression);
  }
  
  function showDialog(expression) {
    const existing = document.getElementById("expression-dialog");
    if (existing) existing.remove();

    const dialog = document.createElement("div");
    dialog.id = "expression-dialog";
    dialog.style.cssText = `
      position: fixed;
      top: 20%;
      left: 50%;
      transform: translateX(-50%);
      background: white;
      border: 2px solid #0078d4;
      padding: 20px;
      z-index: 99999;
      box-shadow: 0 4px 12px rgba(0,0,0,0.2);
      font-family: sans-serif;
      border-radius: 8px;
      min-width: 400px;
    `;

    dialog.innerHTML = `
      <div style="margin-bottom: 10px; font-weight: bold;">Power Automate Expression</div>
      <input type="text" id="expression-input" value="${expression.replace(/"/g, '&quot;')}" style="width: 100%; padding: 8px; font-size: 14px;" readonly />
      <div style="margin-top: 10px; text-align: right;">
        <button id="turnoff-btn" style="margin-right: 10px;">Turn Off</button>
        <button id="copy-btn" style="margin-right: 10px;">Copy</button>
        <button id="close-btn" style="margin-right: 10px;">Close</button>
        <button id="copy-close-btn">Copy & Close</button>
      </div>
    `;

    document.body.appendChild(dialog);

    const input = document.getElementById("expression-input");
    input.focus();
    input.select();

    document.getElementById("copy-btn").onclick = () => {
      navigator.clipboard.writeText(expression).then(() => {
        const btn = document.getElementById("copy-btn");
        btn.innerText = "Copied!";
        setTimeout(() => btn.innerText = "Copy", 1500);
      });
    };

    document.getElementById("copy-close-btn").onclick = () => {
      navigator.clipboard.writeText(expression).then(() => {
        dialog.remove();
      });
    };

    document.getElementById("close-btn").onclick = () => dialog.remove();

    document.getElementById("turnoff-btn").onclick = () => {
      if (isV3) {
        document.removeEventListener("click", handleClickV3, true);
      } else {
        document.removeEventListener("click", handleClickV2, true);
      }
      dialog.remove();
      alert("Expression click handler turned off.");
    };
  }

  if (isV3) {
    alert("Click on any token in the new editor (v3) to extract the expression. You can keep clicking.");
    document.addEventListener("click", handleClickV3, true);
  } else {
    alert("Click on any expression token (v2) to extract the expression. You can keep clicking.");
    document.addEventListener("click", handleClickV2, true);
  }
})();

Once active, simply click any expression token in the designer to see the popup.

Connecting to Dataverse from Azure Data Factory

2025-04-13T11:25:00.002-04:00

Introduction

Azure Data Factory (ADF) provides versatile ways to connect and interact with Dataverse. Understanding the connection options and configurations is crucial for securely and efficiently managing your data integration tasks. This guide walks through the primary methods for connecting Azure Data Factory to Dataverse, covering their benefits, best practices, and detailed walkthroughs to get you started. provides versatile ways to connect and interact with Dataverse. Understanding the connection options and configurations is crucial for securely and efficiently managing your data integration tasks. This guide walks through the primary methods for connecting Azure Data Factory to Dataverse, covering their benefits, best practices, and detailed walkthroughs to get you started.

Disclaimer: These thoughts are my own and based on my personal experience. If you have different ideas or approaches, I’d love to hear them—I’m always eager to learn more from others in the community.

Connection Types

There are three main ways to connect Azure Data Factory to Dataverse: the REST API, the built-in Dataverse connector, and the OData connector. Each method offers unique advantages depending on your use case. Personally, I prefer using the REST API because it offers the greatest amount of flexibility. While it may require some additional configuration—like setting pagination rules or custom headers—it enables capabilities that are simply not possible with the Dataverse connector, such as returning additional metadata, bypassing plugins, or impersonating users.

REST API

Connecting via the Dataverse REST API gives you detailed control over the data retrieval process, including the ability to return formatted values and metadata for lookups. More on Microsoft Learn: https://learn.microsoft.com/en-us/azure/data-factory/connector-rest?tabs=data-factory

Advantages:

Ability to invoke custom actions or functions on records, enabling powerful server-side operations within the same integration flow.
Richer data retrieval with annotations. Request Annotations
Complete control over data requests.
Returns all fields, including those with null values if you use specify those fields in the $select, this can be helpful when building projections if not all of your records contains data.
Allows setting custom headers such as CallerObjectId for impersonating users (with appropriate permissions) and MSCRM.BypassCustomPluginExecution to bypass plugins. More info: Other Headers
Supports FetchXML queries in addition to OData for advanced querying scenarios.
FetchXML Builder in XrmToolBox can be used to craft FetchXML queries and even convert them into OData format.

Considerations:

Generating the request body for POST or PATCH operations (such as updates) can be more cumbersome and error-prone compared to the built-in Dataverse connector.
Requires handling JSON transformations.
You must configure pagination rules to retrieve more than 5,000 records (to be discussed in a future article).
Ideal for complex scenarios where pipeline lookups fall short.

Dataverse Connector

The built-in Dataverse connector simplifies connectivity and basic data operations with minimal configuration. More on Microsoft Learn: https://learn.microsoft.com/en-us/azure/data-factory/connector-dynamics-crm-office-365?tabs=data-factory

Advantages:

Easy setup with minimal configuration.
Convenient for simple scenarios and basic CRUD operations.
When used in Data Flows, it automatically handles pagination and retrieves more than 5,000 records without additional configuration.

Considerations:

Limited control over annotations and formatted data.
Fields with null values are not returned, making it difficult to detect missing data in pipeline activities.

OData

OData offers a standardized protocol for interacting with Dataverse, beneficial for interoperability and standardized queries. More on Microsoft Learn: https://learn.microsoft.com/en-us/azure/data-factory/connector-odata

Advantages:

Standardized query syntax since it is using the REST API.
Interoperable with multiple tools.

Considerations:

Limited functionality compared to the REST API.
Does not support Managed Identity authentication, limiting secure integration options.

Security Considerations

Each of the connector types discussed above supports different authentication methods. The Dataverse connector and OData connector primarily rely on Azure Active Directory-based service principal authentication. The REST API offers greater flexibility by supporting both service principal and Managed Identity configurations, and it allows you to include custom headers for additional control.

In this article, I focus on using Managed Identity, as it is my preferred method for securing connections. Managed Identity provides credential-free authentication, reduces the risk of secrets exposure, and integrates cleanly with Azure resources. The REST API allows you to use either a System-assigned or a User-assigned Managed Identity, giving flexibility based on your identity management preferences. The Dataverse connector, on the other hand, only supports User-assigned Managed Identities. I typically choose User-assigned identities regardless, as they provide clearer control and traceability across enterprise environments. This article will focus on how to configure and assign a User-assigned identity for this integration. as it is my preferred method for securing connections.

Managed Identities

Managed identities offer secure, credential-free access to Dataverse from ADF.

User-assigned Managed Identity: Preferred due to clear management and reusability.
Ensure appropriate Dataverse security roles are assigned directly.

Walkthrough: Creating and Assigning a Managed Identity

In the Azure Portal, create a new User-assigned Managed Identity.
Navigate to your Azure Data Factory resource.
Under Settings, select Managed identities and then click + Add user-assigned managed identity.
Select the newly created identity and add it to your ADF instance.
Go to Azure Active Directory and assign the identity to your Dataverse environment if needed.
In the Power Platform admin center, assign the identity a security role (e.g., System Administrator or a custom role with required privileges).

Security Roles in Dataverse

Assigning security roles in Dataverse is essential to ensure that your ADF-managed identities can access and perform the operations they require.

Walkthrough: Assigning a Security Role to a Managed Identity

Go to the Power Platform Admin Center and open the Environments section.
Click on the environment you want to assign access to.
Select the S2S Apps tab to manage Server-to-Server (S2S) app users.
Click + Add an app user to begin adding your Managed Identity.
Important: When searching for your Managed Identity, search by Object ID, not the display name, as it may not appear otherwise.
Select a Business Unit if prompted.
Choose a Security Role to grant the necessary permissions.

Although the screenshot example shows assigning the System Administrator role, I typically recommend creating a tailored security role that grants only the permissions needed for your ADF activities. This promotes better security hygiene and minimizes risk.

Configure roles precisely to maintain a strong security posture.

Using the Credentials in Azure Data Factory

Once your User-assigned Managed Identity has been created and assigned the proper security role in Dataverse, the next step is to use it within Azure Data Factory.

Walkthrough: Connecting with Managed Identity in ADF

In Azure Data Factory, go to Manage > Linked services and click + New to create a new connection.
Select your connector type (e.g., REST or Dataverse) and begin configuring the connection.
When prompted for authentication, choose Managed Identity and click + New to create a credential.
Select User-assigned managed identity from the dropdown and pick the identity previously created and assigned roles.

With these steps, your ADF pipelines and data flows will be able to securely access Dataverse using the configured Managed Identity.

Best Practices

Choose REST API for detailed, annotation-rich data access and complex transformations, especially when you need access to all fields including nulls.
Opt for Dataverse Connector for straightforward data integration scenarios and automatic pagination in Data Flows.

Conclusion

Selecting the right connection method and properly configuring security ensures efficient, secure, and robust data integrations between Azure Data Factory and Dataverse.

This article includes detailed walkthroughs to guide you through setting up your Managed Identity and assigning security roles in Dataverse.

Next Steps:
Stay tuned for hands-on examples and step-by-step configurations for each connection method.

Have questions or insights? Drop your comments below and let’s discuss!

Fixing Temperature Fluctuations on the Creality CR-6 SE with PID Tuning

2025-04-12T20:39:00.001-04:00

After installing a new hotend on my Creality CR-6 SE, I ran into a frustrating issue: when printing above 210°C, the hotend temperature would swing wildly—fluctuating by 5–10°C. The printer wouldn’t even start the print due to this instability.

🧠 The Fix: PID Tuning (Thanks to Community Firmware)

This issue led me to discover PID tuning, a way to calibrate how the printer regulates temperature using smarter controls. Out of the box, Creality’s firmware doesn’t always support this—but I’m running the excellent CR-6 SE Community Firmware based on Marlin, which includes PID autotuning support via G-code commands.

🔧 How to Tune the Hotend Using OctoPrint

Open the Terminal tab in OctoPrint.
Turn on the cooling fan before tuning for accuracy, if you typically run the fan during your prints:
```
M106 S255
```
Run PID autotune at a typical print temp (I used 215°C with 10 cycles):
```
M303 E0 S215 C10
```
After sending the M303 command, you’ll see a series of output logs in the terminal as the firmware performs multiple test cycles. Each cycle includes temperature data and candidate PID values.

Look for a section similar to this near the end of the output:
```
Recv:  bias: 137 d: 117 min: 208.7500 max: 222.1429 Ku: 22.2460 Tu: 30.7830
Recv:  Classic PID
Recv:  Kp: 13.3476 Ki: 0.8672 Kd: 51.3600
Recv: PID Autotune finished! Put the last Kp, Ki and Kd constants from below into Configuration.h
Recv: #define DEFAULT_Kp 13.3476
Recv: #define DEFAULT_Ki 0.8672
Recv: #define DEFAULT_Kd 51.3600
```
✅ Important: Use the last set of Kp, Ki, and Kd values from the output. These are the optimized values to set in the next step.
Apply and save the values using:
```
M301 P13.3476 I0.8672 D51.3600
M500
```

This sets and saves the new hotend PID values to your printer’s EEPROM.

🔥 Want to Tune Your Heated Bed Too?

You can tune your heated bed the same way, just use E-1 instead of E0. For example:

M303 E-1 S60 C10

Then apply the values with:

M304 P30.93 I2.13 D299.02
M500

✅ Final Thoughts

Switching to the CR-6 SE Community Firmware opened the door to making this kind of fine-tuning possible. After running PID tuning, my hotend holds temperature within ±1°C, prints kick off right away, and layer consistency has improved.

🔁 Don’t forget: jot down your original PID values in case you need to roll back.

If you’re using a CR-6 SE and have upgraded your hotend—or just want tighter temperature control—I highly recommend giving PID tuning a try.

Automating Azure Storage Account IP Restrictions with Power Automate

2025-03-18T17:32:00.001-04:00

When managing Azure Storage Accounts, it’s essential to control access by restricting allowed IP addresses. Manually updating these restrictions can be cumbersome, especially when dealing with frequently changing IP ranges. To address this challenge, I developed a Power Automate custom connector that automates fetching and processing Azure IP ranges using Microsoft’s Azure IP Ranges and Service Tags JSON files.

While my initial use case focused on automating Azure IP Ranges and Service Tags, this solution can also be adapted to work with custom lists of IP addresses. Many of the actions in this connector, such as reducing CIDR blocks and generating IP rules, can be applied to any list of IP addresses. This allows users to integrate their own IP management workflows and leverage the Azure Management API to dynamically update firewall rules.

I collaborated with Chris Chin to refine this idea, ultimately simplifying the process of updating storage account firewall rules dynamically.

The Challenge

I needed to update an Azure Storage Container’s IP restrictions to allow specific Azure service IPs. The IP addresses I required were within Microsoft’s downloadable JSON file for Azure IP Ranges and Service Tags (available here). The problem?

The file name changes monthly, making it impossible to use a static link.
The storage account IP restriction rules have a maximum CIDR prefix of /30, requiring filtering.
The JSON file includes overlapping CIDR ranges, which needed optimization.
Azure Storage Accounts only support 400 IP rules (limit reference), so it’s necessary to check for and eliminate overlapping CIDRs to reduce the number of rules.

The Solution: A Power Automate Custom Connector

To automate this process, I created a Power Automate custom connector that performs the following:

Fetches the latest download link from the Microsoft website by scraping the URL dynamically.
Downloads the JSON file and extracts the IP ranges associated with specified service tags.
Filters CIDR blocks to ensure no prefixes larger than /30 are included.
Reduces redundant CIDR ranges, keeping only the most efficient set.
Generates an array of IP Rules formatted for use in Azure Management API.
Updates the Azure Storage Account’s firewall rules using an HTTP PATCH request.

How It Works

The Power Automate flow consists of the following steps:

Retrieve the latest download URL using the GetDirectDownloadUrl action.
Extract service tags and IP addresses using the GetIPAddressesByServiceTag action.
Filter CIDR ranges using CIDRReducer, ensuring only /30 or smaller prefixes are included. This action also included an output for Reduced Count, so you can check to make sure your IP addresses are less than 400 at this point.
Generate IP rules using GenerateIPRules, formatting them for Azure Storage firewall.
Compose the request body for the Azure Management API with defaultAction set to Deny and ipRules containing the allowed list.
Call the Azure Management API via an HTTP PATCH request to update the storage account settings.

Overall flow diagram generated using PowerDocu

API Call Example

To update the storage account firewall, I used the following API request:

PATCH https://management.azure.com/subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}/providers/Microsoft.Storage/storageAccounts/{storageAccountName}?api-version=2024-01-01

For GCC or GCCH, use:

PATCH https://management.usgovcloudapi.net/subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}/providers/Microsoft.Storage/storageAccounts/{storageAccountName}?api-version=2024-01-01

The authentication method used was App Registration, with the Storage Account Contributor role assigned.

Prerequisites

Before using this solution, you’ll need to set up an App Registration in Entra ID and assign it the necessary permissions on the Azure Storage Account.

1. Create an App Registration

Navigate to Entra Id in the Azure Portal.
Select App registrations > New registration.
Enter a name and select the appropriate supported account types.
Click Register.

2. Generate Client Credentials

In the App Registration, navigate to Certificates & secrets.
Click New client secret, enter a description, and set an expiration date.
Copy the generated secret value (it won’t be shown again).

3. Assign Role to the Storage Account

Navigate to your Azure Storage Account.
Go to Access Control (IAM) > Role assignments.
Click Add role assignment.
Select Storage Account Contributor.
Assign the role to your App Registration.

Once set up, this App Registration will authenticate API calls to update storage firewall rules.

Deploying the Connector in Power Platform

You can install this solution by downloading and importing the provided solution file or by manually using the PACONN or PAC CLI tools.

1. Install Using Solution File

You can directly download and import the Power Platform solution file:

Download Solution File: AzureIPAddressesCustomConnector_1_0_0_1.zip
Import into Power Automate:
1. Go to Power Apps > Solutions.
2. Click Import Solution.
3. Upload the downloaded .zip file.
4. Follow the prompts to complete the import.

2. Manually Import Using PACONN

If you prefer using the PACONN CLI tool, follow these steps:

Install the Power Platform CLI (paconn) following this guide.
Use the following command to import the connector:
```
paconn create --api-definition apiDefinition.swagger.json --icon icon.png --script script.c
```
This connector includes a code file, so you must use the --script option.

3. Manually Import Using PAC CLI

Alternatively, you can use the Power Platform CLI (PAC CLI):

Install the PAC CLI following this guide.
Use the following command:
```
pac connector create --api-definition apiDefinition.swagger.json --script-file script.c
```
The --script-file option is required since this connector includes a custom script.

👉 GitHub Repository

Conclusion

By leveraging Power Automate and a custom connector, I eliminated the need for manual updates to Azure Storage firewall rules. This solution dynamically fetches the latest IP ranges, optimizes CIDR blocks, and seamlessly updates the storage account via API—all without requiring user intervention.

Big thanks to Chris Chin for helping refine this approach!

FBI Top Ten Power Platform Connector

2025-02-24T19:29:00.004-05:00

Today’s digital landscape offers unprecedented opportunities to leverage technology in the service of public safety. The FBI’s open API, a comprehensive source of data on the nation’s most wanted individuals and significant art crimes, now seamlessly integrates into the Power Platform thanks to the FBI Most Wanted Custom Connector. This innovation enables low-code developers across federal agencies to quickly and easily repurpose critical law enforcement data for the greater good.

Inspiration Behind the Connector

The inception of the FBI Most Wanted Custom Connector was driven by the untapped potential within the FBI’s publicly available data. Recognizing the wealth of information housed on the FBI’s Top Ten Most Wanted list and the broader FBI’s Most Wanted API, the development of this connector was aimed at unlocking this data trove. By facilitating streamlined access to these resources through the Power Platform, the connector is designed to empower developers and agencies to build solutions that enhance public safety and awareness. This initiative not only simplifies the integration of critical law enforcement data into various applications but also supports the wider goal of keeping communities informed and safe.

Operations Overview:

List Recent Wanted: Fetches the latest list of individuals deemed most wanted by the FBI. This operation is perfect for applications or flows designed to provide timely updates on significant fugitives.
Get Wanted Person Details: Allows for retrieving detailed information on a specific individual from the most wanted list. By supplying a unique identifier (UID), users can access in-depth profiles, including criminal charges, last known locations, and available rewards.
List Art Crimes: Accesses information on stolen art and artifacts, highlighting cases of significant cultural and historical value. This operation can be used to raise awareness about art theft and assist in recovery efforts.
Get Art Crime Details: Similar to the operation for individuals, this allows for detailed exploration of a specific art crime, providing descriptions, images, and details about the theft.

Demonstrating the Connector’s Capabilities

To illustrate the practical use of the connector, a sample PowerApp and a flow have been developed:

Sample PowerApp

This application allows users to search and access detailed profiles of the FBI’s most wanted, designed to facilitate public engagement and support law enforcement efforts.

Following the illustrative image in the article, let’s delve into how to effectively leverage the app with the FBI Most Wanted Custom Connector to display the top 10 most wanted individuals. By integrating real-time data directly into your Power App, you can create a dynamic and informative resource. Here’s a step-by-step guide to setting up your app:

Leveraging the FBI Most Wanted Custom Connector in Your App

Add the Data Source:
- In the canvas app editor, navigate to the data table.
- Click the ‘Add data’ button and search for “FBI Most Wanted.”
- Select the FBI Most Wanted Custom Connector to add it as a data source to your app.
Configure the App to Display the Top 10:
- Access the properties for the app and locate the formulas property.
- Enter the following formula to retrieve the top 10 most wanted individuals based on the ‘ten’ poster classification:
```
Top10 = FBIMostWanted.ListWanted({poster_classification: "ten"}).items;
```
- Adjust this formula as needed to match your specific display preferences.
Set Up a Gallery:
- Add a gallery to your app and set its items property to Top10.
- This gallery will dynamically display the top 10 most wanted individuals, pulling the latest data from the FBI’s database.
Display Individual Details:
- To add an image for each individual within the gallery, use the following expression:
```
First(ThisItem.images).thumb
```
  This expression fetches the thumbnail image from the available images for each person.
- To display the person’s name, add a text box to the gallery and set its text property to:
```
ThisItem.title
```
  This will show the title or name associated with each wanted individual.

By following these instructions, you can create a powerful application that not only informs users about the FBI’s top 10 most wanted individuals but also keeps the information current with daily updates. This app serves as a valuable tool for raising awareness and aiding in the identification and reporting of suspects, showcasing the power of integrating real-time law enforcement data into custom applications.

Sample Flow

A scheduled daily flow designed to email users about the current FBI Top 10 Most Wanted. This automation ensures recipients are kept informed with the latest updates, directly contributing to heightened awareness and community safety.

Conclusion

The Power Platform Custom Connector for the FBI’s Most Wanted represents a significant step forward in the use of technology for public safety. By providing easy access to vital information, it enables a broad spectrum of developers to create applications that not only inform the public but also support the vital work of law enforcement agencies. It stands as a testament to the power of technology to make a real-world impact on safety and justice.

How to Show Notes (Annotations) on a Model-Driven App Dashboard

2025-02-19T12:18:00.001-05:00

Model-driven apps in Power Apps allow users to create dashboards that display key data from different entities. By default, dashboards do not provide an easy way to display Notes (Annotations). However, using a combination of dashboard customization and XrmToolBox, you can modify a dashboard to include a list of Notes. This article provides a step-by-step guide to achieving this.

Step 1: Create a New Dashboard

Navigate to the Maker Portal.
Create a new dashboard.
When adding components to the dashboard, insert a List for any entity (e.g., Accounts). The selected entity does not matter, as we will modify it later to display Notes.

Step 2: Obtain the Notes View ID

In the Maker Portal, navigate to an existing View for the Notes (Annotations) entity or create a new custom view.
Once the view is open, copy the View ID as it will be needed later.

Step 3: Install XrmToolBox and FormXml Manager Plugin

Download and install XrmToolBox from https://www.xrmtoolbox.com/.
Install the FormXml Manager Plugin.
Connect to your Dataverse environment where the dashboard was created.

Note: If you don’t have access to use XrmToolBox, you can also update this FormXml by creating a new unmanaged solution and adding in just the dashboard you created earlier. Then export and unzip the solution, you will find the FormXml within the customizations.xml file. Perform the updates as described in the next step, then re-zip all the files and reimport the solution.

Step 4: Modify the Dashboard’s XML

Open FormXml Manager in XrmToolBox.
Click Load Dashboards and select Dashboard in the entity list.
In the FormXmls pane, find and select your custom dashboard.
Click Edit FormXml to open the editor.

Locate the control corresponding to the List component added in Step 1 (e.g., the Accounts list).

Modify the XML as follows:

Change the label from:

<label description="Accounts" languagecode="1033" />

to:

<label description="Notes" languagecode="1033" />

Change the target entity from:

<TargetEntityType>account</TargetEntityType>

to:

<TargetEntityType>annotation</TargetEntityType>

Update the View ID with the one copied earlier:

<ViewId>{e7b7272b-dcee-ef11-be21-00224804c479}</ViewId>

Click Update and Publish the changes.

Step 5: Verify Changes in the Model-Driven App

Return to your Model-Driven App.
Open the Dashboard.
The list that previously displayed Accounts should now display Notes instead.

Conclusion

By following these steps, you have successfully modified a Model-Driven App Dashboard to display Notes (Annotations) using XrmToolBox. This method allows for greater customization of dashboards to meet business requirements.

Using Web Templates as Custom Components in Power Pages: A Classification Banner Example

2025-02-19T10:34:00.004-05:00

Introduction

Web templates in Power Pages offer a powerful way to create reusable components that can be used across multiple pages, headers, and footers. This approach simplifies development and maintenance by allowing common elements, such as banners or data tables, to be managed centrally. In this article, we will explore how to use web templates as custom components, focusing on a practical example: a classification banner that can be placed in the header or footer of a Power Pages site.

What Are Web Templates in Power Pages?

Web templates are Liquid-based templates that allow developers to structure and render dynamic content within Power Pages. They are commonly used for:

Custom layouts: Defining reusable structures for pages.
Reusable components: Creating elements like banners, notifications, and data tables.
Dynamic content: Injecting data dynamically using Dataverse and Liquid expressions.

Microsoft has provided comprehensive guidance on using web templates as components in Power Pages. You can read more about it in the official documentation.

A classification banner is a useful element in government or security-focused applications, where content must be labeled according to its classification level (e.g., Unclassified, Confidential, Top Secret). Instead of hardcoding this banner on multiple pages, we can create a reusable web template component.

Step 1: Creating the Web Template

Below is the web template code for a classification banner component that allows users to specify its position (top or bottom), text, and background color. Once you have the code, you need to add the web template to Power Pages. You can quickly do this using the Power Pages Management app.

Navigate to the Power Pages Management App via the Power Pages Maker Portal.

Create a new Web Template record.
Paste the code into the Source field and save the record.

{% manifest %}
{
    "type": "Functional",
    "displayName": "Classification Banner",
    "description": "Used to display the classification banner in the header and footer with customizable text and background color.",
    "params": [
        {
            "id": "position",
            "displayName": "Position",
            "description": "Enter the position of the banner: either 'top' or 'bottom'."
        },
        {
            "id": "text",
            "displayName": "Banner Text",
            "description": "Enter the classification text to display. Examples: 'Unclassified', 'Controlled (CUI)', 'Confidential', 'Secret', 'Top Secret', 'Top Secret//SCI'."
        },
        {
            "id": "backgroundColor",
            "displayName": "Background Color",
            "description": "Choose a background color based on classification level: Unclassified: #007a33, Controlled (CUI): #502b85, Confidential: #0033a0, Secret: #c8102e, Top Secret: #ff8c00, Top Secret//SCI: #fce83a."
        }
    ]
}
{% endmanifest %}

{% if user %}
<div style="
    background-color: {{ backgroundColor }};
    color: {% if backgroundColor == '#fce83a' or backgroundColor == '#ff8c00' %}black{% else %}white{% endif %};
    width: 100%;
    height: 2.5rem;
    font-weight: 700;
    justify-content: center;
    align-items: center;
    text-transform: uppercase;
    display: flex;
    position: sticky;
    {% if position == 'top' %}
        top: 0;
    {% endif %}
    {% if position == 'bottom' %}
        bottom: 0;
    {% endif %}
    left: 0;
    z-index: 1000;">
    {{ text }}
</div>
{% endif %}

Step 2: Adding the Component to a Page

Once the web template is created, it can be inserted into a page, header, or footer.

Inserting the Component into the Header

To insert the component into the header, use the VS Code Editor built into Power Pages. Navigate to the Header Web Template and add the following code snippet where the classification banner should appear:

{% component name="Classification Banner" position="top" text="Confidential" backgroundColor="#0033a0" %}

Step 3: Modifying Component Settings

Once the component is placed on a page, you can modify its settings directly within the editor. If the component is added to a header or footer, you will need to return to the VS Code Editor to make changes.

Step 4: Editing Web Templates in Restricted Environments

If you are working in an environment where the built-in VS Code Editor is not available, you can use the Power Platform CLI (PAC CLI) to download and edit the web templates.

Steps to Use PAC CLI:

Install the Power Platform CLI if you haven’t already.

Authenticate to your Power Pages environment using:

pac auth create --environment "your-environment" --cloud UsGovHigh

Download the web template files:

pac pages download --environment "your-environment" --cloud UsGovHigh --modelVersion 2

Make your modifications locally using your preferred editor.

Upload the updated web template back to Power Pages:

pac pages upload --environment "your-environment" --cloud UsGovHigh --path "your-local-path"

This method ensures you can manage your custom components even when direct online editing is not possible.

Conclusion

Using web templates as custom components in Power Pages allows for greater flexibility and maintainability in site development. Whether for simple elements like banners or more complex components like data tables, this approach enhances efficiency and reusability.

By leveraging the VS Code Editor or PAC CLI, you can efficiently create, modify, and deploy these components across your Power Pages sites. Ready to start building reusable components? Give it a try and enhance your Power Pages experience!

From Messy to Manageable: Cleanly Load Dataverse Tables in Power BI

2024-11-20T14:35:00.002-05:00

First and foremost, I want to take a moment to acknowledge Brandon Pires, who originally crafted the script that inspired all of this work. Brandon’s creativity laid the foundation for this blog post, and his efforts are key to helping Power BI developers improve efficiency and cleanliness in their reports.

Introduction: Using Templates to Keep Your Dataverse Clean

When working with Microsoft Dataverse in Power BI, things can get messy quickly. Dataverse often contains a wealth of fields, system columns, and metadata that can make your reports harder to understand, cluttered, and less efficient. However, by leveraging templates and custom functions, you can automatically clean and format your data, allowing you to focus on deriving insights rather than grappling with unnecessary noise.

One such function, inspired by Brandon Pires, is designed to clean column names from Dataverse and keep them tidy. In this blog post, I will explain how this function works, why it improves efficiency, and how you can use it in your Power BI workflows to create more manageable and maintainable data models.

The Power BI Cleaning Function: A Deep Dive

let
    // Define the function with parameters for the table name, prefix, Dataverse host, and feature toggles
    #"Clean Table" = (tableName as text, prefix as text, dataverseHost as text, transformDateTimeToDate as logical, removeColumnsWithTablesAndRecords as logical) =>
    let
        // Connect to the Common Data Service
        Source = CommonDataService.Database(dataverseHost),
        
        // Retrieve the specified table
        #"One Table" = Source{[Schema="dbo", Item=tableName]}[Data],
        
        // Rename columns with the specified prefix
        #"Rename columns to system columns" = Table.RenameColumns(
            #"One Table",
            {
                {"createdon", prefix & "createdon"},
                {"modifiedon", prefix & "modifiedon"},
                {"createdby", prefix & "createdby"},
                {"statuscodename", prefix & "statusreason"},
                {"statecodename", prefix & "state"}
            }
        ),
        
        // List of all datetime and datetimezone columns
        #"List of all datetime and datetimezone columns" = if transformDateTimeToDate then
            Table.ColumnsOfType(
                #"Rename columns to system columns",
                {
                    type nullable datetime,
                    type datetime,
                    type nullable datetimezone,
                    type datetimezone
                }
            )
        else
            {},
        
        // Create list pair of datetime and datetimezone columns to type date pair
        #"Create list pair of datetime and datetimezone columns to type date pair" = if transformDateTimeToDate then
            List.Transform(
                #"List of all datetime and datetimezone columns",
                each {_, type date}
            )
        else
            {},
        
        // Transform datetime and datetimezone columns to date
        #"Transform datetime and datetimezone columns to date" = if transformDateTimeToDate then
            Table.TransformColumnTypes(
                #"Rename columns to system columns",
                #"Create list pair of datetime and datetimezone columns to type date pair"
            )
        else
            #"Rename columns to system columns",
        
        // Get names of columns with tables and records
        #"Get names of columns with tables and records" = if removeColumnsWithTablesAndRecords then
            Table.ColumnsOfType(
                #"Transform datetime and datetimezone columns to date",
                {
                    type nullable table,
                    type table,
                    type nullable record,
                    type record
                }
            )
        else
            {},
        
        // Remove columns with tables and records
        #"Remove columns with tables and records" = if removeColumnsWithTablesAndRecords then
            Table.RemoveColumns(
                #"Transform datetime and datetimezone columns to date",
                #"Get names of columns with tables and records"
            )
        else
            #"Transform datetime and datetimezone columns to date",
        
        // Remove system columns, only keep columns with specified prefix or "modified"
        #"Remove system columns" = Table.TransformColumnNames(
            Table.SelectColumns(
                #"Remove columns with tables and records",
                List.Select(
                    Table.ColumnNames(#"Remove columns with tables and records"),
                    each Text.StartsWith(_, prefix) or Text.StartsWith(_, "modified")
                )
            ),
            each Text.Replace(_, prefix, "")
        )
    in
        #"Remove system columns"
in
    #"Clean Table"

The function script presented here is a template that helps you remove unnecessary columns and rename system fields, making your data more consistent and easier to work with. The function takes in parameters such as the Dataverse host URL, a prefix for column names, and flags to control additional transformations. Let’s break down how each part of this function works:

Connect to the Dataverse Host: This function starts by connecting to Dataverse using a parameterized URL (dataverseHost). This is a best practice because it allows the same function to be used across different environments (like dev, test, and production) without modifying the core script.
Rename System Columns: One of the early steps in the function is to rename system columns by adding a specified prefix (prefix). This ensures that key fields like createdon, modifiedon, etc., are consistently labeled, helping to avoid confusion. Consistent column naming also makes your data model more intuitive for future developers or collaborators.
Optional Transformations: This function uses two feature toggles (transformDateTimeToDate and removeColumnsWithTablesAndRecords) to control how much data cleaning is performed. This flexibility helps adapt the function to different use cases:
- Date Transformations: Converts datetime columns to simple date columns if specified, reducing data complexity and making it easier to create date-based visuals.
- Removing Complex Columns: The script can also remove columns containing nested tables or records, which can complicate data processing and impact performance.
Filtering Columns: Finally, the function removes any columns that do not match the specified prefix or the word "modified". This helps limit your data to only what you need, cutting down on clutter and improving the efficiency of your Power BI reports.

Best Practice: Use a Parameter for Dataverse URL

One of the best practices highlighted by this script is to use a parameter for the Dataverse URL (dataverseHost). By using parameters, you can easily switch environments without editing the script each time. This approach supports a more agile and scalable development process and allows teams to deploy their reports in multiple environments seamlessly.

If you’re unsure how to create a parameter in Power BI, here’s a quick guide:

In Power BI Desktop, navigate to the Home tab and click Manage Parameters.
Click New Parameter and name it something like DataverseUrl.
Set the Type to Text and use the Suggested Values option to create a List of Values. Enter your different environment URLs, such as Dev, Integration, and Prod, into the list. Then, set the Default Value and Current Value to the appropriate environment (e.g., your development Dataverse URL).
Use this parameter in your query to reference different environments without rewriting your scripts.

How to Add This Function to Your Power BI Report

To use this function within Power BI, follow these steps:

Open Power Query Editor: In your Power BI Desktop file, go to Transform Data to open Power Query Editor.
Create a New Query: Click on New Source > Blank Query, then open the Advanced Editor and paste the script provided.
Rename Function: After pasting, rename the query to something like CleanTable.
Invoke the Function: Now that you have the function, you can use it to clean multiple tables. For each table, create a new query and use Invoke Custom Function, selecting CleanTable and specifying your desired parameters. You can also use this function on Microsoft tables, such as account and contact, if you wish to apply some of the optional transformations. Just make sure to set the prefix parameter to an empty string ("").

This process allows you to apply consistent cleaning across multiple tables in Dataverse, significantly improving both efficiency and maintainability.

Save as a Template for Future Use

You can also save your Power BI report with this function and parameter as a template file (.pbit). This allows you to easily reuse the setup for all of your future reports. By using a template, you can ensure consistency across different projects and avoid repeating the setup process each time you need to create a new report.

Conclusion

Keeping your Power BI data clean and organized is crucial for effective reporting and analysis. By leveraging custom functions like the one described here, you can maintain consistency, reduce clutter, and improve the performance of your Power BI reports. Inspired by Brandon Pires, this script is a powerful tool to help streamline your data transformation process, making your reports more intuitive and easier to manage.

Feel free to try this approach in your next Power BI project and let me know how it works for you. Embracing these best practices will save time and help your team stay focused on what really matters—extracting valuable insights from your data.

Bypass Power Apps Consent Prompts Using PowerShell

2024-10-16T09:35:00.002-04:00

When a user opens a Power App for the first time, they are often presented with a consent form asking for permission to access data sources or APIs used by the app. This Canvas API consent form can be disruptive, especially in scenarios where the app is intended for a broad user base or part of an automated deployment. The consent form can create unnecessary friction, requiring every user to individually approve permissions. Fortunately, there’s a way to bypass this pop-up consent form using PowerShell.

In this blog post, I will guide you through automating the process of bypassing the Power Apps consent form using PowerShell. The goal is to either allow users to select an app from a specific environment or use a configuration file to perform actions on multiple environments.

This automation can be particularly useful for initial deployments of a new app or to ensure all apps currently have this applied.

Background: Bypassing Power Apps Consent

Before diving into the PowerShell script, it’s important to understand what the Canvas API consent form is and why it might need to be bypassed. The consent form prompts users to confirm that they are comfortable with the application accessing various data sources. While this is an important security measure, it can be a hindrance in some situations. For example, when you have a small embedded app in a Power BI application, it may not be large enough for a user to see the consent form. Similarly, for apps that have been integrated into model-driven apps, users may not even realize that a Canvas App is present.

Matthew Devaney’s article on how to Disable the Power Apps Permissions Pop-Up and Wouter detailed guide on Bypassing Consent When Opening a Power App are excellent resources to get started. They demonstrate how to use PowerShell commands to bypass the consent screen for individual apps. I wanted to take this a step further by making the process more configurable and applicable to multiple environments.

Script Overview

The PowerShell script makes use of the Microsoft.PowerApps.Administration.PowerShell module to interact with Power Apps environments and apps. With the ability to import a configuration file, you can easily define environments and apps that need to have their consent requirements bypassed. Alternatively, the script allows you to select a specific app interactively.

Here’s a high-level overview of how the script works:

Import Required Modules: The script begins by checking if the necessary PowerShell module (Microsoft.PowerApps.Administration.PowerShell) is installed. If not, it installs the module automatically.
Disable Consent for Selected Apps: Depending on how the script is run, it can either take an environment ID and app ID directly or load a configuration file to process multiple apps.
Configuration File Option: The configuration file allows you to define multiple environments and their corresponding apps. This option is useful for automating environments like dev, QA, and production.

The script is documented using PowerShell’s self-documenting features, making it easier to understand and extend. The self-documentation provides details on parameters, descriptions, and usage examples. For more information on how to use these self-documenting features, see Microsoft’s documentation.

PowerShell Script

Below is the complete PowerShell script for automating the process of bypassing the Canvas API consent form. Before diving into the code, here’s a brief explanation: This script can be used to disable the consent prompts for specific Power Apps either interactively or through a predefined configuration. It is useful for simplifying user experience, particularly in deployments where manual consent approvals would be cumbersome.

Getting the Environment ID

To use this script, you will need the environment ID of your Power Platform environment. The easiest way to get this ID is by navigating to the Power Platform Admin Center, selecting the ‘Environments’ area, and viewing the details for your specific environment. Below is a picture that shows where you can find the details for an environment:

How to Use the Script Based on Different Examples

Using a Configuration File:
- Example: ./DisableAppConsent.ps1 -configFilePath "./config.json" -configEnvironmentName "dev"
- This example uses the configuration file to disable consent for all apps in the “dev” environment. You can define multiple environments and their respective apps within a JSON file. This method is particularly helpful if you want to automate the consent bypass process for multiple environments without any manual intervention.
Direct Environment and App ID:
- Example: ./DisableAppConsent.ps1 -environmentId "env-id" -appId "app-id"
- This example disables consent for a specific app using the provided environment ID and app ID. This is ideal when you already know the specific environment and app details you need to process. It allows for a quick and precise update without requiring additional configuration.
Interactive App Selection:
- Example: ./DisableAppConsent.ps1 -environmentId "env-id"
- This example allows the user to select an app from the specified environment to disable consent. If you only have an environment ID but do not know the specific app to target, this interactive option is useful. It will present a list of apps within the specified environment, allowing you to choose one interactively.

You can also view the script on GitHub: You can also view the script on GitHub:

<#
.SYNOPSIS
    This script disables the consent prompt for specified Power Apps in an environment.

.DESCRIPTION
    The script provides three ways to disable the consent prompt:
    1. Using a configuration file that contains environment and app details.
    2. Using a provided environment ID and app ID.
    3. Allowing the user to select an app from a given environment.

.AUTHOR
    Rick Wilson

.PARAMETER configFilePath
    The path to the configuration JSON file containing environment and app details.

.PARAMETER configEnvironmentName
    The name of the environment (e.g., dev, prod, qa) as specified in the configuration file.

.PARAMETER environmentId
    The ID of the environment to be used for disabling consent if a configuration file is not provided.

.PARAMETER appId
    The ID of the app to be used for disabling consent if a configuration file is not provided.

.EXAMPLE
    ./DisableAppConsent.ps1 -configFilePath "./config.json" -configEnvironmentName "dev"
    This example uses the configuration file to disable consent for all apps in the "dev" environment.

.EXAMPLE
    ./DisableAppConsent.ps1 -environmentId "env-id" -appId "app-id"
    This example disables consent for a specific app using the provided environment ID and app ID.

.EXAMPLE
    ./DisableAppConsent.ps1 -environmentId "env-id"
    This example allows the user to select an app from the specified environment to disable consent.

#>

# Parameter definitions
param (
    [Parameter(Mandatory=$false, HelpMessage="The path to the configuration JSON file containing environment and app details.")]
    [string]$configFilePath = "",

    [Parameter(Mandatory=$false, HelpMessage="The name of the environment (e.g., dev, prod, qa) as specified in the configuration file.")]
    [string]$configEnvironmentName = "",

    [Parameter(Mandatory=$false, HelpMessage="The ID of the environment to be used for disabling consent if a configuration file is not provided.")]
    [string]$environmentId = "",

    [Parameter(Mandatory=$false, HelpMessage="The ID of the app to be used for disabling consent if a configuration file is not provided.")]
    [string]$appId = ""
)

# Function to check if a module is installed and import it if necessary
function Import-ModuleIfNeeded {
    param (
        [Parameter(Mandatory=$true)] [string]$ModuleName
    )
    if (-not (Get-Module -ListAvailable -Name $ModuleName)) {
        Write-Output "Module $ModuleName not found. Installing..."
        Install-Module -Name $ModuleName -Force -AllowClobber
    }
    Import-Module -Name $ModuleName
}

# Import required module if it is not already installed
Import-ModuleIfNeeded -ModuleName "Microsoft.PowerApps.Administration.PowerShell"

# Function to Disable Consent on a given App
function Disable-ConsentForApp {
    param (
        [Parameter(Mandatory=$true)] [string]$EnvironmentId,
        [Parameter(Mandatory=$true)] [string]$AppId
    )
    
    # Disable consent
    Set-AdminPowerAppApisToBypassConsent -EnvironmentName $EnvironmentId -AppName $AppId
}

# Main logic to determine which flow to execute
if ($configFilePath -ne "" -and $configEnvironmentName -ne "") {
    # Load configuration file
    if (Test-Path $configFilePath) {
        $config = Get-Content -Raw -Path $configFilePath | ConvertFrom-Json
        
        $environment = $config.environments | Where-Object { $_.name -eq $configEnvironmentName }
        if ($null -ne $environment) {
            foreach ($appName in $environment.appNames) {
                Write-Output "Retrieving App ID for app: $appName in environment: $environment.name"
                $app = Get-AdminPowerApp $appName -EnvironmentName $environment.environmentId
                if ($null -ne $app) {
                    Write-Output "Processing app: $appName with App ID: $($app.AppName) in environment: $environment.name"
                    Disable-ConsentForApp -EnvironmentId $environment.environmentId -AppId $app.AppName
                } else {
                    Write-Output "App: $appName not found in environment: $environment.name"
                }
            }
        } else {
            Write-Output "Environment: $configEnvironmentName not found in configuration file."
            exit
        }
    } else {
        Write-Output "Configuration file not found at path: $configFilePath"
        exit
    }
} elseif ($environmentId -ne "" -and $appId -ne "") {
    # Use provided environment and app ID
    Write-Output "Processing app: $appId in environment: $environmentId"
    Disable-ConsentForApp -EnvironmentId $environmentId -AppId $appId
} elseif ($environmentId -ne "") {
    # Prompt user to select an app from the provided environment
    Write-Output "Loading Power Apps from environment: $environmentId..."
    $apps = Get-AdminPowerApp -EnvironmentName $environmentId
    $appChoices = $apps | ForEach-Object { "$_.DisplayName ($_.AppName)" }

    $selectedAppIndex = $appChoices | Out-GridView -Title "Select a Power App to disable consent" -OutputMode Single
    $selectedApp = $apps[$selectedAppIndex]

    if ($null -ne $selectedApp) {
        Disable-ConsentForApp -EnvironmentId $environmentId -AppId $selectedApp.AppId
    } else {
        Write-Output "No app selected. Exiting."
    }
} else {
    Write-Output "Please provide either a configuration file path and config file environment name, or an environment ID."
    exit
}

Write-Output "Operation completed."

Sample Configuration File

Below is a sample configuration file (config.json) that you can use with the script. This file defines multiple environments and the corresponding apps that need to have their consent bypassed. You can also view this sample configuration file on GitHub.

{
    "environments": [
        {
            "name": "dev",
            "environmentId": "00000000-0000-0000-0000-000000000000",
            "appNames": [
                "App Display Name"
            ]
        },
        {
            "name": "prod",
            "environmentId": "00000000-0000-0000-0000-000000000000",
            "appNames": [
                "App Display Name"
            ]
        }
    ]
}

Use Cases

Interactive Deployment: If you’re managing a single environment and need to disable consent for just one app, the script allows you to manually select the environment and app, making the process straightforward.

Conclusion

Using PowerShell to automate the bypassing of Power Apps consent forms can greatly simplify the user experience, particularly in large deployments. By incorporating flexibility to either select an app interactively or use a configuration file, you can customize the process to suit your needs—whether it’s a quick update or a flexible deployment process.

The articles by Matthew Devaney and Wouter provide an excellent foundation, and this script builds upon those concepts to offer more advanced, flexible automation. I hope this guide and the PowerShell script help you streamline your Power Apps deployments and reduce friction for your users.

Custom Auto Number in Dataverse with Fiscal Year-Based Incrementing Using Plugin Code

2024-08-23T15:20:00.001-04:00

In many scenarios, businesses require custom auto-numbering for records in Microsoft Dataverse that go beyond the default functionality provided. In this article, I’ll walk you through a solution that involves creating a custom auto-number field without using the out-of-the-box auto-numbering feature in Dataverse. Instead, we’ll use a combination of a custom entity as a counter and a plugin that triggers on the pre-create event of the target table.

The Approach

For this solution, we’ll create a custom table called Fiscal Year Counter. This table will manage our numbering sequence based on the fiscal year. The idea is to dynamically generate invoice numbers that reset each fiscal year, providing a high level of customization to fit your organization’s unique fiscal calendar.

The Fiscal Year Counter Table Setup

First, create a table named Fiscal Year Counter with the following fields:

Name (String): To store the fiscal year name, such as “FY2024.”
Start Date (Date Only, Time zone independent): The start date of the fiscal year.
End Date (Date Only, Time zone independent): The end date of the fiscal year.
Last Number (Whole Number): To track the last generated number for the fiscal year.

This setup allows you to configure fiscal years according to your organization’s requirements. The records in this table must be pre-created with appropriate Name, Start Date, and End Date values. If you want to start the numbering at a value other than 1, you can set the Last Number field accordingly.

Important: Ensure that the Start Date and End Date fields are set to “Date Only” and “Time Zone Independent.” This is crucial for the logic to work correctly, as using other date/time formats may lead to discrepancies in determining the correct fiscal year during the auto-numbering process.

Implementing the Auto-Numbering Logic

To automate the numbering, I developed a plugin that runs on the pre-create event of the target table, which in this example is Invoices. This plugin increments the last number in the Fiscal Year Counter table based on the current fiscal year and assigns the new number to the invoice.

Using the Record Creation Date to Determine the Fiscal Year

The plugin determines which fiscal year a record belongs to by using the date on which the record is created. It compares this date with the start and end dates defined in the Fiscal Year Counter table to identify the correct fiscal year.

Ensuring Uniqueness Through Database Locks

A crucial part of the plugin’s design is ensuring that each auto-generated number is unique. To achieve this, the plugin locks the Fiscal Year Counter record during the number generation process. This lock prevents other processes from accessing the same record simultaneously, which could otherwise result in duplicate numbers.

This locking mechanism is critical to maintaining the integrity of the auto-numbering sequence. Therefore, it’s important not to refactor the code for the sake of efficiency, as the current design prioritizes accuracy and uniqueness over speed.

Steps in the Auto-Numbering Process

Here’s a high-level overview of the steps involved:

Identify the Fiscal Year: When a new invoice is created, the plugin uses the current date to determine which fiscal year the record falls into by comparing it against the start and end dates in the Fiscal Year Counter table.
Lock the Fiscal Year Record: The plugin locks the identified fiscal year record to ensure no other process can modify it simultaneously, preventing duplicate numbers.
Increment the Last Number: The plugin retrieves the Last Number field for the identified fiscal year, increments it, and updates the field.
Assign the Auto-Number: The plugin concatenates the fiscal year name (e.g., “FY2024”) with the incremented number and assigns it to the invoice.

Writing the Plugin

To implement this, you need to write a plugin that follows the steps outlined above. I won’t go into the details of settings up a plugin project, but I highly recommend referring to this guide on writing plugins if you need help getting started. This guide provides a comprehensive tutorial on creating and registering a plugin in Dataverse.

using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Messages;
using Microsoft.Xrm.Sdk.Query;
using System;
using System.Linq;
using System.ServiceModel;

namespace AutoNumberByDate.Plugins
{
    public class AutoNumberByFiscalYear : IPlugin
    {
        public void Execute(IServiceProvider serviceProvider)
        {
            // Obtain the tracing service
            ITracingService tracingService =
            (ITracingService)serviceProvider.GetService(typeof(ITracingService));

            // Obtain the execution context from the service provider.  
            IPluginExecutionContext context = (IPluginExecutionContext)
                serviceProvider.GetService(typeof(IPluginExecutionContext));


            // The InputParameters collection contains all the data passed in the message request.  
            if (context.InputParameters.Contains("Target") &&
                context.InputParameters["Target"] is Entity)
            {
                // Obtain the target entity from the input parameters.  
                Entity entity = (Entity)context.InputParameters["Target"];

                // Obtain the IOrganizationService instance which you will need for web service calls.  
                IOrganizationServiceFactory serviceFactory =
                    (IOrganizationServiceFactory)serviceProvider.GetService(typeof(IOrganizationServiceFactory));
                // Passing null into CreateOrganizationService will get the system organization service which will have access to the fiscal year counter by default.
                IOrganizationService service = serviceFactory.CreateOrganizationService(null);

                try
                {
                    //call the method to set the name field with the fiscal year number
                    SetNameFieldWithFiscalYearNumber(entity, service);
                }

                catch (FaultException<OrganizationServiceFault> ex)
                {
                    throw new InvalidPluginExecutionException("An error occurred in AutoNumberByFiscalYear.", ex);
                }

                catch (Exception ex)
                {
                    tracingService.Trace("AutoNumberByFiscalYear: {0}", ex.ToString());
                    throw;
                }
            }
        }

        private static void SetNameFieldWithFiscalYearNumber(Entity entity, IOrganizationService service)
        {
            string controlNumber = "0001";
            int currentNumber = 1;
            // Get the current date in UTC.
            DateTime currentDate = DateTime.UtcNow.Date;

            // Look for an existing fiscal year counter entity
            QueryExpression qExpression = new QueryExpression()
            {
                EntityName = "raw_fiscalyearcounter",
                ColumnSet = new ColumnSet("raw_name", "raw_lastnumber"),
                Criteria = new FilterExpression
                {
                    Conditions =
                    {
                        new ConditionExpression("raw_start", ConditionOperator.LessEqual, currentDate),
                        new ConditionExpression("raw_end", ConditionOperator.GreaterEqual, currentDate)
                    }
                }
            };
            EntityCollection results = service.RetrieveMultiple(qExpression);
            Entity counter = results.Entities.FirstOrDefault();

            if (counter != null)
            {
                // If the counter is there update it to lock it in the transaction.
                Entity blocker = new Entity() { LogicalName = "raw_fiscalyearcounter", Id = counter.Id };
                blocker["raw_name"] = counter.GetAttributeValue<string>("raw_name");
                service.Update(blocker);

                // Now that the counter is locked retrieve the counter again so that we can get the current number and update it.
                QueryExpression qExpression2 = new QueryExpression()
                {
                    EntityName = "raw_fiscalyearcounter",
                    ColumnSet = new ColumnSet("raw_lastnumber", "raw_name"),
                    Criteria = new FilterExpression
                    {
                        Conditions =
                        {
                            new ConditionExpression("raw_start", ConditionOperator.LessEqual, currentDate),
                            new ConditionExpression("raw_end", ConditionOperator.GreaterEqual, currentDate)
                        }
                    }
                };

                // DO NOT refactor RetrieveMultipleRequest to RetrieveMultiple otherwise some caching will occur.
                RetrieveMultipleRequest rmRequest = new RetrieveMultipleRequest() { Query = qExpression2 };
                RetrieveMultipleResponse lockedResults = (RetrieveMultipleResponse)service.Execute(rmRequest);
                Entity lockedCounter = lockedResults.EntityCollection.Entities.First();

                Entity counterUpdater = new Entity() { LogicalName = "raw_fiscalyearcounter", Id = counter.Id };

                // Get the current number and increment it.
                currentNumber = lockedCounter.GetAttributeValue<int>("raw_lastnumber");
                currentNumber = ++currentNumber;
                controlNumber = (currentNumber).ToString().PadLeft(4, '0');

                // Update the counter with the new number.
                counterUpdater["raw_lastnumber"] = currentNumber;
                service.Update(counterUpdater);

                // Set the control number on the invoice {FY}{Sequence #}".
                entity["raw_name"] = $"{lockedCounter.GetAttributeValue<string>("raw_name")}{controlNumber}";
            }
            else
            {
                //if no counter was found throw an error
                throw new InvalidPluginExecutionException("No counter was found for the current fiscal year. Please contact your system administrator.");
            }
        }
    }
}

You can access the complete code solution I created you can get it here: https://github.com/rwilson504/DataverseAutoNumberByDateIncrements/tree/main/code/AutoNumberByDate.

Example Solution

To help you get started, I’ve created a sample solution that implements the concepts discussed in this article. This example includes the Fiscal Year Counter table, the plugin code, and the necessary configurations to replicate the auto-numbering functionality.

Additionally, I’ve included a sample table called Invoices, along with a model-driven app that integrates these tables. This setup allows you to easily configure and test the auto-numbering functionality in a practical environment.

You can download the sample solutions here:

Creating the Fiscal Year Counter Records

Below are screenshots showing the setup of the Fiscal Year Counter records. These screenshots demonstrate how to configure the Name, Start Date, End Date, and Last Number fields.

Creating New Invoices

Here are screenshots showing the process of creating new invoices with the custom auto-numbering system in place. You’ll see how the plugin automatically assigns a unique invoice number based on the current fiscal year using the record creation date.

Conclusion

By following this approach, you can create a highly configurable auto-numbering system that adapts to your organization’s fiscal year requirements while ensuring the uniqueness of each generated number. This method not only offers flexibility but also integrates seamlessly with Dataverse, allowing for custom logic and automation that out-of-the-box features may not provide.

Connecting the Paconn CLI Tool to GCC, GCCH, and DoD Environments for Power Platform Connectors

2024-08-21T16:01:00.001-04:00

When working with the paconn CLI tool to create and manage custom connectors in the Power Platform, it’s essential to configure your environment settings properly, especially when targeting different government cloud environments like GCC, GCC High, and DoD. In this article, we’ll guide you through setting up these configurations, including creating a connectionSettings.json file for each environment, downloading a custom connector, and updating the connector’s settings for seamless management.

Prerequisites

Azure Subscription: You need an active Azure subscription to register an application.
Paconn CLI Tool: Ensure you have the paconn CLI tool installed. You can install it using Python’s pip:
```
pip install paconn
```

Step 1: Create an Azure App Registration

Begin by registering an application in Azure Active Directory (Azure AD) that will serve as the identity for your paconn operations.

Navigate to Azure AD: Sign in to the Azure portal and go to Azure Active Directory.
Create a New App Registration:
- Go to App registrations > New registration.
- Provide a name, such as “Paconn Connector App.”
- Redirect URI: Skip this step as it is not required.
API Permissions:
- The app automatically includes the User.Read permission under Microsoft Graph. No additional API permissions are needed.
Allow Public Client Flows:
- Under Authentication > Advanced settings, set Allow public client flows to Yes. This enables the device code flow, which paconn uses for authentication.
Copy Your IDs:
- After registering the application, go to the Overview section.
- Copy the Application (client) ID and Directory (tenant) ID. These values will be used in the connectionSettings.json file in the next step.

By the end of this step, you should have your Application (client) ID and Directory (tenant) ID ready for configuring the connection settings in the following step.

Step 2: Create the `connectionSettings.json` File

Next, you’ll create a connectionSettings.json file with specific values tailored for each environment. This file is crucial for authenticating and operating within your selected cloud.

GCC Environment

{
  "powerAppsUrl": "https://gov.api.powerapps.us/",
  "flowUrl": "https://gov.api.flow.microsoft.us/",  
  "resource": "https://gov.service.powerapps.us/",
  "authorityUrl": "https://login.microsoftonline.com/",
  "clientId": "<Your Application (client) ID>",
  "tenant": "<Your Directory (tenant) ID>"
}

GCC High Environment

{
  "powerAppsUrl": "https://high.api.powerapps.us/",
  "flowUrl": "https://high.api.flow.microsoft.us/",
  "resource": "https://high.service.powerapps.us/",
  "authorityUrl": "https://login.microsoftonline.us/",
  "clientId": "<Your Application (client) ID>",
  "tenant": "<Your Directory (tenant) ID>"
}

DoD Environment

{
  "powerAppsUrl": "https://api.apps.appsplatform.us/",
  "flowUrl": "https://api.flow.appsplatform.us/",
  "resource": "https://service.apps.appsplatform.us/",
  "authorityUrl": "https://login.microsoftonline.us/",
  "clientId": "<Your Application (client) ID>",
  "tenant": "<Your Directory (tenant) ID>"
}

Replace <Your Application (client) ID> and <Your Directory (tenant) ID> with the values from your Azure App Registration.

Step 3: Log In to Paconn

Once you’ve configured your connectionSettings.json file with the correct values for your environment, the next step is to log in to the paconn CLI tool. This authentication process is essential for performing any subsequent operations with the tool.

To log in, use the following command:

paconn login --settings connectionSettings.json

This command initiates the login process. Follow the prompts to authenticate using the device code flow. Once logged in, you’ll be ready to download and manage your custom connectors.

Step 4: Download a Custom Connector

After successfully logging in, you can download an existing custom connector from your environment. This process involves selecting the environment and the specific connector you want to work with.

Run the following command:

paconn download --settings connectionSettings.json

This command will prompt you to choose the environment based on your connectionSettings.json file. After selecting the environment, you’ll be able to choose the connector you wish to download. The connector will be saved locally, along with a settings.json file.

Step 5: Update the Connector

Once you have downloaded the connector, you may need to update it. The settings.json file downloaded with the connector needs to include the same settings you have in your connectionSettings.json file. This ensures consistency when managing or updating the connector.

Here’s what you need to do:

Open the Downloaded settings.json File: Locate the file that was downloaded along with the connector.

Update the Fields: Add or update the following fields with the values from your connectionSettings.json:

{
  "powerAppsUrl": "https://high.api.powerapps.us/",
  "flowUrl": "https://high.api.flow.microsoft.us/",
  "resource": "https://high.service.powerapps.us/",
  "authorityUrl": "https://login.microsoftonline.us/",
  "clientId": "<Your Application (client) ID>",
  "tenant": "<Your Directory (tenant) ID>"
}

Save the File: Ensure all changes are saved.

To apply the updates to the connector, run:

paconn update --settings settings.json

This command will push the changes to the connector in the selected environment.

Step 6: Create a New Connector

Creating a new custom connector follows a similar process to updating an existing one, but with a few differences. Since it’s a new connector, you don’t need to include all the same properties in the settings.json file.

Here’s an example of a settings.json file for a new connector:

{
  "environment": "d9f0b637-5539-e256-9232-ecb5839cdb02",
  "apiProperties": "apiProperties.json",
  "apiDefinition": "apiDefinition.swagger.json",
  "icon": "icon.png",
  "powerAppsUrl": "https://high.api.powerapps.us/",
  "flowUrl": "https://high.api.flow.microsoft.us/",
  "authorityUrl": "https://login.microsoftonline.us/",
  "resource": "https://high.service.powerapps.us/",
  "clientId": "<Your Application (client) ID>",
  "tenant": "<Your Directory (tenant) ID>"
}

Key Differences:

No connectorId: Since this is a new connector, you don’t need to include the connectorId property.
Optional script Property: If you aren’t including custom code, you can omit the script property from the settings.json file.

Once the settings.json file is ready, you can create the new connector with the following command:

paconn create --settings settings.json

This will create a new custom connector in your specified environment using the provided settings.

Conclusion

When working with government cloud environments like GCC, GCC High, and DoD, it’s essential to consistently use settings.json files for your operations with the paconn CLI tool. This is because attempting to mix the --settings option with other command-line arguments like --api-prop and --api-def will not work as expected.

To ensure smooth operations:

Keep a connectionSettings.json file at your project root: This file should contain the basic authentication and environment details.
Update the settings.json file for each connector: Tailor it with the specific details for each connector you manage.

By following this approach, you’ll be able to effectively use the paconn CLI tool to manage connectors across various government cloud environments.

It’s also worth noting that while the PAC CLI tool also supports commands for managing connectors, it is relatively new and still has some bugs. Until these issues are resolved, I recommend continuing with the paconn method described here. However, the PAC CLI tool does offer a much easier way to connect to these clouds, so I’m optimistic about its potential in the future.

Overcoming OpenAPI 2.0 `multi` CollectionFormat Challenges in Power Automate Custom Connectors

2024-06-24T13:51:00.002-04:00

Integrating APIs into Power Automate flows often requires creating custom connectors, a process that can encounter challenges with certain API specifications. Specifically, the OpenAPI 2.0 specification allows array and multi-value parameters to be passed in various formats, including the multi collection format. This format is particularly troublesome for Power Automate’s Custom Connector wizard, which does not support it natively. This article outlines the issue and provides a detailed workaround using custom code components and modifying input parameters, ensuring seamless API integration.

The Challenge with `multi` CollectionFormat

The OpenAPI 2.0 specification introduces several collection formats for array parameters, with multi being one of them. This format allows multiple values for the same parameter to be passed by repeating the parameter’s name in the query string, which is not directly supported in Power Automate Custom Connectors. Attempting to import an OpenAPI specification using the multi format results in an error, blocking the creation of the custom connector and hindering integration efforts.

Error Encountered

Developers face a specific error when the Custom Connector wizard encounters a multi collection format:

Error: paths/~1v1~1preview~1records~1person/get/parameters/1/collectionFormat: The 'collectionFormat' keyword value 'Multi' is not supported.

This error signifies the inability of the wizard to process the specified collection format, necessitating a workaround to proceed with the connector creation.

A Workaround

Step 1: Modifying Parameter Types from Array to String

The first part of the workaround involves changing the input parameters’ types in the OpenAPI definition from arrays to strings. This modification aims to bypass the Custom Connector wizard’s limitations by allowing the input of multiple values in a single, comma-separated string.

Original Array Parameter Definition

{
  "collectionFormat": "multi",
  "description": "List of services to filter facilities by the services they offer.",
  "x-ms-summary": "Services Offered",
  "in": "query",
  "items": {
    "type": "string"
  },
  "name": "services[]",
  "type": "array"
}

Modified String Parameter Definition

{
  "description": "A comma-separated list of services to filter facilities by the services they offer.",
  "x-ms-summary": "Services Offered",
  "in": "query",
  "name": "services[]",
  "type": "string"
}

Step 2: Leveraging Custom Code Components

To handle the modified string parameters correctly and support the original intention of passing multiple values, custom code components within the connector are utilized. These components programmatically adjust the request’s query string, ensuring that the API receives the parameters in the expected multi format.

For detailed guidance on writing code for Power Automate Custom Connectors, visit Microsoft’s official documentation.

Custom Code Example

public class Script : ScriptBase
{
    private readonly Dictionary<string, string[]> specialHandlingMap = new Dictionary<string, string[]>
    {
        { "GetFacilities", new[] { "facilityIds", "services[]", "bbox[]" } },
        { "GetFacilityServicesById", new[] { "serviceIds" } },
        { "GetNearbyFacilities", new[] { "services[]" } }
    };

    public override async Task<HttpResponseMessage> ExecuteAsync()
    {
        var operationId = this.Context.OperationId;
        if (specialHandlingMap.TryGetValue(operationId, out var queryParamNames))
        {
            var query = HttpUtility.ParseQueryString(this.Context.Request.RequestUri.Query);
            foreach (var paramName in queryParamNames)
            {
                if (query.AllKeys.Contains(paramName))
                {
                    var values = query[paramName].Split(',').Select(value => value.Trim()).ToArray();
                    query.Remove(paramName);
                    foreach (var value in values)
                    {
                        query.Add(paramName, value);
                    }
                }
            }

            var uriBuilder = new UriBuilder(this.Context.Request.RequestUri) { Query = query.ToString() };
            this.Context.Request.RequestUri = uriBuilder.Uri;
        }

        return await this.Context.SendAsync(this.Context.Request, this.CancellationToken).ConfigureAwait(false);
    }
}

Importance of Descriptive Parameter Descriptions

Adjusting the parameter types necessitates clear and descriptive parameter descriptions to guide users on the correct input format. Descriptions like “A comma-separated list of services…” ensure users understand how to format their input, enhancing usability and reducing errors.

Conclusion

By modifying the OpenAPI definition to change parameter types from arrays to strings and utilizing custom code components within Power Automate Custom Connectors, developers can overcome the limitations posed by the lack of support for the multi collection format. This comprehensive workaround ensures that custom connectors remain a powerful tool for integrating a wide range of APIs into Power Automate flows, even when facing complex parameter formatting challenges.

Installing PowerShell Modules in Firewalled and Air-Gapped Systems

2024-06-14T10:13:00.002-04:00

Managing IT environments with limited or no internet access, such as firewalled systems or air-gapped setups, presents unique challenges. One of the critical tasks in such environments is the installation and usage of PowerShell modules, which often require internet access for download and updates. This guide provides a method to facilitate the offline installation of PowerShell modules using custom scripts and includes a practical example of managing Power Platform environments by limiting developer environments.

Background

Firewalled systems have restricted internet access, necessitating special methods to manage and configure software. Air-gapped systems are completely isolated from the internet, making direct downloads impossible. In such environments, administrators need to manually transfer and install PowerShell modules to manage various tasks effectively.

Step-by-Step Guide

Step 1: Prepare the PowerShell Module for Offline Use

The first script, PowerShellModuleOfflinePackager.ps1, downloads the required PowerShell module and its dependencies from an internet-connected machine, then packages them into a zip file for easy transfer.

Here’s the script (Get it on Github):

<#
.SYNOPSIS
Downloads the most current version of a PowerShell NuGet package or a specified version and extracts it for manual installation.

.DESCRIPTION
This script downloads the specified or latest version of a NuGet package from the PowerShell Gallery and extracts it to a specified or default output directory. 
If no version is specified, the latest version of the package is downloaded. If no output directory is specified, the script 
creates a default output folder in the script's directory. After extracting the packages, the script can optionally compress 
the package folders into a single ZIP file for easy transfer to an offline system. If the zipping is done, it also deletes the 
folders created by Save-Package unless the SkipZip parameter is specified.

.PARAMETER Name
The ID of the NuGet package to download.

.PARAMETER Version
The version of the NuGet package to download. If not specified, the latest version will be downloaded.

.PARAMETER OutputDir
The directory where the package will be downloaded and extracted. If not specified, a folder named 'NuGetPackages' will be 
created in the directory where the script is being run.

.PARAMETER SkipZip
If specified, the script will not compress the package folders into a ZIP file.

.EXAMPLE
.\PowerShellModuleOfflinePackager.ps1 -Name "PSReadline"

Downloads the latest version of the PSReadline package, extracts it to the default output directory, and compresses the extracted files into a ZIP file.

.EXAMPLE
.\PowerShellModuleOfflinePackager.ps1 -Name "PSReadline" -Version "2.2.0"

Downloads version 2.2.0 of the PSReadline package, extracts it to the default output directory, and compresses the extracted files into a ZIP file.

.EXAMPLE
.\PowerShellModuleOfflinePackager.ps1 -Name "PSReadline" -OutputDir "C:\MyPackages"

Downloads the latest version of the PSReadline package, extracts it to C:\MyPackages, and compresses the extracted files into a ZIP file.

.EXAMPLE
.\PowerShellModuleOfflinePackager.ps1 -Name "PSReadline" -Version "2.2.0" -OutputDir "C:\MyPackages"

Downloads version 2.2.0 of the PSReadline package, extracts it to C:\MyPackages, and compresses the extracted files into a ZIP file.

.EXAMPLE
.\PowerShellModuleOfflinePackager.ps1 -Name "PSReadline" -SkipZip

Downloads the latest version of the PSReadline package, extracts it to the default output directory, but does not compress the extracted files into a ZIP file.
#>

param (
    [Parameter(Mandatory = $true, HelpMessage = "The ID of the NuGet package to download.")]
    [string]$Name,

    [Parameter(HelpMessage = "The version of the NuGet package to download. If not specified, the latest version will be downloaded.")]
    [string]$Version,

    [Parameter(HelpMessage = "The directory where the package will be downloaded and extracted. If not specified, a folder named 'NuGetPackages' will be created in the directory where the script is being run.")]
    [string]$OutputDir = "$PSScriptRoot",

    [Parameter(HelpMessage = "If specified, the script will not compress the package folders into a ZIP file.")]
    [switch]$SkipZip
)

$packageDir = "$($OutputDir)\NuGetPackages"

# Create the output directory if it doesn't exist
if (-Not (Test-Path -Path $PackageDir)) {
    New-Item -ItemType Directory -Path $PackageDir -Force
}

# If no version is specified, find the latest version
if (-Not $Version) {
    Write-Output "Fetching the latest version of $Name from the PowerShell Gallery..."
    $latestPackage = Find-Package -Name $Name -Source PSGallery | Sort-Object -Property Version -Descending | Select-Object -First 1
    if ($latestPackage) {
        $Version = $latestPackage.Version
        Write-Output "Latest version of $Name is $Version"
    } else {
        Write-Error "Package $Name not found in the PowerShell Gallery."
        exit 1
    }
}

# Download and extract the specified version of the package
Write-Output "Downloading and extracting $Name version $Version..."
Save-Package -Name $Name -RequiredVersion $Version -Path $packageDir -Source PSGallery

# Compress the extracted package folders into a single ZIP file, if SkipZip is not specified
if (-Not $SkipZip) {
    $zipFileName = "$Name-$Version.zip"
    $zipFilePath = Join-Path -Path $OutputDir -ChildPath $zipFileName

    Write-Output "Compressing the package folders into $zipFilePath..."
    Compress-Archive -Path "$packageDir\*" -DestinationPath $zipFilePath -Force

    Write-Output "Packages compressed into $zipFilePath"

    # Remove the extracted folders after zipping
    Write-Output "Cleaning up extracted folders..."
    Remove-Item -Path $packageDir -Recurse -Force

    Write-Output "Cleanup complete."
} else {
    Write-Output "Skipping compression and cleanup of the package folders."
}

Example Usage:

.\PowerShellModuleOfflinePackager.ps1 -Name "Microsoft.PowerApps.Administration.PowerShell" -OutputDir "C:\ModulePackages"

Step 2: Transfer the Zip File to the Air-Gapped or Firewalled System

Copy the generated zip file and the second script, ExtractModuleToDirectory.ps1, to the air-gapped or firewalled system using a USB drive or other secure method.

Step 3: Extract and Install the PowerShell Module

The second script, ExtractModuleToDirectory.ps1, extracts the PowerShell module from the zip file to a specified directory within the PSModulePath environment variable.

Here’s the script (Get it on Github):

<#
.SYNOPSIS
Extracts a PowerShell module ZIP file to a selected PowerShell module directory.

.DESCRIPTION
This script is used on an offline system to extract the contents of a PowerShell module ZIP file to a specified PowerShell module directory. 
The script lists all the available PowerShell module directories, allows the user to select one, and then extracts the ZIP file contents to that directory. 
If the -Force parameter is specified, existing files will be overwritten.

.PARAMETER ZipFilePath
The path to the ZIP file containing the PowerShell module.

.PARAMETER Force
If specified, existing files in the target directory will be overwritten.

.EXAMPLE
.\ExtractModuleToDirectory.ps1 -ZipFilePath "C:\Modules\PSReadline-2.2.0.zip"

Lists available PowerShell module directories, prompts the user to select one, and extracts the contents of PSReadline-2.2.0.zip to the selected directory.

.EXAMPLE
.\ExtractModuleToDirectory.ps1 -ZipFilePath "C:\Modules\PSReadline-2.2.0.zip" -Force

Lists available PowerShell module directories, prompts the user to select one, and extracts the contents of PSReadline-2.2.0.zip to the selected directory, overwriting existing files if they already exist.
#>

param (
    [Parameter(Mandatory = $true, HelpMessage = "The path to the ZIP file containing the PowerShell module.")]
    [string]$ZipFilePath,

    [Parameter(HelpMessage = "If specified, existing files in the target directory will be overwritten.")]
    [switch]$Force
)

# Function to get all available PowerShell module directories
function Get-ModulePaths {
    $modulePaths = $env:PSModulePath -split ';'
    return $modulePaths
}

# Function to display a menu and get user selection
function Show-Menu {
    param (
        [string[]]$MenuItems
    )

    for ($i = 0; $i -lt $MenuItems.Length; $i++) {
        Write-Host ("[{0}] {1}" -f ($i + 1), $MenuItems[$i])
    }

    $selection = Read-Host "Please select a directory (enter the number)"
    return [int]$selection - 1
}

# Verify that the ZIP file exists
if (-Not (Test-Path -Path $ZipFilePath)) {
    Write-Error "The ZIP file '$ZipFilePath' does not exist."
    exit 1
}

# Get available PowerShell module directories
$modulePaths = Get-ModulePaths

# Display the menu

 and get user selection
Write-Output "Available PowerShell module directories:"
$selectionIndex = Show-Menu -MenuItems $modulePaths

# Validate the selection
if ($selectionIndex -lt 0 -or $selectionIndex -ge $modulePaths.Length) {
    Write-Error "Invalid selection. Exiting."
    exit 1
}

$targetDir = $modulePaths[$selectionIndex]

# Ensure the target directory exists
if (-Not (Test-Path -Path $targetDir)) {
    Write-Output "The directory '$targetDir' does not exist. Creating it..."
    New-Item -ItemType Directory -Path $targetDir -Force
}

# Extract the ZIP file to the selected directory
Write-Output "Extracting the contents of '$ZipFilePath' to '$targetDir'..."
try {
    Add-Type -AssemblyName System.IO.Compression.FileSystem
    [System.IO.Compression.ZipFile]::ExtractToDirectory($ZipFilePath, $targetDir, $Force)
    Write-Output "Extraction complete."
} catch {
    Write-Error "An error occurred while extracting the ZIP file: $_"
    if ($_.Exception.Message -match "already exists") {
        Write-Output "Consider using the -Force parameter to overwrite existing files."
    }
}

Example Usage:

.\ExtractModuleToDirectory.ps1 -ZipFilePath "D:\ModulePackages\Microsoft.PowerApps.Administration.PowerShell.zip" -Force

Practical Example: Limiting Power Platform Developer Environments

With the PowerShell module installed, you can manage Power Platform environments, including restricting the creation of developer environments. This example demonstrates how to connect to Power Platform and disable developer environments using the installed PowerShell module.

Connect to Power Platform

For firewalled systems, specify the appropriate -Environment parameter to connect to your Power Platform environment. For government cloud environments, adjust the -Endpoint parameter accordingly (e.g., usgov for GCC Moderate, usgovhigh for GCC High, or dod for GCC DOD).
```
Add-PowerAppsAccount -Endpoint "usgov"
```

Disable Developer Environments

Use the following command to disable the creation of developer environments:

$requestBody = [pscustomobject]@{
    powerPlatform = [pscustomobject]@{
        governance = [pscustomobject]@{
            disableDeveloperEnvironmentCreationByNonAdminUsers  = $true
        }
    }
}

Set-TenantSettings -RequestBody $requestBody

This configuration helps maintain control over the Power Platform environments, ensuring that only authorized users can create developer environments.

Conclusion

Managing PowerShell modules in firewalled or air-gapped systems requires additional steps, but with the right tools, it can be efficiently achieved. By packaging and transferring PowerShell modules offline, you can maintain the security of your environment while still being able to manage it effectively. The provided scripts simplify this process, ensuring that your isolated systems remain functional and secure. Additionally, the practical example of managing Power Platform environments demonstrates how to apply these techniques to real-world scenarios.

Syncing Azure DevOps Work Item Status to Microsoft Dataverse with Dataflows

2024-03-08T15:48:00.002-05:00

In today’s fast-paced development environments, seamless integration between customer and development tracking systems can be crucial for maintaining transparency, efficiency, and alignment across teams. Our objective centers around a scenario faced by some organizations: synchronizing customer requirements captured by staff in Microsoft Dataverse with the development work tracked in Azure DevOps (ADO).

Business Case and Goals

Our service teams interact with customers to capture requirements, which are then stored in Microsoft Dataverse. As development plans are formulated, corresponding ADO items are created for each requirement. The primary goals of our integration efforts are:

Visibility for Service Staff: Enable service teams to view the status of the ADO items associated with customer requirements directly within Dataverse. This integration aims to eliminate the need for service staff to navigate away from their primary system to check development progress, fostering a more efficient and cohesive workflow.
Enhanced Reporting Capabilities: By syncing ADO item statuses with Dataverse, we unlock the potential for advanced querying capabilities within Dataverse. This allows for the creation of detailed reports and analytics on development progress, directly correlating customer requirements with development statuses.
Streamlined Operations: The integration ensures that information flow between customer service and development teams is automated and streamlined. This not only saves time but also reduces the potential for errors in tracking and reporting on the progress of development work against customer requirements.

By achieving these goals, we aim to enhance the operational efficiency of our teams, improve the accuracy of our reporting, and ultimately deliver a better service experience to our customers. The following sections detail the technical journey we embarked on to realize this integration, navigating through authentication challenges, API limitations, and leveraging Power BI Dataflows as a creative solution to synchronize ADO Work Items with Microsoft Dataverse.

The Challenge

Our objective to synchronize specific ADO Work Item fields with a Dataverse table for enriched reporting introduced a multifaceted set of challenges:

Authentication with Personal Access Token (PAT): Although ADO supports various authentication methods, our scenario necessitated the use of a PAT for its flexibility and security. Integrating this with Dataverse Dataflows presented an initial obstacle, as these dataflows do not natively support basic authentication, which is essential when using PATs.
Inconsistent Behavior in Authentication: Initial attempts to directly set the Authorization header in the Web.Contents function and configure the connection as Anonymous were met with errors, reporting incorrect credentials. This issue underscored the subtle complexities of handling authentication within Power BI and Dataverse integrations.
Challenges with Data Source Configuration: Adding a new data source via the Web API connector and attempting to use the PAT solely in the password field (leaving the username empty) resulted in invalid credentials errors. However, starting with a blank query and then incorporating the Web.Contents function with Basic authentication—using only the PAT for authentication—eventually proved successful. This discovery process highlighted the trial and error involved in establishing a viable authentication method.
API Limitations: The wit/workitemsbatch endpoint, ideal for our purposes, does not support POST requests when authenticating with basic authentication via the Web.Contents function in M code. This limitation, along with a constraint on processing only 200 records at a time, required a strategic approach to batch processing and API requests.
Absence of a Direct Azure DevOps Connector in Dataflows: Unlike Power BI Desktop, Dataflows lacks an out-of-the-box connector for Azure DevOps, adding an extra layer of complexity. This required us to utilize the Azure DevOps REST API, a robust yet initially daunting interface for those unfamiliar with its intricacies. Learning to navigate and effectively leverage the REST API took time but was essential for achieving our integration goals.

For comprehensive guidance on utilizing the Azure DevOps REST API, including accessing work items, repositories, and other essential services, refer to the official API documentation: Azure DevOps Services REST API Reference.

The Solution

Despite these obstacles, a solution was crafted through a series of steps, leveraging Power BI Dataflows as an intermediary to handle the data transformation and syncing process.

Step 1: Create and Configure the PAT

The initial and crucial step in syncing Azure DevOps (ADO) Work Item states with Microsoft Dataverse involves creating a Personal Access Token (PAT) within Azure DevOps. This PAT serves as the authentication mechanism for accessing ADO’s APIs securely.

Here’s a step-by-step guide to ensure your PAT is properly configured:

Navigate to Azure DevOps: Go to your Azure DevOps organization’s user settings.
Access Security: Find and click on the “Personal access tokens” option under the security settings.
Create New Token: Select “New Token.” Ensure you provide it with a descriptive name that clearly indicates its usage, such as “DataverseSync.”
Set Expiry: Choose an appropriate expiry date for the token according to your project duration and security policies.
Assign Scopes: Assign the necessary scopes for the PAT. For this integration, you must at least include permissions to read Work Items. If your integration requires accessing other ADO API endpoints, make sure to include those permissions as well.

For a detailed walkthrough on creating a PAT in Azure DevOps, refer to the official documentation available at Create Personal Access Tokens to authenticate access.

Remember, the PAT is sensitive and should be securely stored. It provides direct access to your Azure DevOps services and should only be shared with trusted individuals and applications.

Step 2: Implement Power BI Dataflow

After securing your PAT, the next step is to establish a Power BI dataflow that will serve as the intermediary for transferring and transforming data between Azure DevOps (ADO) and Microsoft Dataverse. This involves creating custom functions within Power BI to handle data batching and API calls. Follow these detailed instructions to set up your Power BI dataflow:

Creating a New Blank Query for Batching

Open Power BI Desktop: Start by opening Power BI Desktop and navigating to the Data view.
Create a New Query: Go to the Home tab, click on “Transform Data,” and then select “Data Source Settings.” Here, choose to create a new blank query by selecting “New Source” > “Blank Query.”
Enter the SplitListIntoBatch Function Code: In the query editor that opens, enter the following M code to create the SplitListIntoBatch function. This function is designed to split your data into smaller batches for processing.

// Function to split a list into smaller lists of a given size
(list as list, batchSize as number) as list =>
let
    // Calculate the number of batches
    numBatches = Number.RoundUp(List.Count(list) / batchSize),
    // Generate a list of batches
    batches = List.Transform(
        {0..numBatches - 1}, each List.Skip(list, _ * batchSize) & List.FirstN(list, batchSize)
    )
in
    batches

Name Your Query: Rename the query to SplitListIntoBatch by right-clicking on the query name in the left pane and selecting “Rename.”

Creating the Function for Azure DevOps API Calls

Add Another Blank Query: Repeat the steps to add a new blank query, this time for making API calls to Azure DevOps.
Enter the GetWorkItems Function Code: In the new blank query, copy the following M code to create the GetWorkItems function. Adjust the adoOrganization and adoProject variables as necessary for your Azure DevOps instance.

(ids as list) as table =>
let
    // Convert the list of IDs to a comma-separated string
    idsString = Text.Combine(List.Transform(ids, Text.From), ","),

    // Specify the fields to retrieve
    fields = "System.Title,System.State",
    
    //ADO Organization Name
    adoOrganization = "PowerAppsRAW",

    //ADO Project Name
    adoProject = "My%20Project",

    // Set up API URL and headers
    apiUrl = "https://dev.azure.com/" & adoOrganization & "/" & adoProject & "/_apis/wit/workitems",
    headers = [
        #"Content-Type" = "application/json"
    ],
    
    // Make the Get request
    Source = Json.Document(Web.Contents(apiUrl, [
        Headers = headers,
        Query = [
            #"api-version" = "7.1",
            ids = idsString,
            fields = fields
        ]
    ])),
    
    // Convert 'value' array to a table
    workItemsTable = Table.FromList(Source[value], Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    
    // Expand the top-level properties of each work item
    expandedTable = Table.ExpandRecordColumn(workItemsTable, "Column1", {"id", "rev", "fields", "url"}, {"ID", "Revision", "Fields", "URL"}),
    
    // Dynamically expand all fields within the 'Fields' column
    // Get the list of field names dynamically from the first row (assuming consistency across rows)
    fieldNames = Record.FieldNames(expandedTable{0}[Fields]),
    fullyExpandedTable = Table.ExpandRecordColumn(expandedTable, "Fields", fieldNames, fieldNames)
in
    fullyExpandedTable

Name Your Query: This time, rename the query to GetWorkItems to reflect its purpose.

After completing these steps, you’ve successfully created the necessary functions within your Power BI Dataflow. These functions will allow you to batch process your Dataverse data and fetch updated work item information from Azure DevOps, respectively.

Step 3: Efficiently Combining Data for Synchronization

As we proceed to optimize our synchronization process, it’s important to address the configuration needed when actually running the query. This step ensures that we only query Azure DevOps for updates on ADO item IDs present in our Dataverse environment, significantly reducing Azure API usage. Below, we’ve provided sample code and now include essential guidance on configuring the connection for the Azure DevOps (ADO) URL.

Efficient Data Fetching and Processing

Initiate Data Retrieval: Start the process by creating a new query for the new_customerrequirement table in Dataverse to identify the specific ADO item IDs that require updates. This step determines the scope of our synchronization efforts.
Apply Batch Processing: Utilize the SplitListIntoBatch function to divide the list of relevant IDs into smaller batches. This approach is crucial for managing API call volume and adhering to Azure DevOps API rate limits.
Fetch Updated Work Item Information: With each batch, invoke the GetWorkItems function to fetch the current status and other pertinent details for the ADO items. Targeting only the IDs identified earlier ensures that our data retrieval is as efficient as possible.

let
    // Connect to dataverse
    Source = CommonDataService.Database("12345.crm.microsoftdynamics.com", [CreateNavigationProperties = true]),
    // Navigate to our table
    #"Navigation 1" = Source{[Shcema="dbo", Item="new_customerrequirement"]}[Data],
    // We will drill down just to the new_adoitemid field so we can pass the entire list to the next step
    #"Drill Down" = #"Navigation 1"[new_adoitemid],
    // Split the list into batches of 200
    #"Batch Items" = SplitListIntoBatches(#"Drill Down", 200),
    // Get the work items for each batch from the Azure DevOps API
    #"Get Work Items" = List.Transform(#"Batch Items", each GetWorkItems(_)),
    // Combine the results into a single table
    #"Combined Results" = Table.Combine(#"Get Work Items")
in
    #"Combined Results"

Running the Query and Configuring the Connection

After setting up your query with the provided sample code, executing the query will prompt you to configure the connection settings for accessing Azure DevOps. Here’s how to accurately set up the connection:

Prompt for Connection Settings: When you attempt to run the query for the first time, Power BI will prompt you to specify how to connect to the Azure DevOps URL. This is a crucial step to ensure secure and successful data retrieval.
Set Authentication Type: In the dialog box that appears, you’ll need to set the authentication method for the ADO URL connection. Choose “Basic” as the authentication method. This selection is necessary to use your Personal Access Token (PAT) for authentication.
Configure Username and Password: For the username field, leave it blank. The PAT does not require a username to be specified. In the password field, paste in the PAT that you created earlier. Your PAT acts as the password, providing secure access to Azure DevOps data based on the permissions you’ve set when creating the token.
Save and Proceed: After configuring the authentication settings, save your changes and proceed with running the query. This setup should allow Power BI to securely fetch the required data from Azure DevOps using your PAT, enabling the data transformation and syncing process to proceed.

Finalizing the Data Preparation

With the connection properly configured, you can efficiently combine the data fetched from Azure DevOps with the records in your Dataverse new_customerrequirement table. This process prepares the synchronized dataset for the final step of updating Dataverse records, ensuring that only relevant and updated ADO item information is processed and prepared for synchronization.

Step 4: Creating a Dataverse Dataflow and Linking to Power BI Dataflow Results

After setting up your Power BI dataflow to fetch and process ADO item statuses, the final step involves creating a dataflow within Microsoft Dataverse. This Dataverse dataflow will utilize the Dataflows connector to connect to your Power BI dataflow, allowing you to synchronize and update the new_customerrequirement table with the latest ADO item statuses. Here’s how to accomplish this:

Creating a Dataverse Dataflow

Navigate to Power Apps: Start by going to the Power Apps portal and selecting your environment.
Access Dataflows: From the left navigation pane, choose “Data” and then “Dataflows” to access the dataflows section.
Create New Dataflow: Click on “New dataflow” and then select “Start from blank” to begin the creation process.

Connecting to Power BI Dataflow

Use the Dataflows Connector: Within the dataflow creation process, select “Add data” and then choose the “Power BI dataflows” connector. This allows you to connect directly to the data processed by your Power BI dataflow.
Authenticate and Select Your Dataflow: Authenticate as necessary and select the Power BI dataflow you created earlier, which contains the ADO item statuses.

Linking and Updating the `new_customerrequirement` Table

Combine IDs with Power BI Dataflow Results: With the data from your Power BI dataflow now accessible within Dataverse, the next step is to link this data with the corresponding IDs in the new_customerrequirement table. This involves matching ADO item IDs from Power BI dataflow results with those stored in Dataverse to ensure accurate updates.
Update Table with ADO Item Statuses: Finally, utilize the linked information to update the new_customerrequirement table, specifically the fields related to ADO item statuses. This ensures that the service staff can view the current status of development work directly within Dataverse, without needing to access Azure DevOps.

Finalizing the Integration

Test and Validate: It’s essential to test the dataflow to ensure that data is being correctly updated in the new_customerrequirement table. Validate the integration by checking if the ADO item statuses in Dataverse accurately reflect those in Azure DevOps.
Schedule Refreshes: To maintain up-to-date information, schedule regular refreshes of your Dataverse dataflow. This ensures that the service staff always has the latest status updates at their disposal.

By following these steps, you complete the integration cycle, effectively bridging Azure DevOps and Microsoft Dataverse. This enables your organization to streamline operations, enhance reporting capabilities, and provide your service staff with the visibility needed to offer informed customer support.

An Alternative Approach: Single Query Execution

While the method described above leverages functions within Power BI Dataflows to batch process and synchronize data, it’s crucial to note that there is an alternative strategy that does not require premium capacity. This alternative involves consolidating the entire process into a single query, thereby avoiding the creation of a computed table which necessitates a Power BI workspace with premium capacity.

Benefits of a Single Query Approach

Cost Efficiency: By avoiding the need for premium capacity, this method can be more cost-effective, especially for organizations looking to optimize their use of Power BI and Azure resources.
Simplicity: Consolidating the process into a single query can simplify the dataflow, making it easier to manage and troubleshoot.
Performance: A single query approach might also offer performance benefits by reducing the complexity and potential overhead introduced by multiple function calls and batch processing.

Considerations

It’s important to weigh the benefits against potential limitations, such as the complexity of crafting a single, comprehensive query that can handle all necessary operations efficiently. Additionally, while this approach avoids the need for premium capacity, it still requires careful planning around API rate limits and the handling of large datasets.

Implementation

Implementing this strategy requires a deep understanding of the Power Query M language and the ability to effectively leverage the Web.Contents function, query parameters, and data transformation capabilities within a single query block. This approach might look something like the following:

let
    // Connect to dataverse
    Source = CommonDataService.Database("12345.crm.microsoftdynamics.com", [CreateNavigationProperties = true]),
    
    // Navigate to our table
    #"Navigation 1" = Source{[Shcema="dbo", Item="new_customerrequirement"]}[Data],
    
    // We will drill down just to the new_adoitemid field so we can pass the entire list to the next step
    #"Drill Down" = #"Navigation 1"[new_adoitemid],
    
    // Define the batch size
    BatchSize = 200,

    // Split the list into batches
    NumBatches = Number.RoundUp(List.Count(#"Drill Down") / BatchSize),
    Batches = List.Transform({0..NumBatches - 1}, each List.Skip(#"Drill Down", _ * BatchSize) & List.FirstN(#"Drill Down", BatchSize)),

    // Function to fetch work items for a batch of IDs (inline definition)
    GetWorkItemsForBatch = (ids as list) as table =>
        let
            idsString = Text.Combine(List.Transform(ids, Text.From), ","),
            fields = "System.Title,System.State",
            adoOrganization = "PowerAppsRAW",
            adoProject = "My%20Project",
            apiUrl = "https://dev.azure.com/" & adoOrganization & "/" & adoProject & "/_apis/wit/workitems",
            headers = [#"Content-Type" = "application/json"],
            Source = Json.Document(Web.Contents(apiUrl, [
                Headers = headers,
                Query = [
                    #"api-version" = "7.1",
                    ids = idsString,
                    fields = fields
                ]
            ])),
            workItemsTable = Table.FromList(Source[value], Splitter.SplitByNothing(), null, null, ExtraValues.Error),
            expandedTable = Table.ExpandRecordColumn(workItemsTable, "Column1", {"id", "rev", "fields", "url"}, {"ID", "Revision", "Fields", "URL"}),
            fieldNames = if Table.IsEmpty(expandedTable) then {} else Record.FieldNames(expandedTable{0}[Fields]),
            fullyExpandedTable = Table.ExpandRecordColumn(expandedTable, "Fields", fieldNames, fieldNames)
        in
            fullyExpandedTable,

    // Fetch work items for each batch and combine results
    FetchResults = List.Transform(Batches, each GetWorkItemsForBatch(_)),
    CombinedResults = Table.Combine(FetchResults)
in
    CombinedResults

This illustrative example simplifies the process into a single, cohesive query, demonstrating the potential to streamline the integration without relying on premium features.

Overcoming Obstacles

This journey wasn’t without its trials, particularly around authentication and the nuances of the M language for dataflows. The solution required creative thinking, such as using Power BI dataflows as a workaround for Dataverse’s authentication limitations and meticulously crafting M code to interact with the ADO API within its constraints.

Conclusion

By bridging Azure DevOps and Microsoft Dataverse with Power BI Dataflows, we’ve established a robust process for syncing work item updates for enhanced reporting and insight into project requirements. This solution not only addresses the immediate need but also offers a template for similar challenges, showcasing the flexibility and power of Microsoft’s ecosystem when it comes to custom integrations.

Govee and Power Platform: Transforming Smart Lighting Automation

2024-03-06T15:20:00.002-05:00

Introduction

In the evolving world of smart home technology, the integration of lighting solutions with sophisticated automation platforms is gaining significant traction. Our focus in this discussion is the Govee Lights Power Automate Connector, a pioneering tool that brings together the advanced capabilities of Govee’s smart lighting with the robust automation features of Microsoft Power Automate.

For those interested in exploring the full capabilities of this connector, including setup instructions, usage scenarios, and technical specifications, detailed information is available on the official Microsoft documentation page. You can access it here: Govee Connector for Power Automate.

This connector not only exemplifies the practical application of integrating smart devices with automation platforms but also opens up new possibilities for enhancing home and office environments through intelligent lighting control.

Practical Integration for Advanced Home Automation

The Govee Lights Power Automate Connector provides a pragmatic solution for controlling Govee smart lights through the Power Automate platform. By utilizing the Govee Developer API, this connector enables a straightforward yet sophisticated approach to lighting automation.

Building the Connector: Technical Insights into the Govee API

The development of the Govee Lights Power Automate Connector required a detailed understanding of the Govee API. This process was facilitated by two key resources:

Postman Quickstart Guide for Govee: This guide provides a comprehensive starting point for anyone looking to understand and use the Govee API. It is especially useful for beginners or those not familiar with API interactions. The guide walks you through the basic steps of setting up API requests and testing them in Postman, making it an invaluable resource for getting up to speed quickly. Access the guide here: Postman Quickstart for Govee.
Govee Developer API Reference: For a more in-depth exploration, the official Govee Developer API Reference is the go-to document. It contains detailed information on the API’s capabilities, parameters, response structures, and more. This comprehensive document is essential for developers looking to fully exploit the features of the Govee API in their applications. You can find the official documentation here: Govee Developer API Reference.

Using these resources, we were able to effectively understand and harness the full potential of Govee’s API, paving the way for the creation of this connector. This knowledge allowed us to build a tool that can interact seamlessly with Govee lights, providing users with an enhanced level of control and automation possibilities.

Key Features

Direct Device Control: The connector allows users to manage their Govee lights through Power Automate, offering functionalities like brightness adjustment and color change.
Device Information Access: This feature provides essential information about Govee devices, such as the MAC address, model, and supported properties. It’s vital for identifying the specific requirements to execute commands on the devices.

Utilizing Power Automate for Smarter Lighting Control

The integration of this connector with Power Automate’s automation tools enhances the control and customization of home lighting. It allows for the efficient management of lights as part of a broader automated home system.

Application Example: Device Information Retrieval and Command Execution

Consider a practical application of this integration:

Retrieve Device Information: Create a flow in Power Automate to gather the necessary details about your Govee devices.
Process and Implement Data: Use this information to prepare and structure the commands for the intended devices.
Command Execution: Utilize this setup to execute desired actions, such as turning lights on or off and adjusting brightness.
Refine and Integrate Your Automation: Test and modify your flow for optimal performance, potentially incorporating other smart automation triggers.

Examples of Power Automate Triggers for Govee Lights

Time-Based Lighting Control:
- Trigger: Schedule (Time-based trigger in Power Automate).
- Action: Automatically dim Govee lights in the evening and brighten them in the morning.
Calendar Event-Driven Lighting:
- Trigger: Calendar event start (Outlook Calendar trigger).
- Action: Change Govee light colors for different types of events.
Email Notification Response:
- Trigger: New email from a specific sender (Email trigger in Power Automate).
- Action: Flash or change the color of Govee lights for priority emails.
Weather-Based Lighting Adjustments:
- Trigger: Weather forecast change (Weather connectors or APIs).
- Action: Adjust light brightness or color temperature in response to weather.
IoT Device Interaction:
- Trigger: IoT device status change (Smart home IoT triggers).
- Action: Modify Govee light brightness with changes in room temperature.
Social Media Alerts:
- Trigger: New social media mention or message (Social media triggers).
- Action: Change light color or pattern for notifications.
Motion-Triggered Lighting:
- Trigger: Motion sensor activation (Smart home motion sensors).
- Action: Turn on or adjust Govee lights when motion is detected.
Custom User Input:
- Trigger: Button press on a mobile app or physical button (Power Automate Button trigger).
- Action: Switch Govee lights to a pre-set scene or color palette.

Conclusion

The Govee Lights Power Automate Connector represents a focused approach to enhancing home lighting systems through automation. It stands as a practical example of how smart technology can be effectively integrated into daily life using tools like Microsoft Power Automate.

Explore the functionality and benefits of this connector for a more efficient and tailored smart home lighting experience.

Step-by-Step to Success: Run AutoGPT using Azure OpenAI on Docker

2024-02-29T11:47:00.001-05:00

Integrating AutoGPT with Azure OpenAI through Docker offers a direct path to unlocking advanced AI capabilities. This detailed guide not only walks through the initial setup and configuration steps but also emphasizes the critical adjustments required for effective Azure OpenAI integration. Let’s dive into a more focused and informative discussion on setting up AutoGPT and ensuring it works seamlessly with Azure OpenAI services.

What is AutoGPT?

AutoGPT is like having a smart robot buddy that helps you achieve a specific goal by chatting with a super smart AI, kind of like having a conversation with a genius friend. Here’s how it works, broken down really simply:

You Set a Goal: Imagine you have a goal, like planning a surprise birthday party or learning about space. You tell this to your robot buddy.
The Robot Starts the Chat: Your robot buddy kicks things off by asking the first question to the genius AI, aiming to get information or ideas related to your goal.
Listening and Thinking: After getting an answer, the robot thinks about it, figures out if it’s helpful, and what to ask next to get closer to your goal.
Asking More Questions: Based on what the genius AI says, the robot keeps the conversation going, asking more questions to dig deeper or get more specific information, all aimed at reaching your goal.
Goal Achieved: This back-and-forth chat continues until your robot buddy has gathered enough info or ideas to help you meet your goal, like having a full plan for that surprise party or a good understanding of space.

In short, AutoGPT is like a helpful middleman between you and a super-smart AI, doing all the talking and thinking for you, so you don’t have to come up with what to ask next. It makes getting to your goal easier by handling the conversation, making sure everything stays on track.

Detailed Configuration Steps for Integrating AutoGPT with Azure OpenAI

Initial Setup

Install Docker
Fork and Clone the AutoGPT Repository: Begin by forking the AutoGPT repository on GitHub and cloning it to your local machine, for instance, at C:\Auto-GPT.

Configuration

Environment Setup:
- Copy the .env.template file from C:\Auto-GPT\autogpts\autogpt to the primary folder C:\Auto-GPT and rename it to .env.
- Edit the .env file, setting USE_AZURE=True to enable Azure OpenAI integration. Ensure True is capitalized to avoid issues.
API Key Configuration:
- Update the OPENAI_API_KEY in the .env file with your Azure OpenAI API key, found in the Azure portal under your OpenAI service’s “Keys and Endpoints”.

Docker and Azure YAML Setup:

Copy the azure.yaml.template file to C:\Auto-GPT rename it to azure.yaml we will adjust it later according to our Azure OpenAI service details.

Create a docker.compose.yml file in C:\Auto-GPT using the Docker setup template from the AutoGPT documentation. Add the following line to the volumes section to prevent the app/azure.yaml file not found error:

Volume Sample:

volumes:
  - ./azure.yaml:/app/azure.yaml

Entire docker-compose:

 version: "3.9"
 services:
   auto-gpt:
     image: significantgravitas/auto-gpt
     env_file:
       - .env    
     ports:
       - "8000:8000"  # remove this if you just want to run a single agent in TTY mode
     profiles: ["exclude-from-up"]
     volumes:
       - ./data:/app/data
       ## allow auto-gpt to write logs to disk
       - ./logs:/app/logs
       ## allow auto-gpt to read the azure yaml file
       - ./azure.yaml:/app/azure.yaml
       ## uncomment following lines if you want to make use of these files
       ## you must have them existing in the same folder as this docker-compose.yml
       #- type: bind
       #  source: ./ai_settings.yaml
       #  target: /app/ai_settings.yaml
       #- type: bind
       #  source: ./prompt_settings.yaml
       #  target: /app/prompt_settings.yaml

Azure AI Models Deployment

Deploy Azure AI Models:
- Use Azure AI Studio to deploy necessary models like gpt-4 and gpt-3.5-turbo-text-embedding-ada-002, setting deployment names to match the model names for simplicity.

Final Adjustments

Modify the azure.yaml File:
- Set azure_api_type to azure, ensuring the use of the API key for authentication. If you want to use Azure AD you can set the parameter to azure_ad. This will also require that you use an auth token as your OPENAPI_API_KEY. Instructions on how to obtain this token can be found in How to Configure AutoGPT with Azure OpenAI Active Directory Managed Identity.
- The azure_api_base and azure_api_version was determined using the Azure AI Studio’s chat playground “View code” feature.
- For azure_model_map, an iterative approach was taken. Initially, no mappings were specified. After running the Docker command, errors indicating missing models were used to gradually populate this section with the correct model mappings. This process involved mapping the AutoGPT’s expected model names to the corresponding deployment names in Azure AI Studio.
Complete azure.yaml file.
```
azure_api_type: azure
azure_api_base: https://rawopenai.openai.azure.com/
azure_api_version: 2024-02-15-preview
azure_model_map:
    gpt-3.5-turbo-16k: gpt-35-turbo
    gpt-4: gpt-4
    ext-embedding-3-small: text-embedding-ada-002
```

Execution

Running AutoGPT:
- Execute AutoGPT via Docker from the C:\Auto-GPT directory using the command docker compose run --rm auto-gpt. This step confirms the successful integration and functionality of AutoGPT with Azure OpenAI.

Conclusion

AutoGPT revolutionizes our interaction with AI by automating the conversation process, guiding us toward achieving specific goals with minimal effort. This transformative approach streamlines tasks ranging from content creation to complex data analysis, making it a versatile tool for anyone looking to leverage AI’s power. The simplicity of AutoGPT, coupled with its goal-oriented methodology, democratizes access to advanced AI capabilities, enabling users to focus on outcomes rather than getting bogged down in the technicalities of prompt engineering.

Through this article, we’ve provided a detailed blueprint for integrating AutoGPT with Azure OpenAI, ensuring you have the knowledge to harness this innovative technology effectively. Whether you’re a seasoned developer or new to the world of AI, the step-by-step guide laid out here is designed to empower you to implement AutoGPT within the Azure ecosystem successfully. Embracing AutoGPT opens up a realm of possibilities, allowing you to push the boundaries of what you can achieve with AI, turning complex tasks into manageable, goal-driven projects.

Power Automate Blueprint Accessing Azure Portal Backend APIs and the Intricacies of main.iam.ad.ext.azure.com

2024-02-29T11:17:00.001-05:00

In the realms of digital infrastructure management, automation emerges as a pivotal ally, especially when confronting repetitive and time-sensitive tasks. A recent endeavor led me to a scenario where automating the management of OAuth tokens for users within our organization was paramount. Our meticulous record-keeping of these tokens and their respective assignments is handled through Power Apps. However, the manual aspect of adding these tokens via the Azure portal, which necessitated the upload of a CSV file each time, posed a cumbersome challenge.

Given the current preview status of this functionality, a straightforward method through Graph API was conspicuously absent. Thus, I aimed to devise an automated framework, enabling individuals with the appropriate permissions in Power Apps to seamlessly add these tokens for users. While this wouldn’t entirely absolve admins of their duties—they would still need to activate the tokens within the Azure portal—it significantly mitigated the manual labor involved in creating and uploading the CSV file.

My exploration revealed that during a manual CSV file upload, an API located at main.iam.ad.ext.azure.com is triggered in the background. However, this API is a closed door, accessible exclusively via the Azure portal and not through an app registration. This discovery beckoned a deeper dive to harness this API for automating the OAuth token management chore.

This trail led to the crafting of a solution using Power Automate, which is the focal point of this article. The scripts and methodologies delineated here draw inspiration and foundational knowledge from the following insightful articles:

While these articles unveiled a treasure trove of information, I envisioned extending this knowledge by showcasing how this automation could be embodied within Power Automate. This article, therefore, aspires to bridge that informational chasm and furnish a step-by-step guide on configuring the main.iam.ad.ext.azure.com API with Power Automate to automate OAuth token management, albeit it’s crucial to note that main.iam.ad.ext.azure.com is an unsupported API, which calls for a cautious approach in a production environment.

Service Account

Utilizing the main.iam.ad.ext.azure.com API requires a distinct approach for authentication due to its unsupported status and exclusive accessibility via the Azure portal. Unlike many Azure services, this API doesn’t support service principal access, which is a common method for non-interactive, automated access within Azure environments.

To navigate this, it’s advisable to create a dedicated service account with the precise permissions required to interact with the main.iam.ad.ext.azure.com API. This setup aligns with the principle of least privilege (PoLP), ensuring the account has only the necessary access rights, and facilitates easier auditing and monitoring of the automated processes.

Creating a service account is a good practice from a security standpoint. It not only provides a workaround for the lack of service principal support but also ensures that all actions carried out by the automation processes are traceable, thus enhancing the security and accountability of the setup.

Important Note on MFA and Token Lifetime: If your Azure environment has Multi-factor authentication (MFA) requirements set up through conditional access, it can affect the token lifetime for the service account. In scenarios where this becomes a hindrance to automation processes, you may need to consider excluding or adjusting the MFA requirements for the service account. However, always ensure that any changes made align with your organization’s security policies and best practices.

Initial Key Vault Setup

Azure Key Vault is a pivotal component in this setup, acting as a secure repository for storing and managing the refresh token needed for automation. It provides a centralized platform to safeguard sensitive data such as secrets, encryption keys, and certificates.

Resource Provider Registration

Before diving into scripts or deployment files, ensure that the necessary Azure resource providers are registered within your subscription, particularly Microsoft.Authorization and Microsoft.KeyVault' These providers are crucial for creating and managing the Key Vault and setting the necessary permissions.

Deployment Options

There are various avenues to set up the Key Vault – you can employ Azure PowerShell, Azure CLI, or Bicep files, among other methods. In this guide, both PowerShell scripts and Bicep files are provided to offer flexibility based on your preferences or environment constraints.

PowerShell Scripts

A script named 01-create-resources-powershell.ps1 is provided to facilitate the creation of a resource group and a Key Vault. It’s an executable script where you just need to replace the parameters with your environment details.

.\01-create-hidden-api-environment.ps1 -Location "eastus" -ResourceGroupName "hidden-resourcegroup" -KeyVaultName "hidden-kv" -TenantId "123e4567-e89b-12d3-a456-426614174000" -SubscriptionId "123e4567-e89b-12d3-a456-426614174001"

Bicep Files

Bicep is a declarative language for describing and deploying Azure resources. It simplifies the process of writing and managing Azure Resource Manager (ARM) templates. Bicep files offer a streamlined method to deploy resources directly from Visual Studio Code, provided you have the Bicep extension installed. The files included in this guide are structured to deploy a resource group and a Key Vault effortlessly.

What is great about these files is that you can easily deploy from within Visual Studio Code. Just install the Bicep extension.

Then right click on the bicep file to deploy. The main.parameters.json file can be used to supply all the necessary parameters for deployment.

Both deployment methods are designed to abstract the complexities involved in setting up Azure Key Vault, providing a straightforward path to secure the refresh token necessary for automation.

Azure Portal

Navigate to the Azure portal, then select “Create a resource.”
In the “Search the Marketplace” field, type “Key Vault” and select it from the list.
Click the “Create” button to open the “Create Key Vault” blade.
Fill in the necessary fields such as the Name, Subscription, Resource Group, and Location.
Review the other settings and adjust them according to your preferences or requirements.
Click the “Review + create” button, review your settings one final time, and then click “Create” to deploy the Key Vault.

Acquiring the Initial Refresh Token

For the automation to function seamlessly, acquiring a refresh token initially is crucial. This token will serve as the bridge for obtaining new access tokens, allowing the automation to interact with the main.iam.ad.ext.azure.com API securely.

A PowerShell script named 02-set-initial-refresh-token.ps1 is provided to facilitate this process. When executed, this script will prompt you to sign in twice. The first login should be performed with an account that possesses the requisite permissions to update the Key Vault. Following this, a second login prompt will appear. This is where you would log in using the service account created earlier, which has been configured with just the necessary rights to interact with the Azure API. This dual-login mechanism ensures that the process is securely handled, aligning with the principle of least privilege while setting the stage for automated interactions with the main.iam.ad.ext.azure.com API. The example below shows you how to run the script, replace the parameters with your environment details.

.\02-set-initial-refresh-token.ps1 -TenantId "123e4567-e89b-12d3-a456-426614174000" -SubscriptionId "123e4567-e89b-12d3-a456-426614174001" -KeyVaultName "hidden-kv"

Automation Framework Setup

Embarking on the automation of OAuth token management necessitates a structured setup within Power Automate and Azure. The steps below delineate the essential configurations:

Create App Registration

There’s no need to add any API roles.
Generate a new secret, copying the secret value for safekeeping, as it will be crucial when creating the connection for Key Vault in Power Automate.
Ensure to also copy the Application (Client) ID and the Directory (tenant) ID, which will be required for the connection within Power Automate.

Install the Azure Key Vault Custom Connectors

Either install the Azure Key Vault custom connectors solution AzurePortalAPICustomConnector_1_0_0_2, or follow the installation guidelines provided here. Utilizing a custom connector is vital since the certified connector for Key Vault doesn’t allow writing secrets back to the Key Vault, a feature requisite for this solution.

Update the host URL to reflect your new Key Vault.

Revise the security section:
Ensure the Enable Service Principal support option is selected.
Input the details copied from the app registration you created.
- For the authentication URL, use:
  - Azure Cloud: https://login.microsoftonline.com
  - Azure Government: https://login.microsoftonline.us
- For the Resource URL use:
  - Azure Cloud: https://vault.azure.net
  - Azure Government: https://vault.usgovcloudapi.net

Copy the Redirect URL from the security section, to be added within the app registration later.

Update App Registration

Incorporate the Power Automate redirect URL into the app registration.

Key Vault Access Management

Designate the app registration as a Key Vault Secrets Officer in Key Vault, ensuring the requisite permissions for reading and writing secrets are granted.

Power Automate Flow

The culmination of our setup is the creation of a Power Automate flow, designed to automate the process of managing OAuth tokens. Initially, the flow retrieves the existing refresh token from Key Vault, utilizes it to log in the service account, and upon a successful login, acquires a new refresh token alongside an access token. The new refresh token is then stored back in Key Vault, extending its lifespan, while the access token is employed to interact with the main.iam.ad.ext.azure.com API.

For a hands-on experience, you can download a solution containing the sample Flow from here: AzurePortalAPIFlowExample_1_0_0_4.zip.

Important Note: Ensure to secure all input and output parameters within any actions that may utilize the refresh token or access token to safeguard against unauthorized access.

Get Refresh Token

The journey begins with the ‘Get secret’ action, courtesy of our custom Key Vault connector, fetching the refresh token from Key Vault to set the stage for the login process.

Upon adding this action to your flow, select Service Principal Connection as the authentication type, and furnish the details from the app registration created earlier.

Parse JSON from Refresh Token

Simplify the subsequent steps by parsing the Key Vault response using the ‘Parse JSON’ action.

The following schema can be copy/pasted into this action.

{
    "properties": {
        "attributes": {
            "properties": {
                "created": {
                    "type": "integer"
                },
                "enabled": {
                    "type": "boolean"
                },
                "recoverableDays": {
                    "type": "integer"
                },
                "recoveryLevel": {
                    "type": "string"
                },
                "updated": {
                    "type": "integer"
                }
            },
            "type": "object"
        },
        "id": {
            "type": "string"
        },
        "value": {
            "type": "string"
        }
    },
    "type": "object"
}

The ‘HTTP’ action propels a POST request to the AD OAuth token endpoint to procure the authorization token essential for Azure API interactions.

URI (Replace {tenant-id} with your own tenant Id):

Azure Cloud: https://login.windows.net/{tenant-id}/oauth2/token
Azure Government: https://login.microsoftonline.us/{tenant-id}/oauth2/token

Header:

content-type:application/x-www-form-urlencoded

Body:
-Azure Cloud:

resource=74658136-14ec-4630-ad9b-26e160ff0fc6&grant_type=refresh_token&refresh_token=@{body('Parse_JSON_From_Refresh_Token')?['value']}&client_id=1950a258-227b-4e31-a9cf-717495945fc2&scope=openid

-Azure Government:

resource=ee62de39-b9b0-4886-aa58-08b89c4e3db3&grant_type=refresh_token&refresh_token=@{body('Parse_JSON_From_Refresh_Token')?['value']}&client_id=1950a258-227b-4e31-a9cf-717495945fc2&scope=openid

Dive into the login response to extract the access and refresh tokens using the ‘Parse JSON’ action.

Utilize this schema:

{
  "properties": {
      "access_token": {
          "type": "string"
      },
      "expires_in": {
          "type": "string"
      },
      "expires_on": {
          "type": "string"
      },
      "ext_expires_in": {
          "type": "string"
      },
      "foci": {
          "type": "string"
      },
      "id_token": {
          "type": "string"
      },
      "not_before": {
          "type": "string"
      },
      "refresh_token": {
          "type": "string"
      },
      "resource": {
          "type": "string"
      },
      "scope": {
          "type": "string"
      },
      "token_type": {
          "type": "string"
      }
  },
  "type": "object"
}

Set new Refresh Token

With a new refresh token at hand, update Key Vault using the ‘Create or update secret value’ action via the AzureKeyVault custom connector, thereby elongating the token’s lifespan.

HTTP to Azure API

The curtain call involves employing the access token, obtained from the Login action, to interact with the Azure API. In this illustration, we’re fetching the account SKUs.

Upload Auth Token

The primary incentive behind devising this solution was to automate the simulation of uploading a hardware token CSV. The subsequent details exhibit the URLs and a sample body JSON for this purpose:

To delve deeper into the realm of OATH tokens in Azure Entra, explore the following link: Authentication methods in Microsoft Entra ID - OATH tokens.

Sample URL/Payload

Commercial URLs:

Azure Cloud: https://main.iam.ad.ext.azure.com/api/MultifactorAuthentication/HardwareToken/upload
Azure Government: https://main.iam.ad.ext.azure.us/api/MultifactorAuthentication/HardwareToken/upload

Body:

{
  "content": "upn,serial number,secret key,time interval,manufacturer,model\r\ntest@mydomain.com,1234567,2234567abcdef2234567abcdef,30,Contoso,HardwareKey",
  "id": null,
  "mimeType": "application/vnd.ms-excel",
  "name": "760b21fc-128a-4504-9374-71f47638e27c.csv"
}

In the final HTTP action of the Power Automate Flow, the above URLs and body can be employed to automate the hardware token upload process. You would specify the corresponding URL (based on your Azure environment) in the HTTP action’s URI field, and the provided JSON payload in the Body field. This automation step is crucial as it streamlines the hardware token management process by allowing a seamless upload of token data through the main.iam.ad.ext.azure.com API.

The payload above delineates the necessary information for uploading hardware tokens, embodying user details, token attributes, and the corresponding hardware specifics.

In conclusion, leveraging the main.iam.ad.ext.azure.com API in conjunction with Power Automate facilitates a seamless automation of hardware token management, thereby significantly reducing manual labor and enhancing security by minimizing human error.

PowerApps RAW!

Set Your Preferred Solution for the Dataverse MCP

What’s a Preferred Solution?

Setting Your Preferred Solution

Option 1: When Creating a New Solution

Option 2: From the Solutions List

That’s It

Fixing PAC CLI “non-recoverable error” in GCC High and DoD by enabling telemetry

What I observed

The surprising trigger: telemetry disabled

Check your telemetry status

Fix: enable telemetry and retry

Why the error is misleading

Quick troubleshooting checklist

Wrap-up

Bridging Clouds: Secure Pipelines from Azure DevOps to GCC High

Introduction

Key Placeholders to Fill In

Step 1 — Create a Storage Account in GCC High

Step 2 — Create a Managed Identity in GCC High

Step 3 — Assign Roles to the Managed Identity

Step 4 — Begin Service Connection Setup in Azure DevOps (Commercial)

Step 5 — Configure Federated Credentials (Exchange Between ADO & GCC High)

Step 6 — Complete Service Connection in ADO (Commercial)

Step 6a — Approve pipeline to use the service connection

Step 7 — Test the Connection

Locking Down a Logic App (Consumption) with OAuth for Calls from Dataverse Plug-ins using Managed Identity

Why I did this

What I changed

1) Disable SAS for the HTTP trigger (Consumption only)

2) Add an OAuth policy on the Logic App

Narrowing Down the OAuth To Specific Managed Identity

What broke (and how I fixed it)

Minimal plug-in tracing snippet (to see the token)

References

Passing Pipeline Trigger Time to Data Flows in Azure Data Factory: Use Strings, Not Timestamps!

The Scenario

The Common Mistake

The Solution

Lesson Learned

Power Query: Driftless Merges using Table.Buffer

What happened

Why and how Table.Buffer works

Demonstration of issue

Reproduce the issue and the fix

Create the three small tables with Enter data

Query 1: pick per group, then merge (unbuffered)

Query 2: same logic with Table.Buffer to freeze the pick

How to View DLP Policies Applied to a Power Platform Environment

Step 1: Locate Your Environment ID

Step 2: Use the URL to View Applied DLP Policies

✅ New Admin Center:

🕹️ Old Admin Center:

Summary

Harnessing Host Form Data with PCF Controls in Model-Driven Applications

Introduction

Setting up JavaScript on Host Form

JavaScript Code Implementation

Deployment and Configuration

Getting Data from Host Form in Custom PCF Control

Implementing the Render Component

Accessing in React

Getting Data from Host Form in Microsoft Form Component PCF

Setting up JavaScript in the Child Form

Copy Hidden Power Automate Expressions

The Workaround (Until Now)

Bookmarklets to the Rescue

Requirements

Where This Is Useful

How It Works

How to Use It

Option 1: Bookmarklet

Option 2: DevTools Snippet

Connecting to Dataverse from Azure Data Factory

Introduction

Connection Types

REST API

Dataverse Connector

OData

Security Considerations

Why and how `Table.Buffer` works

Query 2: same logic with `Table.Buffer` to freeze the pick

Step 2: Create the `connectionSettings.json` File