Introduction:

Managing Power BI reports can be a time-consuming task, especially when dealing with multiple reports across different workspaces. Automating this process not only saves time but also ensures consistency and accuracy in your report management. Today, we will explore a PowerShell script designed to automate the creation and updating of Power BI reports.

1. What Does the Script Do?
   • The script automates the creation and updating of Power BI reports. It connects to your Power BI Service account, manages the required module, and processes .pbix files from a specified directory, streamlining the workflow significantly.
2. Setting Up the Script:
   • Before you run the script, you need to specify certain parameters like your workspace name, the location of your .pbix files, and where you want the output CSV file to be saved.
3. Ensuring the Right Tools:
   • A key feature of the script is its ability to check for the necessary Power BI module. It installs or updates this module as needed, ensuring that you always have the tools required for the job.
4. Creating and Updating Reports:
   • The script processes each .pbix file, creating or updating reports in your specified Power BI workspace. This means you can manage multiple reports with a single execution.
5. Tracking Your Reports:
   • As it processes the reports, the script generates detailed information, including file paths, report names, IDs, and URLs. This data is then exported to a CSV file, giving you a comprehensive record of your report management activities.
6. Ease of Use:
   • The script is designed for ease of use. Even if you’re not a PowerShell expert, you can run this script with basic knowledge of script parameters and Power BI.

Script Snippet as below:

<#

.SYNOPSIS

This PowerShell script automates the creation or update of Power BI

reports in a specified workspace, adding detailed English comments

for better understanding.

.DESCRIPTION

The script connects to the Power BI Service account, checks for the

required Power BI module, installs or updates it if necessary, and

then creates or updates Power BI reports based on .pbix files in a

specified directory. The script generates a CSV file containing

information about the processed reports, including file paths,

names, IDs, web URLs, and retrieval dates.

>

Define script parameters

param (

[string]$workspaceName = “TechnologyCuePortfolio”, # Name of the Power BI workspace

[string]$pbixFilePath = “C:\TechnologyCue\S3_SupplyChain”, # Path where .pbix files are located

[string]$prefixName = “S3_SupplyChain”, # Prefix to be added to the Power BI report names

[string]$output_path = “C:\TechnologyCue\OutputResult” # Path to store the output CSV file

)

Function to Ensure Module Existence

function Assert-ModuleExists {

param(

[string]$ModuleName

)

Check if the module is available

$module = Get-Module $ModuleName -ListAvailable -ErrorAction SilentlyContinue

if (!$module) {

Write-Host “Installing module $ModuleName …”

Install-Module -Name $ModuleName -Force -Scope CurrentUser

Write-Host “Module installed”

}

else {

Write-Host “Module $ModuleName found.”

Update the module if it’s not the desired version

if ($module.Version -lt ‘1.0.0’ -or $module.Version -le ‘1.0.410’) {

Write-Host “Updating module $ModuleName …”

Update-Module -Name $ModuleName -Force -ErrorAction Stop

Write-Host “Module updated”

}

}

}

Check and install/update required module

Assert-ModuleExists -ModuleName “MicrosoftPowerBIMgmt”

Connect to Power BI Service Account

Connect-PowerBIServiceAccount

Define Power BI workspace

$workspace = Get-PowerBIWorkspace -Name $workspaceName -ErrorAction SilentlyContinue

Create workspace if not exists

if (-not $workspace) {

$workspace = New-PowerBIWorkspace -Name $workspaceName

}

Get all .pbix files in the specified directory

$pbixFiles = Get-ChildItem -Path $pbixFilePath -Filter *.pbix

Iterate through each .pbix file and create or update reports

$reportInfo = @()

foreach ($pbixFile in $pbixFiles) {

$Pbix = $pbixFile.FullName

$reportName = “$($prefixName)_$($pbixFile.BaseName)”

Write-Host “Processing file: $($pbixFile.BaseName)”

Create or Update Power BI Report

New-PowerBIReport -Path $Pbix -WorkspaceId $workspace.Id -Name $reportName

$reportInPBI = New-PowerBIReport -Path $Pbix -WorkspaceId $workspace.Id -Name $reportName

$retrieveDate = Get-Date

$reportInfoInfo = [PSCustomObject]@{

“FilePath” = $pbixFilePath

“FileName” = $pbixFile.BaseName

“reportName” = $reportInPBI.Name

“reportId” = $reportInPBI.Id

“WebUrl” = $reportInPBI.WebUrl

“RetrieveDate” = $retrieveDate.ToString(“yyyy-MM-ddThh:mm:ss”)

}

$reportInfo += $reportInfoInfo

}

$csvFilePath = “$($output_path)\reportInfo.csv”

Check if the CSV file exists

if (-not (Test-Path $csvFilePath)) {

If the file does not exist, create it and add headers

$reportInfo | Export-Csv -Path $csvFilePath -NoTypeInformation

}

else {

If the file exists, append the data

$reportInfo | Export-Csv -Path $csvFilePath -NoTypeInformation -Append

}

Write-Host “Complete.”

Description of the PowerShell Script

This PowerShell script is designed to automate the process of creating or updating Power BI reports in a specified workspace. It is particularly useful for users who need to manage Power BI reports frequently and efficiently. Here’s a breakdown of its functionality:

1. Synopsis and Description:
   • The script’s synopsis indicates its primary function: automating the creation or update of Power BI reports.
   • The description elaborates on this, explaining that the script connects to the Power BI Service account and either installs or updates the necessary Power BI module. It processes .pbix files from a specified directory, creating or updating Power BI reports accordingly. Finally, it generates a CSV file with details of these reports.
2. Script Parameters:
   • The script accepts parameters like the workspace name, the file path of the .pbix files, a prefix for the Power BI report names, and an output path for the resulting CSV file.
3. Asserting Module Existence:
   • A function,Assert-ModuleExists, checks if the required Power BI module is present. If not, it installs it; if an older version is present, it updates it.
4. Connecting to Power BI Service:
   • The script connects to the user’s Power BI Service account.
5. Workspace Management:
   • It checks for the existence of the specified Power BI workspace and creates it if it doesn’t exist.
6. Processing .pbix Files:
   • The script finds all .pbix files in the specified directory and iterates over them. For each file, it creates or updates a Power BI report in the specified workspace.
7. Generating Report Information:
   • As it processes each file, the script generates a custom object containing details like the file path, file name, report name, report ID, web URL, and the date of retrieval.
8. Exporting Data to CSV:
   • The script checks if a CSV file exists at the specified output path. If it does not, it creates one and exports the report information to it. If the file already exists, it appends the new data.
9. Completion:
   • The script ends with a confirmation message indicating the completion of the process.

Conclusion: This PowerShell script is a powerful tool for anyone managing multiple Power BI reports. It not only saves time but also adds a level of precision to your report management process. By automating these tasks, you can focus more on data analysis and less on administrative work. Experience the efficiency and ease it brings to your workflow, and share your experiences or any modifications you make to further enhance its capabilities.