Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

AlexHolmeset

Integrating PowerShell with Power BI (Part 1)

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

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!!

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

Great post @AlexHolmeset 👍

Thanks 🙂

 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.

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

I get this error, although the Install-Module did not throw error.

 

Connect-PowerBI : The 'Connect-PowerBI' command was found in the module 'Microsoft.PowerBI.PowerShell', but the module could not be
loaded.

Anonymous

Hello!

Relatively new Power BI developer for my company...and a complete neophyte to PowerShell and Azure. Is this the most current method for integrating PowerShell with an existing Power BI report I've built?

 

I believe what I'm seeking is pretty simple compared to what others are doing. We basically want a real-time report on our Microsoft 0365 licenses. Previously we were using data from a 3rd party to build the datasets we needed...license, cost, # purchased, # used, employee assigned, their department, etc. I was easily able to take that data in CSV format and create the report I wanted.

As we suspected, we should have access to this data already within our Azure system...question was how to access it, and how to do it in realtime. One of our other developers works almost exclusively in PowerShell and pulled what we needed...and he mentioned he was pretty sure there was a way we could fire off the PowerShell script inside of Power BI so we could get essentially real-time data.

I'm looking for the best place to start tutorial wise given my almost zero level of familiartiy with PowerShell and Azure. I do not have creds within our Azure system, so I'd need the best step by step, "PowerShell integration with Power BI for Dummies" as possible.

Thanks!