Power BI and Excel

How it works

The query on this page does three things on every refresh. It swaps your Client ID and Client Secret for an access token. It reads the profile search, 300 people at a time, with POST requests. Then it turns the result into a table with one row per person per skill. It uses the Web.Contents function with its Content, Headers, RelativePath and Query options. Microsoft's reference for Web.Contents

Before you start

• An API client with the Read scope. An Owner or Admin creates it under Team/Org › Settings › API Access. The steps are in MuchSkills API and API Access.
• Excel with Power Query, or Power BI Desktop. To refresh on a schedule, you also need access to a Power BI workspace.
• A private file. The query holds your Client Secret. Keep the workbook or report file to yourself and share the results.

Steps

In MuchSkills

1. Create a Read client. Go to Team/Org › Settings › API Access, click New Client, name it, for example Power BI export, tick Read under Scopes and click Create Client. Copy the Client ID and Client Secret before you click Done.

In Excel or Power BI Desktop

1. Open a blank query. In Excel, choose Data › Get Data › From Other Sources › Blank Query. In Power BI Desktop, choose Home › Get data › Blank query.
2. Paste the query. In the Power Query editor, choose Home › Advanced Editor, delete what is there and paste this query. Replace YOUR_CLIENT_ID and YOUR_CLIENT_SECRET with your values and keep the quotation marks. Click Done.

let
ClientId = "YOUR_CLIENT_ID",
ClientSecret = "YOUR_CLIENT_SECRET",
BaseUrl = "https://app.muchskills.com",
PageSize = 300,

// 1. Get an access token. A new one is fetched on every refresh.
TokenResponse = Json.Document(Web.Contents(BaseUrl, [
RelativePath = "oauth2/token",
Headers = [#"Content-Type" = "application/x-www-form-urlencoded"],
Content = Text.ToBinary(Uri.BuildQueryString([
grant_type = "client_credentials",
client_id = ClientId,
client_secret = ClientSecret
]))
])),
Token = TokenResponse[access_token],

// 2. Read one page of profiles. The search is a POST:
// skip and limit go in the address, the body is {}.
GetPage = (Skip as number) as record =>
Json.Document(Web.Contents(BaseUrl, [
RelativePath = "api/v2/profile/search",
Query = [skip = Text.From(Skip), limit = Text.From(PageSize)],
Headers = [
Authorization = "Bearer " & Token,
#"Content-Type" = "application/json"
],
Content = Text.ToBinary("{}")
])),

// 3. Read every page.
FirstPage = GetPage(0),
Total = FirstPage[pagination][total],
MoreSkips = List.Numbers(PageSize, List.Max({0, Number.RoundUp(Total / PageSize) - 1}), PageSize),
Pages = {FirstPage} & List.Transform(MoreSkips, GetPage),
Profiles = List.Combine(List.Transform(Pages, each _[data])),

// 4. One row per person per skill.
OrEmpty = (x) => if x = null then {} else x,
Rows = List.Combine(List.Transform(Profiles, (person) =>
List.Combine(List.Transform(OrEmpty(Record.FieldOrDefault(person, "skillTypes", null)), (category) =>
List.Transform(OrEmpty(Record.FieldOrDefault(category, "skills", null)), (skill) => [
Name = person[profile][name],
Email = person[profile][email],
Department = Record.FieldOrDefault(person[profile], "department", null),
#"Skill category" = category[type],
Skill = skill[name],
#"Own level (1-9)" = Record.FieldOrDefault(skill, "level", null),
#"Validated level (1-9)" = Record.FieldOrDefault(skill, "validatedLevel", null)
])
))
)),
Result = Table.FromRecords(Rows)
in
Result

1. Allow the connection. If Power Query asks how to connect to https://app.muchskills.com, choose Anonymous and click Connect. If it asks about privacy levels, choose Organizational.
2. Load the table. In Excel, choose Home › Close & Load. In Power BI Desktop, choose Home › Close & Apply. To get fresh data later, choose Data › Refresh All in Excel or Refresh in Power BI Desktop.

In the Power BI service (optional)

1. Publish and set credentials. Publish the report to a workspace. Open the semantic model's settings, and under Data source credentials edit the credentials for https://app.muchskills.com: choose Anonymous and the privacy level Organizational, then sign in.
2. Schedule the refresh. In the same settings, turn on scheduled refresh and choose how often. No gateway is needed. Microsoft's guide to data refresh in Power BI

Check it worked

• You get a table with one row per person per skill, with each person's own level and any validated level.
• The number of different emails in the table matches the number of people in your MuchSkills organisation.
• In the Power BI service, the refresh history shows a successful refresh.

What you get

• Skills in your own reports. Combine MuchSkills data with HR, finance or project data in Power BI or Excel.
• Fresh numbers without exports. A refresh pulls the current data, so nobody downloads and pastes files.
• Self and manager views side by side. Both levels are in the table, so you can see where they differ.

Important to know

Levels are on a 1 to 9 scale. 1 to 3 is Beginner, 4 to 6 Intermediate and 7 to 9 Expert. The validated level is the one a manager confirmed. It is empty when no manager has validated the skill.

POST requests must be anonymous. Power Query only sends POST requests when the connection is set to Anonymous. That is why the query fetches its own token rather than using a stored credential. Microsoft's notes on the Web connector

Keep the base address fixed. The Power BI service can refresh this query because the address is always https://app.muchskills.com, and the path and paging are added with RelativePath and Query. If you edit the query, keep that pattern. Joining the address together as text can stop scheduled refresh from working.

Paging. Each page holds up to 300 people. The query reads the total from the first page and fetches the rest.

Request limits. If you refresh many queries at once, MuchSkills may answer 429 Too Many Requests. Wait a minute and refresh again.

Revoke if exposed. If the file reaches someone it should not, click Revoke on the client in API Access and create a new one.

Troubleshooting