Integrating PowerShell with Power BI (Part 1)

by AlexHolmeset Frequent Visitor on ‎11-21-2017 02:27 AM - last edited on ‎12-06-2017 03:40 PM by Power BI Admin MindyJ

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.

app reg 1

app reg 2

 

Register the App in Azure:

  1. Search for App registrations under More Services.
    app registration
  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
      azureapp
    4. Click Create.

 

 

Apply permissions to your application within Azure AD:

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

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.

Comments
by jeloma Frequent Visitor
on ‎12-01-2017 11:23 PM

Hi Alex, in your post for register the Power BI App, the correct URL is: dev.powerbi.com/apps , please change in point 1. Thanks and excellent post!!

by AlexHolmeset Frequent Visitor
on ‎12-01-2017 11:32 PM

Thanks, il let a admin know so it can be corrected.

by cfosund Frequent Visitor
on ‎12-02-2017 12:35 AM

Great post @AlexHolmeset 👍

by AlexHolmeset Frequent Visitor
on ‎12-03-2017 11:43 PM

Thanks :-)

by ruiromano Frequent Visitor
on ‎12-04-2017 02:15 AM

 Hi Alex, check this out:

https://github.com/DevScope/powerbi-powershell-modules/blob/master/README.md

 

Also published on the powershell gallery.

 

It uses its own app id, but you can change it to your own.

by AlexHolmeset Frequent Visitor
on ‎12-04-2017 02:56 AM

Interesting, was not aware of this module. Will definetly take a look at this one. Thanks!