Since Power BI has gone to preview there have been a lot of great apps created by our developers that really enhance the Power BI experience. In this guest blog Rui Romano from DevScope will walk you through an application they built that really enhances the Power BI developer experience. This app is the Power BI PowerShell Modules. The PowerShell Modules for Power BI are a wrapper around the Power BI REST APIs that allows a user to call all of the API functionality without having to know things like REST or OAuth and without having to register any new applications with Azure Active Directory. Take it away Rui:
We are going to walkthrough a specific example of what you can do with the Power BI PowerShell modules. Say we wanted to upload the data in a CSV file to a dataset in Power BI and then append new data to the same dataset as new files arrive. Below is the script that someone could write:
| cls $ErrorActionPreference = “Stop” $currentPath = (Split-Path $MyInvocation.MyCommand.Definition -Parent)
#Create Archive Folder new-item -Name “Archive” -Force -ItemType directory -Path “$currentPath\CSVData” | Out-Null Import-Module “$currentPath\Modules\PowerBIPS” -Force while($true) { # Iterate each CSV file and send to PowerBI Get-ChildItem “$currentPath\CSVData” -Filter “*.csv” |% { $file=$_ #Import csv and add column with filename $data = Import-Csv $file.FullName | select @{Label=”File”;Expression={$file.Name}}, * # Send data to PowerBI $data | Out-PowerBI -dataSetName “CSVSales” -tableName “Sales” -types @{“Sales.OrderDate”=”datetime”; “Sales.SalesAmount”=”double”; “Sales.Freight”=”double”} -batchSize 300 -verbose # Archive the file Move-Item $file.FullName “$currentPath\CSVData\Archive\” -Force }
Write-Output “Sleeping…” Sleep -Seconds 5 } |
Let’s examine the pieces of the script:
| First we get all of the files with the extension .csv in the folder \CSVData | Get-ChildItem “$currentPath\CSVData” -Filter “*.csv” |% { |
| Import all the data from each file and add File column with the file name to make tracking easier | $data = Import-Csv $file.FullName | select @{Label=”File”;Expression={$file.Name}}, * |
| Then we send the data to Power BI, using Out-PowerBI command, specifying the Dataset name, table name and column types. | $data | Out-PowerBI -dataSetName “CSVSales” -tableName “Sales” -types @{“Sales.OrderDate”=”datetime”; “Sales.SalesAmount”=”double”; “Sales.Freight”=”double”} -batchSize 300 -verbose |
| Finally we move the file into an archive | Move-Item $_.FullName “$currentPath\CSVData\Archive\” –Force |
| We will continue to monitor the directory for new files to arrive | Write-Output “Sleeping…” Sleep -Seconds 5 |
Let’s see it in action.
I have a directory of CSV files:
We go ahead and run the above PowerShell script
A login dialog will show up and ask you to log into Power BI and consent to certain permissions:
Once you consent, the data upload will begin.
Once the upload is complete, the CSVData directory will now look like this:
and you will also see a new dataset created in Power BI:
You can explore this dataset by clicking on it and quickly create a Report on your csv files data:
And then pin your favorite report items to a live Power BI dashboard:
If a new CSV file is dropped into the \CSVData directory, they will automatically be uploaded, the new data will be appended to the existing PowerBI data and the dashboard will update right in front of you (no need to refresh, it will update automatically!):
DevScope have made the PowerShell modules available at: https://github.com/DevScope/powerbi-powershell-modules and have included lots of sample scripts to try. You can follow Rui Romano on his blog and twitter for more updates on PowerBI tools & resources.
This is just one example of a Power BI application that can be built. Which apps and scripts do you want to make?