Partner Center API and Power BI

Article
08/31/2016

Power BI provides a feature rich toolset that makes it possible to construct meaningful reports that can provide actionable data. With Power BI you can import data through several different mechanisms include HTTP request. From an advanced query you can utilize the Web.Contents function to obtain the contents downloaded from a web address. Having this capability means that a query can be constructed that returns information from the Partner Center API. With this in mind I would like to walk you through how to utilize the Partner Center API as a data source for a Power BI report.

Let’s walkthrough how to use the Partner Center API as a data source for a report. In order to do this, you will need Power BI Desktop installed

Open Power BI Desktop, and close the getting started wizard if it loads
Click Edit Queries in the toolbar
Add a black query as a new source by clicking on New Source in the toolbar and then selecting Blank Query
Right click on Query1 and then click on Advanced Editor to open the advanced query editor
Enter the following for the query and then click Done

let
Source = (AccountId as text, ApplicationId as text, ApplicationSecret as text, Password as text, Username as text) => let
aadRequest = [
client_id = ApplicationId,
client_secret = ApplicationSecret,
grant_type = "password",
password = Password,
resource = "https://api.partnercenter.microsoft.com",
scope = "openid",
username = Username
],
aadContent = Text.ToBinary(Uri.BuildQueryString(aadRequest)),
aadTokenRequest = Web.Contents("https://login.microsoftonline.com/" & AccountId & "/oauth2/token",
[
Headers = [#"content-type"="application/x-www-form-urlencoded"],
Content = aadContent
]
),
aadTokenJSON = Json.Document(aadTokenRequest),
aadToken = aadTokenJSON[access_token]
in
aadToken
in
Source

This query will create a function that will be used to obtain an Azure AD token, which will be used to obtain a Partner Center token. It obtains an Azure AD token by making a HTTP POST against https://login.microsoftonline.com using App + User authentication. It is important to note for this sample we will be querying Azure usage records so App + User authentication is required.
Rename the query to GetAzureADToken
Create another blank query, rename it to GetPartnerCenterToken, and then specify the following query using the advanced query editor

let
Source = (AccountId as text, ApplicationId as text, ApplicationSecret as text, Password as text, Username as text) => let
aadToken = GetAzureADToken(AccountId, ApplicationId, ApplicationSecret, Password, Username),
pcTokenRequest = Web.Contents("https://api.partnercenter.microsoft.com/GenerateToken",
[
Headers = [
#"Authorization"="Bearer " & aadToken,
#"content-type"="application/x-www-form-urlencoded"
],
Content = Text.ToBinary("grant_type=jwt_token&client_id=" & ApplicationId)
]
),
pcTokenJSON = Json.Document(pcTokenRequest),
pcToken = pcTokenJSON[access_token]
in
pcToken
in
Source

This query will obtain an Azure AD token using the GetAzureADToken function create in step five to obtain a Partner Center token. Once you have this token you can utilize the Partner Center API.
Add another blank query, rename it to GetCustomers, and then specify the following query using the advanced query editor
let
Source = (PartnerCenterToken as text) => let
customersRequest = Web.Contents("https://api.partnercenter.microsoft.com/v1/customers",
[
Headers = [
#"Authorization"="Bearer " & PartnerCenterToken,
#"Accept"="application/json"
]
]
),
customers = Json.Document(customersRequest),
items = customers[items],
customersTable = Table.FromList(items, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
expendedRecord = Table.ExpandRecordColumn(customersTable, "Column1", {"id", "companyProfile", "relationshipToPartner"}, {"id", "companyProfile", "relationshipToPartner"}),
expandedCompanyProfile = Table.ExpandRecordColumn(expendedRecord, "companyProfile", {"tenantId", "domain", "companyName"}, {"tenantId", "domain", "companyName"})
in
expandedCompanyProfile
in
Source

This will create a function that can be used to obtain a list of customers using the Partner Center API.
Add another blank query, rename it to GetSubscriptions, and then specify the following query using the advanced query editor

let
Source = (CustomerId as text, PartnerCenterToken as text) => let
subscriptionsRequest = Web.Contents("https://api.partnercenter.microsoft.com/v1/customers/" & CustomerId & "/subscriptions",
[
Headers = [
#"Authorization"="Bearer " & PartnerCenterToken,
#"Accept"="application/json"
]
]
),
subscriptionsJSON = Json.Document(subscriptionsRequest),
items = subscriptionsJSON[items],
subscriptions = Table.FromList(items, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
subscriptionsExpanded = Table.ExpandRecordColumn(subscriptions, "Column1", {"id", "offerId", "offerName", "friendlyName", "quantity", "unitType", "creationDate", "effectiveStartDate", "commitmentEndDate", "status", "autoRenewEnabled", "billingType", "suspensionReasons", "contractType", "orderId"}, {"subscriptionId", "offerId", "offerName", "friendlyName", "quantity", "unitType", "creationDate", "effectiveStartDate", "commitmentEndDate", "status", "autoRenewEnabled", "billingType", "suspensionReasons", "contractType", "orderId"}),
subscriptionsFiltered = Table.RemoveRowsWithErrors(subscriptionsExpanded, "{Column1"),
subscriptionsRemoved = Table.SelectRows(subscriptionsExpanded, each [subscriptionId] <> null),
subscriptionsAppended = Table.AddColumn(subscriptionsRemoved, "tenantId", each CustomerId)
in
subscriptionsAppended
in
Source

This will create a function that will obtain a list of all subscriptions that belong to the specified customer using the Partner Center API.
Create another blank query, rename it to GetAllSubscriptions, and then specify the following query using the advanced query editor

let
Source = (PartnerCenterToken as text) => let
customers = GetCustomers(PartnerCenterToken),
Customer.Subscriptions = (customerId) => let
subscriptions = GetSubscriptions(customerId, PartnerCenterToken)
in
subscriptions,
customersSubscriptions = Table.AddColumn(customers, "Custom", each Customer.Subscriptions([tenantId])),
removeNoSubErrors = Table.RemoveRowsWithErrors(customersSubscriptions, {"Custom"}),
customExpand = Table.ExpandListColumn(removeNoSubErrors, "Custom"),
expanded = Table.ExpandRecordColumn(customExpand, "Custom", {"subscriptionId", "offerId", "offerName", "friendlyName", "quantity", "unitType", "creationDate", "effectiveStartDate", "commitmentEndDate", "status", "autoRenewEnabled", "billingType", "suspensionReasons", "contractType", "orderId"}, {"subscriptionId", "offerId", "offerName", "friendlyName", "quantity", "unitType", "creationDate", "effectiveStartDate", "commitmentEndDate", "status", "autoRenewEnabled", "billingType", "suspensionReasons", "contractType", "orderId"}),
removeIdColumn = Table.RemoveColumns(expanded, {"id"})
in
removeIdColumn
in
Source
This will create a function that calls the GetCustomers and GetSubscription functions to get a complete list of subscriptions for all customers.
Create another blank query, rename it to GetSubscriptionMonthlyUsage , and then specify the following query using the advanced query editor

let
Source = (CustomerId as text, SubscriptionId as text, PartnerCenterToken as text) => let
Subscription.Usage = (tenantId, subscriptionId) => let
usageRequest = Web.Contents("https://api.partnercenter.microsoft.com/v1/customers/" & tenantId & "/subscriptions/" & subscriptionId & "/usagerecords/resources",
[
Headers = [
#"Authorization"="Bearer " & PartnerCenterToken,
#"Accept"="application/json"
]
]
),
usageJSON = Json.Document(usageRequest),
items = usageJSON[items],
usage = Table.FromList(items, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
usage,
records = Subscription.Usage(CustomerId, SubscriptionId),
usage = Table.ExpandRecordColumn(records, "Column1", {"category", "subcategory", "quantityUsed", "unit", "resourceId", "id", "resourceName", "name", "totalCost", "currencyLocale", "lastModifiedDate", "attributes"}, {"category", "subcategory", "quantityUsed", "unit", "resourceId", "id", "resourceName", "name", "totalCost", "currencyLocale", "lastModifiedDate", "attributes"}),
transform = Table.TransformColumnTypes(usage,{{"quantityUsed", type number}, {"totalCost", Currency.Type}})
in
transform
in
Source

This will create a function that will utilize the Partner Center API in order to obtain monthly Azure usage records for the specified subscription
Create another blank query, rename it to GetMonthlyUsage, and then specify the following query using the advanced query editor

let
Source = (PartnerCenterToken as text) => let
subscriptions = GetAllSubscriptions(PartnerCenterToken),
azureSubscriptions = Table.SelectRows(subscriptions, each ([billingType] = "usage") and ([status] = "active")),
narrowed = Table.SelectColumns(azureSubscriptions, {"subscriptionId","tenantId"}),
usage= Table.AddColumn(narrowed, "Custom", each GetSubscriptionMonthlyUsage([tenantId], [subscriptionId], PartnerCenterToken)),
usageExpanded = Table.ExpandTableColumn(usage, "Custom", {"category", "subcategory", "quantityUsed", "unit", "resourceId", "id", "resourceName", "name", "totalCost", "currencyLocale", "lastModifiedDate", "attributes"}, {"category", "subcategory", "quantityUsed", "unit", "resourceId", "id", "resourceName", "name", "totalCost", "currencyLocale", "lastModifiedDate", "attributes"}),
transform = Table.TransformColumnTypes(usageExpanded, {{"quantityUsed", type number}, {"totalCost", Currency.Type}})
in
transform
in
Source

This will create a function that use the GetAllSubscriptions and GetSubscriptionMonthlyUsage functions to obtain Azure usage records for all active Azure subscription owned by the partner’s customers.

Now that all of the required functions have been created we can create three queries that will provide data to construct a report. In order to create the necessary queries, perform the following

1. Create a blank query, rename it to Customers, and then specify the following query using the advanced query editor
  
  let
  pcToken = GetPartnerCenterToken("{INSERT-ACCOUNT-IDENTIFIER-HERE }", "{INSERT-APPLICATION-ID-HERE}", "{INSERT-PASSWORD-HERE}", "{INSERT-USERNAME-HERE}"),
  customers = GetCustomers(pcToken),
  #"Renamed Columns" = Table.RenameColumns(customers,{{"companyName", "Company Name"}})
  in
  #"Renamed Columns"
  
  This query will return a list of customer associated with the specified CSP reseller. It is recommended that credentials not be specified in the query itself but rather leverage parameters. That was done here for simplicity, however, if you would like to do it then the only change you need to make after creating the parameters would be the second line. It would be modified to something similar to the following
  
  pcToken = GetPartnerCenterToken(#"AccountId", #"ApplicationId", #"ApplicationSecret", #"Password", #"Username"),
2. Once you click done the query will execute and you should see a message stating The user was not authorized. This is excepted and all you need to do is click Edit Credentials -> Connect
3. Add another blank query, rename it to Subscriptions, and then specify the following query using the advanced query editor

let
pcToken = GetPartnerCenterToken("{INSERT-ACCOUNT-IDENTIFIER-HERE }", "{INSERT-APPLICATION-ID-HERE}", "{INSERT-PASSWORD-HERE}", "{INSERT-USERNAME-HERE}"),
subscriptions = GetAllSubscriptions(pcToken)
in
subscriptions

1. Add another blank query, rename it to MonthlyUsage and then specify the following query using the advanced query editor

let
pcToken = GetPartnerCenterToken("{INSERT-ACCOUNT-IDENTIFIER-HERE }", "{INSERT-APPLICATION-ID-HERE}", "{INSERT-PASSWORD-HERE}", "{INSERT-USERNAME-HERE}"),
usage = GetMonthlyUsage(pcToken)
in
usage

Click Close & Apply in the toolbar so you can start building a report
Start building reports that you need

Hopefully through all of this you learned how you can leverage the Partner Center API as a data source for Power BI reports. You can download a partially completed report from here, the queries to obtain the customers, subscriptions, and usage will need to be added by you.

Partner Center API and Power BI

Additional resources