Twitter Real-Time Analytics in PowerBI with #PowerBIPS

In SQLSaturday Paris I made a demo of Real-Time twitter analytics in PowerBI using the PowerShell module PowerBIPS.

In this post I will show how I did that (you can find the full script at the bottom), the script does the following:

  1. Imports the required modules: #PowerBIPS and #InvokeTwitterAPIs
  2. Save in a variable the Twitter OAuth settings (learn mode here)
  3. Starts a infinite loop that will pool the Twitter data and at each iteration:
    1. Calls the Twitter search API that will search for new tweets containing the #PowerBI hashtag and since the last query
      1. The script stores in a local file the “sinceId” and will use it at a next execution
    2. Parses the Twitter search results and for each tweet does some sentiment analysis using the REST APi of
    3. Sends that data to PowerBI using the Out-PowerBI cmdlet of #PowerBIPS

At the end you should be able to do a dashboard similar to this:



$ErrorActionPreference = "Stop"

$currentPath = (Split-Path $MyInvocation.MyCommand.Definition -Parent)

# Import the PowerBIPS Powershell Module:
Import-Module "$currentPath\..\PowerBIPS" -Force

# Import the InvokeTwitterAPIs module:
Import-Module "$currentPath\Modules\InvokeTwitterAPIs.psm1" -Force

#region Twitter Settings

# Learn how to generate these keys at: and
$accessToken = "your access token key"
$accessTokenSecret = "your access token secret"
$apiKey = "your api key"
$apiSecret = "your api secret"

$twitterOAuth = @{'ApiKey' = $apiKey; 'ApiSecret' = $apiSecret; 'AccessToken' = $accessToken; 'AccessTokenSecret' = $accessTokenSecret}


$sinceId = $null
$sinceIdFilePath = "$currentPath\twitterDemoSinceId.txt"

	if (Test-Path $sinceIdFilePath)
		$sinceId = Get-Content $sinceIdFilePath

	# Hashtags to search (separated by comma) and the number of tweets to return, more examples of search options:

	$twitterAPIParams = @{'q'='#powerbi';'count' = '5'}

	if (-not [string]::IsNullOrEmpty($sinceId))
		$twitterAPIParams.Add("since_id", $sinceId)

	# Ger Twitter Data (if SinceId is not Null it will get tweets since that one)

	$result = Invoke-TwitterRestMethod -ResourceURL '' -RestVerb 'GET' -Parameters $twitterAPIParams -OAuthSettings $twitterOAuth -Verbose

	# Parse the Twitter API data

	$twitterData = $result.statuses |? { [string]::IsNullOrEmpty($sinceId) -or $sinceId -ne $_.id_str } |% {
		$aux = @{
			Id = $_.id_str
			; UserId = $
			; UserName = $
			; UserScreenName = $_.user.screen_name
			; UserLocation = $_.user.location
			; Text = $_.text
			; CreatedAt =  [System.DateTime]::ParseExact($_.created_at, "ddd MMM dd HH:mm:ss zzz yyyy", [System.Globalization.CultureInfo]::InvariantCulture)		

		# Get the Sentiment Score

		$textEncoded = [System.Web.HttpUtility]::UrlEncode($aux.Text, [System.Text.Encoding]::UTF8)

		$sentimentResult = Invoke-RestMethod -Uri "$textEncoded" -Method Get -Verbose

			"0" { $aux.Add("Sentiment", "Negative") }
			"4" { $aux.Add("Sentiment", "Positive") }
			default { $aux.Add("Sentiment", "Neutral") }
		Write-Output $aux

	if ($twitterData -and $twitterData.Count -ne 0)
		# Persist the SinceId

		$sinceId = ($twitterData | Sort-Object "CreatedAt" -Descending | Select -First 1).Id
		Set-Content -Path $sinceIdFilePath -Value $sinceId

		# Send the data to PowerBI

		$twitterData | Out-PowerBI -dataSetName "TwitterPBIAnalysis" -tableName "Tweets" -types @{"Tweets.CreatedAt"="datetime"} -verbose
		Write-Output "No tweets found."

	Write-Output "Sleeping..."

	Sleep -Seconds 30

One thought on “Twitter Real-Time Analytics in PowerBI with #PowerBIPS

  1. Hi Rui, I ran into a problem that you might be able to help me with. When I run the script (PowerShell ISE, stand alone or VS 2015) I get the following error : Exception calling “AcquireToken” with “4” argument(s): “Loading an assembly required for interactive user authentication
    failed. Make sure assembly ‘Microsoft.IdentityModel.Clients.ActiveDirectory.WindowsForms, Version=, Culture=neutral,
    PublicKeyToken=31bf3856ad364e35’ exist
    Any suggestions on how to fix it, I’ve tried downloading it, installing it in the GAC but still without any success ?

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s