When you want to use the API Version 3.1 (actually in preview, https://docs.microsoft.com/en-us/azure/cognitive-services/text-analytics/whats-new) with Power BI and you used before the Version 2, you must do some changes. The first change is, that you must use another URL with the region inside, e.g.: https://northeurope.api.cognitive.microsoft.com/text/analytics/v3.1-preview.1/
The next change to identify the language is very easy, because the returned JSON field names not detectedLanguages in this version, the name is only detectedLanguage without an “s”. Okay, easy, right? To detect the Key Phrases there are no changes. But to detect the sentiment there are more changes to do. You see the M code below, the JSON gives us now the confidence score and it delivers three columns for positive, neutral, and negative. Before you get only one column. Mostly it is enough to analyze only the positive column, because when it contains a small number, it is more negative, when it contains 0.5, it is neutral. So, you can expand the columns with M and use only the positive column and start to analyze the data.

Attached you will find an example Power BI Report

I read so much about the Adoption of Power BI in the past. Microsoft released to this topic a great article, but I read so less about the use of the “get help” information. On the most tenants with I am worked in the past, the URL’s referring to the standard Microsoft websites and the business users do not use this information, because the most did not understand these topics … It is also hard to figure out, which department is responsible for the license assignment. Imagine that you want a Pro license – which person do you want to ask for it? You can ask your manager, but if he agree that you get one, what is the right process to get one? The URL’s can help you out. At the tenant settings you can customize these links:

There are four options:
1. Training documentation
2. Discussion Forum
3. Licensing requests
4. Help Desk
You find these at the Admin Portal -> Tenant Setting -> Get Help
Here are the endpoints:

If you modify the links, you must take care of the maintenance of the websites and keep the content up to date.

In my last Blog posts, I described how you can get data from the Power BI REST API and how you can execute them via Azure Automation. When you use the last one to execute your scripts, it is also nice to save the output 😊 Now I want to describe how you can write the output to the Azure Blob Storage. The following script exports the assigned Users to a Power BI Pro License – the same story from my last post. I have created before a Storage account and a container within.

$connectionName = "AzureRunAsConnection"

try {
    # Get the connection "AzureRunAsConnection "
    $servicePrincipalConnection=Get-AutomationConnection -Name $connectionName

     "Logging in to Azure..."
    Add-AzureRmAccount `
    -ServicePrincipal `
    -TenantId $servicePrincipalConnection.TenantId `
    -ApplicationId $servicePrincipalConnection.ApplicationId `
    -CertificateThumbprint $servicePrincipalConnection.CertificateThumbprint
}
catch {
    if (!$servicePrincipalConnection)
    {
        $ErrorMessage = "Connection $connectionName not found."
        throw $ErrorMessage
    } else{
        Write-Error -Message $_.Exception
        throw $_.Exception
    }
}

# the following user writes to the blob storage and needs also access to the AD
$myPassword = '<Password>'
$myUsername = 'philipp@plenz.onmicrosoft.com'

$password = ConvertTo-SecureString $myPassword -AsPlainText -Force
$credential = New-Object System.Management.Automation.PSCredential ($myUsername, $password)
Connect-AzureAD -Credential $credential

$date = Get-Date -Format "ddMMyyyy"

$PBIProServicePlanID = "70d33638-9c74-4d01-bfd3-562de28bd4ba"
$ProLicenseFile = 'proLicense' + $date  + '.csv'

$ADUsers = Get-AzureADUser -All $true | Select-Object ObjectId, ObjectType, DisplayName, UserPrincipalName, UserType, @{Name="Date Retrieved";Expression={$RetrieveDate}}

$OrgO365Licenses = Get-AzureADSubscribedSku | Select-Object SkuID, SkuPartNumber,CapabilityStatus, ConsumedUnits -ExpandProperty PrepaidUnits | `
    Select-Object SkuID,SkuPartNumber,CapabilityStatus,ConsumedUnits,Enabled,Suspended,Warning, @{Name="Retrieve Date";Expression={$RetrieveDate}} 
   
$UserLicenseDetail = ForEach ($ADUser in $ADUsers)
    {
        $UserObjectID = $ADUser.ObjectId
        $UPN = $ADUser.UserPrincipalName
        $UserName = $ADUser.DisplayName
        Get-AzureADUserLicenseDetail -ObjectId $UserObjectID -ErrorAction SilentlyContinue | `
        Select-Object ObjectID, @{Name="User Name";Expression={$UserName}},@{Name="UserPrincipalName";Expression={$UPN} `
        } -ExpandProperty ServicePlans
    }
$ProUsers = $UserLicenseDetail | Where-Object {$_.ServicePlanId -eq $PBIProServicePlanID}

Set-AzureRmCurrentStorageAccount -StorageAccountName savedfiles -ResourceGroupName PBI
$MSOPwd = ConvertTo-SecureString -String $myPassword -AsPlainText -Force
$MSOCred = New-Object –TypeName System.Management.Automation.PSCredential –ArgumentList $myUsername, $MSOPwd

# append the output to a CSV format
write-output $ProUsers | Export-Csv savedfiles -Append
# write the output to the blob storage
Set-AzureStorageBlobContent -Container test -File savedfiles -Blob $ProLicenseFile

After running the script, the output is available at the container and can also used at Power BI with the Blob Connector.

In a Power BI Premium license environment, only the Report creators have a Power BI Pro license, because they create Reports, Dashboards and so on. The other users, who only consume the data, are assigned to a Free license. In the past, I saw so many organization’s where users have a Pro license, because they need a in the past, but now they do not do anything with it. For an administrator it can be horrible to find out, which user really need a license, because, when he does not use the activity logs, he must ask every user which has one assigned. In a previous Blog Post, I have explained how you can get the Activities of the last 90 days into CSV files. If you run this automatically and over a long term of time, you get a good time series (https://www.flip-design.de/?p=916). With this data you can easily create a filter, to check which user creates reports or so on:

 Now you need the information which user has a Pro license assigned. To get this information, you can request your Active Directory, get this data into a CSV file, and merge it with this data. The following script exports every user into the defined file who has a license.

$myPassword = '<password>'
$myUsername = 'philipp@plenz.onmicrosoft.com'

$password = ConvertTo-SecureString $myPassword -AsPlainText -Force
$credential = New-Object System.Management.Automation.PSCredential ($myUsername, $password)
Connect-AzureAD -Credential $credential

$RetrieveDate = Get-Date 
$BasePath = "C:\Users\plenz\OneDrive\Power BI WS"
$AzureADUsersCSV = $BasePath + "Users.csv"
$OrgO365LicensesCSV = $BasePath + "OrgO365Licenses.csv"
$UserPBIProLicensesCSV = $BasePath + "UserPBIProLicenses.csv"

<#
See MS Licensing Service Plan reference: 
https://docs.microsoft.com/en-us/azure/active-directory/users-groups-roles/licensing-service-plan-reference
#>
$PBIProServicePlanID = "70d33638-9c74-4d01-bfd3-562de28bd4ba"


$ADUsers = Get-AzureADUser -All $true | Select-Object ObjectId, ObjectType, CompanyName, Department, DisplayName, JobTitle, Mail, Mobile, `
            SipProxyAddress, TelephoneNumber, UserPrincipalName, UserType, @{Name="Date Retrieved";Expression={$RetrieveDate}}

$OrgO365Licenses = Get-AzureADSubscribedSku | Select-Object SkuID, SkuPartNumber,CapabilityStatus, ConsumedUnits -ExpandProperty PrepaidUnits | `
    Select-Object SkuID,SkuPartNumber,CapabilityStatus,ConsumedUnits,Enabled,Suspended,Warning, @{Name="Retrieve Date";Expression={$RetrieveDate}} 
   
$UserLicenseDetail = ForEach ($ADUser in $ADUsers)
    {
        $UserObjectID = $ADUser.ObjectId
        $UPN = $ADUser.UserPrincipalName
        $UserName = $ADUser.DisplayName
        $UserDept = $ADUser.Department
        Get-AzureADUserLicenseDetail -ObjectId $UserObjectID -ErrorAction SilentlyContinue | `
        Select-Object ObjectID, @{Name="User Name";Expression={$UserName}},@{Name="UserPrincipalName";Expression={$UPN}}, `
        @{Name="Department";Expression={$UserDept}},@{Name="Retrieve Date";Expression={$RetrieveDate}} -ExpandProperty ServicePlans
    }
    $ProUsers
$ProUsers = $UserLicenseDetail | Where-Object {$_.ServicePlanId -eq $PBIProServicePlanID}

$ProUsers | Export-Csv $UserPBIProLicensesCSV

After that, you can create a relationship between both tables and you can check, which user really needs the Pro license, or you can assign it to another user.

Unfortunately, it is not possible to make a login to Power BI with the Cmdlet’s (Login-PowerBIServiceAccount) by Azure Automation. So, you cannot use the Power BI PowerShell commands to get very easy the requested information. But if you want to automate the running of your script, like to export some data in your Blob Storage automatically, you can use Azure Automation, but you need to make another authentication and another usage of the commands to get the data. So, you must create an authentication token, like you do that by Power BI Embedded, and then you can invoke a REST call. Sounds easy? Here is the script:

$connectionName = "AzureRunAsConnection"

try {
    # Get the connection "AzureRunAsConnection "
    $servicePrincipalConnection=Get-AutomationConnection -Name $connectionName

     "Logging in to Azure..."
    Add-AzureRmAccount `
    -ServicePrincipal `
    -TenantId $servicePrincipalConnection.TenantId `
    -ApplicationId $servicePrincipalConnection.ApplicationId `
    -CertificateThumbprint $servicePrincipalConnection.CertificateThumbprint
}
catch {
    if (!$servicePrincipalConnection)
    {
        $ErrorMessage = "Connection $connectionName not found."
        throw $ErrorMessage
    } else{
        Write-Error -Message $_.Exception
        throw $_.Exception
    }
}

# Application id from https://app.powerbi.com/apps
$clientId = "<Id>"

# Credetials of the user
$myPassword = '<Password>'
$myUsername = '<Username>'

# function to create an app token with user credentials
 function GetAuthToken {
     $redirectUri = "urn:ietf:wg:oauth:2.0:oob"
     $resourceAppIdURI = "https://analysis.windows.net/powerbi/api"
     $authority = "https://login.microsoftonline.com/common/oauth2/authorize";
     $authContext = New-Object "Microsoft.IdentityModel.Clients.ActiveDirectory.AuthenticationContext" -ArgumentList $authority
     $PWord = ConvertTo-SecureString -String $myPassword -AsPlainText -Force
     $credentials = New-Object System.Management.Automation.PSCredential $myUsername,$PWord
     $AADcredential = New-Object “Microsoft.IdentityModel.Clients.ActiveDirectory.UserCredential” -ArgumentList $credentials.UserName,$credentials.Password
     $authResult = $authContext.AcquireToken($resourceAppIdURI, $clientId, $AADcredential)
     return $authResult
}

# Save the API token
$token = GetAuthToken

# Create an autentification header
$headers = New-Object "System.Collections.Generic.Dictionary[[String],[String]]"
$headers.Add("Content-Type", "application/json")
$headers.Add("Authorization", $token.CreateAuthorizationHeader())

# Get all workspaces ...
$a = Invoke-RestMethod -Uri "https://api.powerbi.com/v1.0/myorg/groups" -Method Get -Headers $headers
foreach ($b in $a.value ) {
    $b
}

In my last Blog Post I showed you, how you can get every scheduled refresh of your Power BI tenant to organize them better and to avoid the eviction when you refresh too many datasets at the same time: https://www.flip-design.de/?p=921
To get a better timeslot, it is also helpful to know how much time are needed when a particular dataset is refreshing. For this task you can also use the Power BI REST API. The following Power Shell script exports the history and the planned refreshes as well:

After you imported both files into Power BI Desktop, you can analyze them better to find a good timeslot:

$myPassword = '<Password>'
$myUsername = '<Username>'

$password = ConvertTo-SecureString $myPassword -AsPlainText -Force
$credential = New-Object System.Management.Automation.PSCredential ($myUsername, $password)

Login-PowerBIServiceAccount -Credential $credential

$Workspaces = Get-PowerBIWorkspace

$date = Get-Date -Format "ddMMyyyy"

$HistoryFile = '<Path>\history' + $date  + '.csv'
$RefreshFile = '<Path>\Refresh' + $date + '.csv'

foreach($workspace in $Workspaces)
{
    $DataSets = Get-PowerBIDataset -WorkspaceId $workspace.Id | where {$_.isRefreshable -eq $true}    
    foreach($dataset in $DataSets)
    {
        $RefreshURI = "groups/" + $workspace.Id + "/datasets/" + $dataset.id + "/refreshes"
        $ScheduleURI = "groups/" + $workspace.Id + "/datasets/" + $dataset.id + "/refreshSchedule"
       
        $Results = Invoke-PowerBIRestMethod -Url $RefreshURI -Method Get | ConvertFrom-Json

        #
        $RefreshURISchedule = "groups/" + $workspace.Id + "/datasets/" + $dataset.id + "/refreshSchedule"      
        $ResultsSchedule = Invoke-PowerBIRestMethod -Url $RefreshURISchedule -Method Get | ConvertFrom-Json

        # Only enabled datasets fro refreshing
        if($ResultsSchedule.enabled -eq $true) {

            foreach($content in $Results.value) {           
                $row = New-Object psobject
                $row | Add-Member -Name "Workspace" -Value $workspace.Name -MemberType NoteProperty 
                $row | Add-Member -Name "WorkspaceId" -Value $workspace.Id -MemberType NoteProperty    
                $row | Add-Member -Name "Dataset" -Value $Dataset.Name -MemberType NoteProperty   
                $row | Add-Member -Name "DatasetId" -Value $Dataset.Id -MemberType NoteProperty  
                $row | Add-Member -Name "refreshType" -Value $content.refreshType -MemberType NoteProperty 
                $row | Add-Member -Name "Status" -Value $content.status -MemberType NoteProperty
                $row | Add-Member -Name "StartTime" -Value $content.startTime -MemberType NoteProperty  
                $row | Add-Member -Name "EndTime" -Value $content.endTime -MemberType NoteProperty           
                $row | Export-Csv -Path $HistoryFile -Append -Delimiter ';' -NoTypeInformation  
            }
        }

        $refreshScheduleURI = "groups/" + $workspace.Id + "/datasets/" + $dataset.id + "/refreshSchedule"
       
        $Results = Invoke-PowerBIRestMethod -Url $refreshScheduleURI -Method Get | ConvertFrom-Json
        # Only enabled datasets fro refreshing
        if($Results.enabled -eq $true) {
            $days = $Results.days -join ','
            $time = $Results.times -join ','
            $row = New-Object psobject
            $row = New-Object psobject
            $row | Add-Member -Name "Workspace" -Value $workspace.Name -MemberType NoteProperty 
            $row | Add-Member -Name "WorkspaceId" -Value $workspace.Id -MemberType NoteProperty    
            $row | Add-Member -Name "Dataset" -Value $Dataset.Name -MemberType NoteProperty   
            $row | Add-Member -Name "DatasetId" -Value $Dataset.Id -MemberType NoteProperty    
            $row | Add-Member -Name "Days" -Value $days -MemberType NoteProperty 
            $row | Add-Member -Name "Time" -Value $time -MemberType NoteProperty         
            $row | Export-Csv -Path $RefreshFile -Append -Delimiter ';' -NoTypeInformation  
        }
    }
}

After you imported both files into Power BI Desktop, you can analyze them better to find a good timeslot: