Connecting to the UniPhi REST API from Excel Power Query

This article explains how to connect Microsoft Excel Power Query to the UniPhi REST API to extract live data (for example, projects) for reporting and analysis.

The guidance is split by UniPhi version, as authentication methods differ between UniPhi v21 and UniPhi v20 and earlier.

Prerequisites (All Versions)

Before you begin, ensure:

You have Microsoft Excel (Microsoft 365 recommended).

You are licensed as a Program Manager or Administrator in UniPhi.

You know your UniPhi base URL (e.g. https://yourorganisation.uniphi.com).

You have access to the UniPhi REST API (enabled by default for supported licences).

UniPhi v21 (Recommended)

Overview

UniPhi v21 introduces modern Microsoft authentication, allowing Power Query to connect securely using your organisational Microsoft account.

This is the recommended approach for all new integrations.

Key Requirements

• Microsoft Authentication must be enabled in your UniPhi environment (Hamburger Menu < Configuration < Authentication)

  
  

  

• You must have logged into the UniPhi web application at least once using your Microsoft account.

• Your UniPhi licence must be Program Manager or Administrator.

If it is not possible to use Microsoft Authentication, then refer to the UniPhi v20 and earlier method below for an alternative authentication method.

Step-by-Step: Connecting Power Query to UniPhi v21

1. Open Power Query in Excel

Open Excel.

Go to Data → Get Data → From Other Sources → From Web.

2. Enter the UniPhi API Endpoint

Use a UniPhi REST endpoint, for example: https://yourorganisation.uniphi.com.au/api/project

Click OK.

3. Choose Authentication Method

When prompted:

Select Organizational Account.

Click Sign in.

Authenticate using your Microsoft account (the same one used for UniPhi).

This uses Microsoft Entra ID (Azure AD) behind the scenes. No password is stored in Excel.

4. Load and Transform Data

Once connected, Power Query will display the JSON response.

Typical steps:

Select the List or Record returned.

Click To Table (if prompted).

Use the expand (⤢) icon to expand columns such as: Project name, Status, Lifecycle phase etc.

5. Load Data into Excel

Click Close & Load.

The data will be inserted as an Excel table.

Use Refresh at any time to pull the latest data from UniPhi.

API Documentation (v21)

Full REST API documentation can be accessed via the Hamburger Menu < API Documentation

• 

• Or by contacting UniPhi Support to enable or review API endpoints for your environment

Common endpoints include:

• /api/project

• /api/resource

• /api/risk

• /api/issue

  
  

UniPhi v20 and Earlier

Overview

UniPhi v20 and earlier versions do not support Microsoft authentication for the API.

The only supported method is Basic Authentication using a special API password.

This password is not the same as your UniPhi ID or Microsoft login.

In UniPhi v20 and earlier, it is only possible to connect to the UniPhi API from Excel PowerQuery using Basic Authentication

Role & Permission Check

Before attempting to generate a password, ensure the following criteria are met:

• Administrative Access: Only an Administrator can issue or reset these credentials. If you are not an admin, you will need to request this from your system administrator.

• License Type: The user accessing the API must have a Program Manager or Administrator license assigned to their profile.

  
  

Generating the Password

According to the documentation, the password must be issued from within the software's user management area:

Log in to the UniPhi web application.

Navigate to the Resources module.

Search for and open the specific Person Profile for the user who needs API access.

The Administrator can then click on the Reset Password which will send an email to set up a new basic authentication password to the relevant person.

Step-by-Step: Connecting Power Query to UniPhi v20

1. Open Power Query

Excel → Data → Get Data → From Web

2. Enter the API URL

Example:

https://yourorganisation.uniphi.com.au/api/project

3. Choose Basic Authentication

When prompted:

Authentication type: Basic

Username: Your UniPhi username (email or login name)

Password: The API password created in your profile

4. Transform and Load Data

The transformation steps are the same as v21:

Convert JSON to a table

Expand records

Rename and filter columns

Load into Excel