I have been looking into how I can do some reporting on the PowerApps I have and what connectors they are using, I knowthe CoE toolkit will do a lot of this, but I havent installed it yet, It’s on its way.

In the mean time I need to get some idea of how many apps I have and what connectors are in use and I wanted to do this with PowerShell Cmdlets.

To start with here is the powershell I used to get the PowerApps and all the Connectors they use.

$powerApps=Get-AdminPowerApp 
$filename = "powerapps" + $timestamp + ".json"

foreach ($app in $powerApps) {
   $app | Add-Member -MemberType NoteProperty -Name Connectors -TypeName System.Collections.Arraylist -value $app.Internal.properties.connectionReferences.PSObject.Properties.Value
   $.Connectors += {}
}

$powerApps | Convertto-json -depth 100|Out-File -Encoding ascii $filename 

The code above will return all the powerapps in all environments, you may want to filter if you need to. You do not need to do the Foreach loop, it makes it easier to do the PowerBi stuff later on, other wise you are expanding records all the way down to get to the internal.

The important thing here when converting this object is the -depth 100 because if you dont add it, then your connectors will not be included, it will just be a string value truncated at the “Internal” property, it may not even show you all the properties, and some of them are intersting, like uses gateway, or is usesPremium connectors.

Then you may want to use this in a PowerBI report, slight problem here is that the connectorsReference property can contain, null, a single record, or an array of connectors. The conversion does this and I dont know how to stop it.

To create my Report in PowerBI, first was to Create a Table that contains the PowerApps, load the Json, convert it to a table, then expand the record etc. But do not expand the connectionReferences, this needs to be another table in powerbi, to get your relationship.

To create a table of Conenctors with a PK of AppId (to the PowerApps table) then I start with two tables, one will be a list of connectors from this json that are a Record, and the other will be a table to handle Lists of connectors, this is to counter the export problem.

Create a blank Query and go into Advanced Editor this is the code

let
    
source = Json.Document(File.Contents("C:\powerapps.json")),
    #"Converted to Table" = Table.FromList(source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    out = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"AppName", "Connectors"}, {"AppName", "Connectors"})
    ,out2=Table.SelectRows(out, each Value.Is([Connectors], type record)),
    #"Expanded Connectors" = Table.ExpandRecordColumn(out2, "Connectors", {"id", "displayName", "iconUri", "dataSources", "dependencies", "dependents", "isOnPremiseConnection", "bypassConsent", "apiTier", "isCustomApiConnection"}, {"id", "displayName", "iconUri", "dataSources", "dependencies", "dependents", "isOnPremiseConnection", "bypassConsent", "apiTier", "isCustomApiConnection"})
       

in
 #"Expanded Connectors"

It is the line in the above, that gets the “Out2” table results that does this, it returns on the type of Connectors that are “Records” for those entries where the JSON didnt put it into an array of 1.

Next create another blank query to filter to the lists

let
    
    source = Json.Document(File.Contents("C:\powerapps.json")),
    #"Converted to Table" = Table.FromList(source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    out = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"AppName", "Connectors"}, {"AppName", "Connectors"})
    ,out2=Table.SelectRows(out, each Value.Is([Connectors], type list )),
    #"Expanded Connectors" = Table.ExpandListColumn(out2, "Connectors"),
    #"Expanded Connectors1" = Table.ExpandRecordColumn(#"Expanded Connectors", "Connectors", {"id", "displayName", "iconUri", "dataSources", "dependencies", "dependents", "isOnPremiseConnection", "bypassConsent", "apiTier", "isCustomApiConnection"}, {"id", "displayName", "iconUri", "dataSources", "dependencies", "dependents", "isOnPremiseConnection", "bypassConsent", "apiTier", "isCustomApiConnection"})
in
    #"Expanded Connectors1"

Now that you have two tables that when combined show all the connectors and have the PowerApp AppId as its PKI in PowerBI do a Append Query that creates a New Table and call it PowerAppsConnectors.

Then do your usual powerbi relationship management, it may have auto created links from the first 2 tables and you dont want that, you want a new one for the combined tables.

You will find that -depth 100 handy for all the Convertto-JSON commands.