Friday, 2 April 2021

Power Apps using Excel in One Drive or SharePoint

Overview:  A Canvas Power App can easily connect to Excel held with SharePoint of OneDrive.   It is great for getting values in, or for reading static values from a list.


1. You need to create a table in Excel to connect from Power Apps.  The problem is the table cannot contain any formulas.  I wanted to input a value, use Excel formulas to get a calculate risk value.  It can't connect.  The work around is to build the formulas in Power Apps but for my customer complex Excel sheet I don't want to spend weeks re-engineering the logic from code.

Excel: Trying to read field B19 that reference my calculation:

Power Apps: Trying to connect to the Excel table results in the "Excel file containing formula are currently not supported" error message:

2. Size limit, max 2 MB Excel file.  This may be bigger now.

Summary:  You can't work with large data sets and you can read from calculation cells in tables o Excel is fine for inputting into an Excel document that will be used as excel but pretty useless as a data store for Power Apps.  Rather use a SharePoint list but this doesn't help if you actually want to use Excel as it contains complex logic.

Monday, 15 March 2021

APIM Authentication and authorisation

Overview:  APIM provides two methods for secure access to an API operations:

  1. Subscription: passed in the header or the query string (generated primary and secondary subscription key for each subscription), and 
  2. Client Certificates.

Saturday, 13 March 2021

APIM OpenAPI Specification Documentation Example within the Developer Portal

Overview:  I find document APIM contracts incredibly important and yet it's often very poorly done.  This post provide a simple YAML and JSON example that can be imported into APIM or any other gateway product for that matter.

The YAML file below can be imported into APIM and published to the developer portal.  The example provides a clear example on options and how an API should be documented.  Developers can see an example of the JSON to use when performing the PUT.  The developers can see more information of a property, for instance a passport number would be a certain length and rather than specifying and option free text string with no description, the developer would know that the property has to come in the correct format.

Simple Open API specification showing a single documented operation for a complex PUT object (YAML).

PB APIM Series:
Documenting you API in the Developer portal (this post)

Sunday, 7 March 2021

.NET versions no longer supported

Problem:  The organisation has a lot of .NET applications, that run on Azures Service Fabric created over a number of years.  Service Fabric has minimum versions and many of the apps have gone out of Microsoft support for the .NET version they are written on.

Initial Hypothesis:  We have .NET framework and .NET Core apps.  .NET core 5.0 has dropped the "Core" part of the naming, so it is merely .NET 5.

Proposed Resolution: My preference is to upgrade .NET core and .NET Framework separately as this is the lowest risk, longest life with minimized cost. 

 .NET Core

For .NET Core apps, if less than .NET core 3.1 migrate the app to .NET 5.  .NET Core 3.1 don't upgrade.  Any other versions should be upgraded to .NET 5. 


Release date

Released with

Latest update

Latest update date

Support ends[20]

.NET Core 1.0


Visual Studio 2015 Update 3



June 27, 2019

.NET Core 1.1


Visual Studio 2017 Version 15.0



June 27, 2019

.NET Core 2.0


Visual Studio 2017 Version 15.3



October 1, 2018

.NET Core 2.1


Visual Studio 2017 Version 15.7

2.1.26 (LTS)


August 21, 2021

.NET Core 2.2


Visual Studio 2019 Version 16.0



December 23, 2019

.NET Core 3.0


Visual Studio 2019 Version 16.3



March 3, 2020

.NET Core 3.1


Visual Studio 2019 Version 16.4

3.1.13 (LTS)


December 3, 2022

.NET 5


Visual Studio 2019 Version 16.8



3 months after .NET 6 release

.NET Framework

For .NET Framework, anything less than .NET Framework 3.5, move to 3.5.  3.5 Framework based apps can stay on 3.5.  For app using .NET Framework 4.0 to 4.5.1 upgrade to .NET framework 4.8.  For app using .NET 4.5.2 to 4.7, stay on their versions.


Existing Application Support

Target New Applications?


out of support - migrate ASAP

out of support - do not target


out of support - migrate ASAP

out of support - do not target


out of support - migrate ASAP

out of support - do not target


 Operating System (until 2029) 

Operating System (until 2029) target with caution

4.0 to 4.5.1

out of support - migrate ASAP

out of support - do not target

4.5.2 to 4.7

⚠️Operating System (mixed) - review support policy of each version

Operating System (mixed) - target with caution


 Operating System (indefinite)

 Operating System (indefinite)

Sunday, 28 February 2021

Uploading custom Teams Backgrounds

Instructions on how to Add a personalised meeting Background to your MS Teams Meetings and Calls.

Website that allows people to create corporate branded Teams and Zoom backgrounds.  Take a look and let me know what you think!

Friday, 26 February 2021

Teams meetings backgrounds

Overview:  I like to use a background in my Teams meetings as I don't need to worry about what people and clients can see in my home.  I recommend using a branded background that includes your company logo and your name.

Friday, 12 February 2021

APIM debugging, tracing, monitoring tricks and tips

Debugging APIM requests from Visual Studio code 

Has an extension for debugging APIM.

Azure API Management - Visual Studio Marketplace

It's also useful to have a APIM requesting/client extension installed

Tracing APIM

To get a full trace add the HTTP header "Ocp-Apim-Trace: true" to the request and the response shall contain a URL to retrieve the trace information.

Documentation Tips

Ensure you fill in relevant descriptions and summaries.  It's also key to provide examples.

My Technical Working Notes for Microsoft Technology: APIM OpenAPI Specification Documentation Example within the Developer Portal (

APIM documentation updates on the Developer portal (after re-publishing).  It has a great UI, but ensure the summaries are added for param/attributes to get a truly rich integration set of documentation (it will save so many questions and time).   I also like to add a getting started guide, keep it short and simple and most importantly have a simple explanation of security/authentication and connectivity.

- in: query
  name: age
      type: integer
      maximum: 3
    examples:       # Multiple examples
      max: # Distinct name
        value: 3   # Example value
        summary: The age is dependent on dob, min is 0, can't be negative 

Thursday, 28 January 2021

Encryption Options for Azure SQL

Overview:  With all IT storage, we are looking for encryption at rest and making sure the data is encrypted “over the wire” until it is stored storage.  For encryption in transit, Azure SQL supports TLS/SSL versions 1.0, 1.1,and 1.2.  If possible got for TLS 1.2.

Azure SQL Server Transparent Data Encryption (TDE) related to encryption at rest by encrypting the log and data files on the storage; Azure enforces TDE as the default on databases.  TDE can be turned off on your Azure SQL instance.

Backups should also be encrypted, and if TDS is enabled on Azure, your backups are also automatically encrypted.  Tip: Validate your restore of Azure Backups to another instance.

Column encryption is useful for encrypting a column within a table.  I prefer to use a Key Vault and use a SQL column to point to the database for things like tokens and secrets, but something like credit card numbers column encryption is ideal.

Always Encrypted allows for one or more columns to be encrypted within a database.  Client application shall decrypt and provides for separation where database owners/access cannot validate/view the encrypted column/columns.

Wednesday, 27 January 2021

Tree Testing and Heuristic Reviews - Ux for dummies

I was speaking to two Ux experts in a meeting and they referred to Tree Testing when discussing Information Architecture and the users working there way around a new SaaS product.  "Tree Testing" is not a term I had heard before so they showed me this site and it fantastic.

I'm more familiar with Heuristic Reviews - that I find useful for improving UI/UX using an iterative approach that suits Agile nicely.