A gotcha with PowerApps, Flow and migrated Managed Metadata columns

Hi all

This week I have been migrating SharePoint content from one site collection to another using ShareGate and I hit a frustrating issue. After migration, SharePoint worked fine, but PowerApps and Flow were both unable to connect to any SharePoint lists that contained multi-value managed metadata columns. In PowerApps I would get “An error occurred on the server” and a fiddler trace showed me an error 502 (gateway not found). Attempting to use any of the SharePoint in Flow (such as Get Items or Get item) showed a similar message…

Examining the detailed error message was quite telling… here is an example…
can you see part of a term set in the message? What’s going on here?

"error": {
  "code": 502,
  "source": "australia-001.azure-apim.net",
  "clientRequestId": "[]",
  "message": "BadGateway",
  "innerError": {
    "status": 500,
    "message": "Emergency|e0b200d8-89ed-4104-8dda-c34770e4d98a\r\nclientRequestId: fbc65434-8363-4d5f-a489-8f39006d0cf8\r\nserviceRequestId: 1ae0b59e-00dd-0000-1bf7-c85974598692"
    }
  }
}

So it turns out that ShareGate migrates managed metadata columns in such a way that only PowerApps and Flow are impacted. It seems to include an extra hash in a hidden column that each and every managed metadata column has associated with it. Normally one might expect to see this:

115;#Incident Management|64e88ce1-39be-48c6-ac24-1728e688492a;#105;#Human factors|9069ac64-a6e7-4a86-80d6-46d2297c01ce

but instead, the migration produced this…note the extra hash in the second term…

115;#Incident Management|64e88ce1-39be-48c6-ac24-1728e688492a;#105;##Human factors|9069ac64-a6e7-4a86-80d6-46d2297c01ce

Now SharePoint does not seem to care about this, but PowerApps and Flow sure do. Luckily there is a fix if you are comfortable with PowerShell. Using PnP I used the Set-PnpTaxonomyFieldValue cmdlet to reapply the terms to the main column, which updates the hidden column and corrects the corruption. Additionally, to avoid “Modified By” and “Modified” columns being updated with my details when correcting this, I took a copy and reapplied the original values as well. The implication here is this script is run as Site Collection Admin or with Full Control permission…

The trick to this script is that the Set-PnPTaxonomyFieldValue expects a set of terms as PowerShell hash table, but SharePoint returns Managed Metadata as a SharePoint taxonomy object, so a little fudging is in order to update it.

The script I used is below… hope this helps someone…

$column = "ManagedMetadataColumn"
connect-pnponline https://site.sharepoint.com/sites/somesite
$list = get-pnplist -id "[ListName]"
$items = Get-PnPListItem -List $list
foreach ($item in $items) {
   $oldauthor = $item["Editor"]
   $oldmodified = $item["Modified"]
   $Refresh = @{}
   foreach ($term in $item[$column]) {
      $Refresh.add($term.TermGuid, $term.Label)
   }
   Set-PnPTaxonomyFieldValue -ListItem $item -InternalFieldName $column -Terms $Refresh
   $item["Editor"] = $oldauthor
   $item["Modified"] = $oldmodified
   $item.update()
   Invoke-PnPQuery
}