Integrating PowerShell with Power BI (Part 1)

[AlexHolmeset]

Power BI is an extremely powerful data and visualization tool. It has opened a new world for me. But, the first thing I always think about when looking at Microsoft products is that there must be a way to control this with PowerShell. I did not find any useful information about any PowerShell module on the product pages.  After some googling, I found a module on the PowerShell gallery potentially created by a Microsoft employee.  What's surprising is that the module has been out for about 7 months and does not have many downloads.  Why hasn’t this been more popular? Hopefully I can help change this with a series of blog posts looking at the possibilities of Power BI and PowerShell.

Let's take a look at what is needed to get started and set up the connection/integration.

What is needed:

• Azure active directory tenant and organizational user.
• Register a Power BI App
• Register the Power BI app in Azure.
• Apply permission to your Application in Azure AD
• Install PowerShell module for Power BI

Register Power BI App:

1. Go to dev.powerbi.com/apps.
2. Sign in with your existing account.
3. Set App Name in the App Name box
4. For PowerShell use select Native app.
5. Enter this as a Redirect URL:
   https://login.microsoftonline.com/{contoso.onmicrosoft.com}/oauth2
6. Sellect all APIs. Check here for more information on there permissions:
   https://powerbi.microsoft.com/en-us/documentation/powerbi-developer-power-bi-permissions/
7. Click the Register App button.
8. Make note of the Client ID.

Register the App in Azure:

1. Search for App registrations under More Services.
   
2. Select New application registration.
3. In the form that pops up, do the following:
   1. Enter a name in the Name box.
   2. Choose Native in the Application Type drop down list.
   3. Set this redirect URL:
      https://login.microsoftonline.com/contoso.onmicrosoft.com/oauth2
      
   4. Click Create.

Apply permissions to your application within Azure AD:

1. Select the newly created app within App Registrations section.
   
2. Select Required permissions menu.
   
3. Open the Windows Azure Active Directory API, select Access the directory as the signed-in user and select Save.
   
4. Open the Power BI Service API, select all permissions under Delegated Permissions and select Save.
   
5. Select Grant Permissions.
   

Now it's time to install the Power BI module and its dependencies.  Since its on the PowerShell Gallery, its as simple as opening a PowerShell window and enter the following:

     Install-Module -Name Microsoft.ADAL.PowerShell
     Install-Module -Name Microsoft.PowerBI.PowerShell

You can read more about the PowerShell module for Power BI here,  Now, you are ready to start sending data into Power BI from PowerShell.  In the next blog article, part 2, I will take a look at how we use the Power BI module and how to visualize the data in Power BI reports.  In the part 3 blog article I will take you through the steps to automate in Azure. 

This article was first published on my blog alexholmeset.blog.