Using the API with Microsoft Excel and Power Query

Microsoft Power BI is a business analytics platform that provides interactive data visualizations for business intelligence (BI). You can use Microsoft Power BI to build customized reports or combine the data it provides with data from other tools to build a unified dashboard.

You can use the License Statistics API as input to Power BI; for example, to create a custom report. The steps to use the License Statistics API with Power BI include: 

1. Create a token with API scope in License Statistics.
2. Add a Web data source to Power BI.
3. Prepare the License Statistics API data for use with Power BI.
4. Use the prepared License Statistics data to create a Power BI report.

Excel’s Power Query tool is used to transform and prepare data. Power Query not only lets you import data from a variety of sources, but gives you the ability to transform and use the data to suit your needs. Using Power Query, you can set up a query once, and then reuse the query by “refreshing” the data as often as needed.

Step-by-step instructions for importing License Statistics data directly into Excel using Power Query are detailed below

1. In Excel, click From Web under the Data ribbon to open the Power Query editor.
   
   

1. In the From Web pane, toggle on Advanced.
   
   
   
   
2. Under the URL parts area, add lines for the parts as shown in the following table and illustration:

1. Under “HTTP request header parameters,” add an X-Auth-token header using a token generated from License Statistics, and click Add header.
   
   
   
   
2. Click OK to continue.
   
   
3. Choose Anonymous as the web content access type, and click Connect.
   
   
   
   
4. Click Info Table under the Content ribbon.
   
   
5. Optionally, you can name the query using the Properties area under Query Settings. In our example, we named the query “Usage Per User” to indicate the type of data being retrieved.
   
   

1. Select data list from the table.
   
   
   
   

1. Click To Table under the Transform tab to convert the data list to a table.
   
   
   
   
2. In the resulting “To Table” pane, choose None as the delimiter, and choose Show as errors to handle extra columns.
   
   
   
   
3. Click OK.
   
   
4. Click the expand icon in the upper right of the Queries table to expand the table columns.
   
   
   
   
5. Click Load more at the bottom of the list to load all the columns.
   
   
   
   
6. Select the columns to include in the query; for example:
• un
• hn
• lsn
• fns
• fv
• massage
• hu
• mu
• ldtc
• ft

1. Optionally, you can rename columns by right-clicking on the column header and choosing Rename from the right-click menu.
   
   
   
   For example you might rename columns to their corresponding label found in License Statistics:
• us = Username
• hn = Host
• lsn = License Server
• fns = Feature Name
• fv = Feature Version
• musage = Max Usage
• hu = Hours Used
• mu = Max Used
• ldtc = Denials
• ft = Total
  
  

1. Click Close & Load.
   
   

1. If you want to refresh data automatically, perform the following additional steps:
   
   
• Go to Queries & Connections under Excel’s Data tab, and select Properties.
  
  
  
  
• In the External Data Properties dialog, click Query Properties.
  
  
  
  
• Under the Usage tab, set the refresh options as desired.
  
  

We hope this article has been helpful for understanding all you need to know to import your License Statistics data into Excel using Power Query. If you have questions or comments, please don’t hesitate to contact us!

Meta description: 

Learn how to import data from License Statistics to Power Query.

Social media:

Get step-by-step instructions for importing data from License Statistics into Power Query.