Functions/Get-BsgPBIWorkspaceAssessment.ps1

<#
    .SYNOPSIS
        Get a list of all the objects on one or more workspaces in Power BI to help to create an assesstment
         
    .DESCRIPTION
        Get a list of all the objects on one or more workspaces in Power BI.
        The result can be exported to MS Excel.
        This will help you to get an overview about the current situation of the workspaces and help to create an assessment.
        Use paramter -mode "Full" to get more assessment information.
 
    .PARAMETER PbiWorkspace
        Type: Microsoft.PowerBI.Common.Api.Workspaces.Workspace
        This is the result of the cmdlet Get-PowerBIWorkspace
 
    .PARAMETER PbiConnection
        The connection to the Power BI Tenant.
 
    .PARAMETER Path
        Path to the PBI Administration folder.
        The assessment will be stored in this folder.
        Please use the same folder for the backup.
 
    .PARAMETER Mode
        The assessment mode defines how much information you get from the assessment.
        To use "full" mode you first need to do a backup with our backup functions Backup-BsgPbiTenant or Backup-BsgPbiWorkspace.
 
    .EXAMPLE
        # Assessment (basic)
        $Path = "C:\temp\BSG PBI Administration"
        Get-PowerBIWorkspace -All | Get-bsgPbiWorkspaceAssessment -Path $Path
 
    .EXAMPLE
        # Assessment (full)
        $Path = "C:\temp\BSG PBI Administration"
        Get-PowerBIWorkspace -All | Get-bsgPbiWorkspaceAssessment -Path $Path -Mode "Full"
 
    .EXAMPLE
        # Assessment (full) for 2 specific workspaces
        $Path = "C:\temp\BSG PBI Administration"
        Get-PowerBIWorkspace -All | where-Object {$_.name -in ("BSGroup DA - Test Workspace", "BSgroup DA")} | Get-bsgPbiWorkspaceAssessment -Path $Path -PbiConnection $Pbilogin -Mode "Full"
 
    .INPUTS
 
    .OUTPUTS
 
    .NOTES
        This script uses the Power BI Management module for Windows PowerShell.
        If this module is not installed, install it by using the command 'Install-Module -Name MicrosoftPowerBIMgmt -Scope CurrentUser'.
#>


function Get-BsgPbiWorkspaceAssessment{

    param
    (
        [Parameter(Mandatory=$true)]
        [String]$Path,

        [Parameter(Mandatory=$false, ValueFromPipeline=$true)]
        [Microsoft.PowerBI.Common.Api.Workspaces.Workspace[]] $PbiWorkspace,

        [Parameter(Mandatory=$false)]
        [Microsoft.PowerBI.Common.Abstractions.Interfaces.IPowerBIProfile] $PbiConnection = $null,
        
        [Parameter(Mandatory=$false)]
        [ValidateSet("Basic", "Full")]
        [String[]]$Mode = "Basic"
    )

    BEGIN {

        $Filename_Assessment = "Powerbi_Assessment_" + $Mode + "_" + (Get-Date -format "yyyyMMdd_hhmmss") + ".xlsx"
        $Path_Assessment = Join-Path -Path $Path -ChildPath "Assessment"
        $Path_AssessmentFile = Join-Path -Path $Path_Assessment -ChildPath $Filename_Assessment
        $Path_Backup = Join-Path -Path $Path -ChildPath "Backup"


        if ($Mode -eq "Full"){
            if ((Test-Path $Path_Backup) -eq $false){
                throw "The Backup Subfolder does not exist. It means, it doesn't have a full backup in the location and it is necessary to generate a Full assessment."
            }
        }

        Write-Host
        Write-Host
        Write-PSFHostColor -Level Host -DefaultColor white -String "---------------------------------------------------------------------------------------------"
        Write-Host
        Write-PSFHostColor -Level Host -DefaultColor green -String (" Power BI Service assessment (" + $Mode + ") started... ")
        Write-Host
        Write-PSFHostColor -Level Host -DefaultColor gray  -String " Location: <c='white'>$Path_Assessment</c>"
        Write-Host
        Write-PSFHostColor -Level Host -DefaultColor white -String "---------------------------------------------------------------------------------------------"
        Write-Host
        Write-Host



        $assessmentWorkspace = @()
        $assessmentWorkspaceUsers = @()
        $assessmentDatasets = @()
        $assessmentDatasources = @()
        $assessmentDataflows = @()
        $assessmentDataflowsources = @()
        $assessmentReports = @()
        $assessmentDashboards = @()
        $assessmentDashboardTiles = @()


        # ===
        # Get tenant level information
        # =

        if ($Mode -eq "Full"){
            $orphanDatasets = Get-BsgPbiOrphanDatasets -Path $Path -SkipConfirmationMessage $true
            $DatasetsWithoutOwner = Get-BsgPbiDatasetsWithoutOwner -Path $Path -SkipConfirmationMessage $true
            $AllDatasetReport_Mappings = Get-BsgPbiAllMappingFiles -Path $Path
        } else {
            $orphanDatasets = @()
            $DatasetsWithoutOwner = @()
            $AllDatasetReport_Mappings = @()
        }

        $AllWorkspaces = Get-PowerBIWorkspace -All -Scope Organization

        $NumOfOrphanDatasets = 0
        $NumOfDatasetsWithoutOwner = 0
        $NumOfReportsSkipped = 0
        $NumOfWorkspacesWithSameName = 0

        Write-PSFHostColor -Level Host -DefaultColor white -String "Getting information for each workspace..."
        
    }
    PROCESS {

        

        foreach ($pbiws in $PbiWorkspace){
            try {
                Write-PSFHostColor -Level Host -DefaultColor gray -String ("- " + $pbiws.Name)

                $WorkspaceAction = ""
                $WorkspaceProblem = ""
                $DuplicateWorkspaces = $AllWorkspaces | Where-Object {$_.name -eq $pbiws.name}
                
                if (@($DuplicateWorkspaces).length -gt 1){
                    $NumOfWorkspacesWithSameName += 1
                    $WorkspaceProblem = 'duplicate name'
                    $WorkspaceAction = 'rename workspace'
                }
                
                $pbiwsEncriptationStatus = Get-PowerBIWorkspaceEncryptionStatus -Id $pbiws.Id -ErrorAction SilentlyContinue
                if (!$pbiwsEncriptationStatus){
                    $pbiwsEncriptationStatus = 'Unauthorized'
                }
                
                $assWorkspace = [PSCustomObject]@{
                    WorkspaceId = $pbiws.Id
                    Workspace = $pbiws.Name
                    Description = $pbiws.Description
                    Problem = $WorkspaceProblem
                    Action = $WorkspaceAction
                    Type = $pbiws.Type
                    State = $pbiws.State
                    IsReadOnly = $pbiws.IsReadOnly
                    IsOnDedicatedCapacity = $pbiws.IsOnDedicatedCapacity
                    CapacityId = $pbiws.CapacityId
                    EncriptationStatus = $pbiwsEncriptationStatus
                }
                $assessmentWorkspace = $assessmentWorkspace + $assWorkspace

                
                $wsusers = Invoke-PowerBIRestMethod -Url "groups/$($pbiws.Id)/users" -Method Get | ConvertFrom-Json
                foreach ($u in $wsusers){
                    foreach ($uu in $u.value){
                        $assUser = [PSCustomObject]@{
                            WorkspaceId = $pbiws.Id
                            Workspace = $pbiws.Name
                            emailAddress = $uu.emailAddress
                            groupUserAccessRight = $uu.groupUserAccessRight
                        }
                        $assessmentWorkspaceUsers = $assessmentWorkspaceUsers + $assUser
                    }
                }
                
                $wsreports = Get-PowerBIReport -WorkspaceId $pbiws.Id
                foreach($rep in $wsreports){

                    # restore action
                    $restoreAction = ''
                    $restoreDescription = ''
                    if ($Mode -eq "Full"){
                        if ($rep.Id -in $AllDatasetReport_Mappings.reportId){
                            $ReportOfBackup = $AllDatasetReport_Mappings | Where-Object {$_.reportId -eq $rep.Id}
                            $restoreAction = $ReportOfBackup.restoreAction
                            $restoreDescription = $ReportOfBackup.restoreDescription
                        }
                    } else{
                        $restoreAction = 'unknown'
                        $restoreDescription = 'Only available in full assessment mode.'
                    }

                    # set assessment level variables
                    if ($restoreAction -eq 'Skip'){
                        $NumOfReportsSkipped += 1
                    }

                    $assReport = [PSCustomObject]@{
                        WorkspaceId = $pbiws.Id
                        Workspace = $pbiws.Name
                        ReportId = $rep.Id
                        ReportName = $rep.Name
                        restoreAction = $restoreAction
                        restoreDescription = $restoreDescription
                        DatasetId = $rep.DatasetId
                        WebUrl = $rep.WebUrl
                        EmbedUrl = $rep.EmbedUrl
                    }
                    $assessmentReports = $assessmentReports + $assReport
                }

                
                
                # $wsreportdatasets = $wsreports | ForEach-Object{ $_.DatasetId} | Select-Object -Unique

                $wsdatasets = Get-PowerBIDataset -WorkspaceId $pbiws.Id

                foreach($ds in $wsdatasets){
                    $usage = 'unknown'
                    $comment = 'Only available in full assessment mode.'
                    if ($Mode -eq "Full"){
                        $usage = 'is used'
                        $comment = 'At least 1 report is bound to the dataset.'
                        if ($ds.Id -in $orphanDatasets.id){
                            $DatasetIsOrphan = $true
                            $DatasetHasNoOwner = $true
                            $usage = 'is orphan'
                            $comment = 'Not used by any report.'
                        } else{
                            $DatasetIsOrphan = $false
                            if ($ds.Id -in $DatasetsWithoutOwner.id){
                                $DatasetHasNoOwner = $true
                                $usage = 'has no owner'
                                $comment = 'No report is directly bound to the dataset.'
                            }else{
                                $DatasetHasNoOwner = $false
                            }
                        }
                    }

                    # set assessment level variables
                    if ($DatasetIsOrphan -eq $true){
                        $NumOfOrphanDatasets += 1
                    }
                    if ($DatasetHasNoOwner -eq $true){
                        $NumOfDatasetsWithoutOwner += 1
                    }
                    

                    $assDataset = [PSCustomObject]@{
                        WorkspaceId = $pbiws.Id
                        Workspace = $pbiws.Name
                        DatasetId = $ds.Id
                        Dataset = $ds.Name
                        Usage = $usage
                        Comment = $comment
                        WebUrl = $ds.WebUrl
                        ConfiguredBy = $ds.ConfiguredBy
                        DefaultRetentionPolicy = $ds.DefaultRetentionPolicy
                        AddRowsApiEnabled = $ds.AddRowsApiEnabled
                        DefaultMode = $ds.DefaultMode
                        Datasources = $ds.Datasources
                        IsRefreshable = $ds.IsRefreshable
                        IsEffectiveIdentityRequired = $ds.IsEffectiveIdentityRequired
                        IsEffectiveIdentityRolesRequired = $ds.IsEffectiveIdentityRolesRequired
                        IsOnPremGatewayRequired = $ds.IsOnPremGatewayRequired
                        TargetStorageMode = $ds.TargetStorageMode
                        ActualStorage = $ds.ActualStorage
                    }
                    $assessmentDatasets = $assessmentDatasets + $assDataset


                    $wsdatasources = Get-PowerBIDatasource -DatasetId $ds.Id -WorkspaceId $pbiws.Id
                    foreach($dsrc in $wsdatasources){
                        $assDatasource = [PSCustomObject]@{
                            WorkspaceId = $pbiws.Id
                            Workspace = $pbiws.Name
                            DatasetId = $ds.Id
                            Dataset = $ds.Name
                            DatasourceId = $dsrc.DatasourceId
                            Datasource = $dsrc.Name
                            ConnectionString = $dsrc.ConnectionString
                            DatasourceType = $dsrc.DatasourceType
                            GatewayId = $dsrc.GatewayId
                            DetailServer = $dsrc.ConnectionDetails.Server
                            DetailDatabase = $dsrc.ConnectionDetails.Database
                            DetailUrl = $dsrc.ConnectionDetails.Url
                        }
                        $assessmentDatasources = $assessmentDatasources + $assDatasource
                    }
                }

                $wsdataflows = Get-PowerBIDataflow -WorkspaceId $pbiws.Id
                foreach($df in $wsdataflows){
                    $assDataflow = [PSCustomObject]@{
                        WorkspaceId = $pbiws.Id
                        Workspace = $pbiws.Name
                        DataflowId = $df.Id
                        Dataflow = $df.Name
                        ConfiguredBy = $df.ConfiguredBy
                        Description = $df.Description
                        ModelUrl = $df.ModelUrl
                    }
                    $assessmentDataflows = $assessmentDataflows + $assDataflow

                    $wsdataflowsources = Get-PowerBIDataflowDatasource -DataflowId $df.Id -WorkspaceId $pbiws.Id
                    $assessmentDataflowsources = $assessmentDataflowsources + $wsdataflowsources
                }





                $wsdashboards = Get-PowerBIDashboard -WorkspaceId $pbiws.Id
                foreach($db in $wsdashboards){
                    $assDashboard = [PSCustomObject]@{
                        WorkspaceId = $pbiws.Id
                        Workspace = $pbiws.Name
                        DashboardId = $db.Id
                        DashboardName = $db.Name
                        IsReadOnly = $db.IsReadOnly
                        EmbedUrl = $db.EmbedUrl
                    }
                    $assessmentDashboards = $assessmentDashboards + $assDashboard

                    $wsdashboardtiles = Get-PowerBITile -WorkspaceId $pbiws.Id -DashboardId $db.Id
                    foreach($tl in $wsdashboardtiles){
                        $assDashboardTile = [PSCustomObject]@{
                            WorkspaceId = $pbiws.Id
                            Workspace = $pbiws.Name
                            DashboardId = $db.Id
                            DashboardName = $db.Name
                            DashboardTileId = $tl.Id
                            DashboardTileName = $tl.Name
                            RowSpan = $tl.RowSpan
                            ColumnSpan = $tl.ColumnSpan
                            ReportId = $tl.ReportId
                            DatasetId = $tl.DatasetId
                            EmbedUrl = $tl.EmbedUrl
                            EmbedData = $tl.EmbedData
                        }
                        $assessmentDashboardTiles = $assessmentDashboardTiles + $assDashboardTile
                    }
                }

            }
            catch {
                Write-Host
                Stop-PSFFunction -Message ("Could not export workspace " + $pbiws.Name) -EnableException $False -Errorrecord $_
                return
            }
        }
    }
    END {

        # ===
        # Generate excel sheets
        # =

        $wsEncriptationKey = Get-PowerBIEncryptionKey -ErrorAction SilentlyContinue
        if (!$PbiWorkspaceEncriptationStatus){
            $wsEncriptationKey = 'Unauthorized'
        }
        
        if ($null -ne $Path_AssessmentFile -and "" -ne $Path_AssessmentFile){

            # Introduction Sheet
            $title = "Power BI Assessment"
            if ($Mode -eq "Full"){
                $AssessmentMode = "Full"
            } else{
                $AssessmentMode = "Basic"
            }
            $intro = (
                [PSCustomObject]@{
                    A = ""
                    B = ""
                },
                [PSCustomObject]@{
                    A = "Assessment Mode"
                    B = $AssessmentMode
                },
                [PSCustomObject]@{
                    A = "Creation Date"
                    B = Get-Date | Out-String
                },
                [PSCustomObject]@{
                    A = "Encriptation Key"
                    B = $wsEncriptationKey
                }
            )    
            if ($null -ne $PbiConnection){
                $intro = $intro + (
                    [PSCustomObject]@{
                        A = ""
                        B = ""
                    },
                    [PSCustomObject]@{
                        A = "Environment"
                        B = $PbiConnection.Environment 
                    },
                    [PSCustomObject]@{
                        A = "Tennant Id"
                        B = $PbiConnection.TenantId  
                    },
                    [PSCustomObject]@{
                        A = "Username"
                        B = $PbiConnection.UserName
                    },
                    [PSCustomObject]@{
                        A = "Login type"
                        B = $PbiConnection.LoginType    
                    }
                )
            }
            $intro | Export-Excel -path $Path_AssessmentFile -WorkSheetname "Introduction" -AutoSize -NoHeader -StartRow 2 -Title $title -TitleSize 20
            # $ws = $excelpkg.Workbook.Worksheets['Introduction']
            # Write-Host $ws
            # $xlParams = @{WorkSheet=$ws;Bold=$true;FontSize=18}
            # Set-Format -Range A4 -WorkSheet $ws -Bold #@xlParams

            # Workspace Sheet
            $ConditionalFormat =$(
                New-ConditionalText -Text "duplicate name" -Range "D:D" -BackgroundColor Red -ConditionalTextColor White
            )
            $assessmentWorkspace | Export-Excel -path $Path_AssessmentFile -WorkSheetname "Workspaces" -TableName "Workspaces" -AutoSize -TableStyle Light8 -ConditionalFormat $ConditionalFormat
            $assessmentWorkspaceUsers | Export-Excel -path $Path_AssessmentFile -WorkSheetname "WorkspaceUsers" -TableName "WorkspaceUsers" -AutoSize -TableStyle Light8

            # Dataset Sheet
            $ConditionalFormat =$(
                New-ConditionalText -Text "is orphan" -Range "E:E" -BackgroundColor Red -ConditionalTextColor White
                New-ConditionalText -Text "has no owner" -Range "E:E" -BackgroundColor Red -ConditionalTextColor White
                New-ConditionalText -Text "unknown" -Range "E:E" -BackgroundColor Orange -ConditionalTextColor White
                New-ConditionalText -Text 'is used' -Range "E:E" -BackgroundColor Green -ConditionalTextColor White
            )
            $assessmentDatasets | Export-Excel -path $Path_AssessmentFile -WorkSheetname "Datasets" -TableName "Datasets" -ConditionalFormat $ConditionalFormat -AutoSize -TableStyle Light8

            # Datasource Sheet
            $assessmentDatasources | Export-Excel -path $Path_AssessmentFile -WorkSheetname "Datasources" -TableName "Datasources" -AutoSize -TableStyle Light8

            # Dataflow Sheet
            $assessmentDataflows | Export-Excel -path $Path_AssessmentFile -WorkSheetname "Dataflows" -TableName "Dataflows" -AutoSize -TableStyle Light8

            # Dataflowsources Sheet
            $assessmentDataflowsources | Export-Excel -path $Path_AssessmentFile -WorkSheetname "Dataflowsources" -TableName "Dataflowsources" -AutoSize -TableStyle Light8

            # Report Sheet
            $ConditionalFormat =$(
                New-ConditionalText -Text "Skip_UsageMetrics" -Range "E:E" -BackgroundColor Orange -ConditionalTextColor White
                New-ConditionalText -Text "Skip" -Range "E:E" -BackgroundColor Red -ConditionalTextColor White
                New-ConditionalText -Text "CreateDataset" -Range "E:E" -BackgroundColor Green -ConditionalTextColor White
                New-ConditionalText -Text "RebindReport" -Range "E:E" -BackgroundColor Green -ConditionalTextColor White
                New-ConditionalText -Text "UpdatePbiConnection" -Range "E:E" -BackgroundColor Green -ConditionalTextColor White
                New-ConditionalText -Text "Wait_UpdatePbiConnection" -Range "E:E" -BackgroundColor Green -ConditionalTextColor White
                New-ConditionalText -Text "CreateDataset_RebindReport" -Range "E:E" -BackgroundColor Green -ConditionalTextColor White
                New-ConditionalText -Text "unknown" -Range "E:E" -BackgroundColor Orange -ConditionalTextColor White
            )
            $assessmentReports | Export-Excel -path $Path_AssessmentFile -WorkSheetname "Reports" -TableName "Reports" -ConditionalFormat $ConditionalFormat -AutoSize -TableStyle Light8

            # Dashboard Sheet
            $assessmentDashboards | Export-Excel -path $Path_AssessmentFile -WorkSheetname "Dashboards" -TableName "Dashboards" -AutoSize -TableStyle Light8

            # Dashboard Tiles Sheet
            $excelpkg = $assessmentDashboardTiles | Export-Excel -path $Path_AssessmentFile -WorkSheetname "DashboardTiles" -TableName "DashboardTiles" -AutoSize -TableStyle Light8 -PassThru

            # Warnings
            $NumOfDatasetsWithoutOwner = $NumOfDatasetsWithoutOwner - $NumOfOrphanDatasets
            if ($BackupExists -eq $false){
                Write-Host
                Write-Warning "Not all information found."
                Write-Host
                Write-PSFHostColor -Level Host -DefaultColor gray -String "<c='white'>Info</c>: In assessment basic mode, not all information can be found."
                Write-PSFHostColor -Level Host -DefaultColor gray -String "Some information (f.e. orphan datasets or reports which cannot be restored) can only be found after you export each object."
                Write-PSFHostColor -Level Host -DefaultColor gray -String "To include the information please run <c='white'>Backup-BsgPbiTenant</c>... or <c='white'>Backup-BsgPbiWorkspace</c>... first."
                Write-PSFHostColor -Level Host -DefaultColor gray -String "After the backup is done, run the assessment again with <c='white'>-Mode `"Full`"</c>"
            }

            if ($NumOfWorkspacesWithSameName -eq 1){
                Write-Host
                Write-Warning "$NumOfWorkspacesWithSameName workspace has a duplicate name. The workspace will not be restored."
                Write-Host
                Write-PSFHostColor -Level Host -DefaultColor gray -String "<c='white'>Info</c>: Please have a look at the <c='white'>workspaces marked as `"duplicate name`"</c> in the excel file."
                Write-PSFHostColor -Level Host -DefaultColor gray -String "In order to restore the workspace, you need to <c='white'>rename the workspace</c> in PBI Service."
            }
            if ($NumOfWorkspacesWithSameName -gt 1){
                Write-Host
                Write-Warning "$NumOfWorkspacesWithSameName workspaces have a duplicate name. They will not be restored."
                Write-Host
                Write-PSFHostColor -Level Host -DefaultColor gray -String "<c='white'>Info</c>: Please have a look at the <c='white'>workspaces marked as `"duplicate name`"</c> in the excel file."
                Write-PSFHostColor -Level Host -DefaultColor gray -String "In order to restore those workspaces, you need to <c='white'>rename each workspaces</c> in PBI Service."
            }

            if ($NumOfOrphanDatasets -eq 1){
                Write-Host
                Write-Warning "$NumOfOrphanDatasets orphan dataset (dataset is not used by any report). It will not be restored."
                Write-Host
                Write-PSFHostColor -Level Host -DefaultColor gray -String "<c='white'>Info</c>: Please have a look at the <c='white'>dataset marked as `"is orphan`"</c> in the excel file."
                Write-PSFHostColor -Level Host -DefaultColor gray -String "No more used datasets should be deleted before starting the restoration."
                Write-PSFHostColor -Level Host -DefaultColor gray -String "In order to restore this dataset, you need to <c='white'>save the dataset as a report</c> in PBI Service."
            } elseif ($NumOfOrphanDatasets -gt 1){
                Write-Host
                Write-Warning "$NumOfOrphanDatasets orphan datasets (datasets are not used by any report). They will not be restored."
                Write-Host
                Write-PSFHostColor -Level Host -DefaultColor gray -String "<c='white'>Info</c>: Please have a look at the <c='white'>datasets marked as `"is orphan`"</c> in the excel file."
                Write-PSFHostColor -Level Host -DefaultColor gray -String "In order to restore those datasets, you need to <c='white'>save each dataset as a report</c> in PBI Service."
            }

            if ($NumOfDatasetsWithoutOwner -eq 1){
                Write-Host
                Write-Warning "$NumOfDatasetsWithoutOwner dataset without owner (dataset has no report bound to it). It will not be restored."
                Write-Host
                Write-PSFHostColor -Level Host -DefaultColor gray -String "<c='white'>Info</c>: Please have a look at the <c='white'>dataset marked as `"has no owner`"</c> in the excel file."
                Write-PSFHostColor -Level Host -DefaultColor gray -String "In order to restore this dataset, you need to <c='white'>save the dataset as a report</c> in PBI Service."
            } elseif ($NumOfDatasetsWithoutOwner -gt 1){
                Write-Host
                Write-Warning "$NumOfDatasetsWithoutOwner datasets without owner (datasets have no report bound to them). They will not be restored."
                Write-Host
                Write-PSFHostColor -Level Host -DefaultColor gray -String "<c='white'>Info</c>: Please have a look at the <c='white'>datasets marked as `"has no owner`"</c> in the excel file."
                Write-PSFHostColor -Level Host -DefaultColor gray -String "In order to restore those datasets, you need to <c='white'>save each dataset as a report</c> in PBI Service."
            }

            if ($NumOfReportsSkipped -eq 1){
                Write-Host
                Write-Warning "$NumOfReportsSkipped report cannot be exported. It will not be restored."
                Write-Host
                Write-PSFHostColor -Level Host -DefaultColor gray -String "<c='white'>Info</c>: Please have a look at the <c='white'>report marked as `"Skip`"</c> in the excel file."
            } elseif ($NumOfReportsSkipped -gt 1){
                Write-Host
                Write-Warning "$NumOfReportsSkipped reports cannot be exported. They will not be restored."
                Write-Host
                Write-PSFHostColor -Level Host -DefaultColor gray -String "<c='white'>Info</c>: Please have a look at the <c='white'>reports marked as `"Skip`"</c> in the excel file."
            }

            Write-Host
            Write-PSFHostColor -Level Host -DefaultColor white -String "Opening excel file..."
            Close-ExcelPackage  -ExcelPackage $excelpkg -Show


            Write-Host
            Write-Host
            Write-PSFHostColor -Level Host -DefaultColor white -String "---------------------------------------------------------------------------------------------"
            Write-Host
            if ($Mode -eq "Full"){
                Write-PSFHostColor -Level Host -DefaultColor green -String " Power BI Service assessment (full) finished."
            } else{
                Write-PSFHostColor -Level Host -DefaultColor green -String " Power BI Service assessment (basic) finished."
            }
            Write-Host
            Write-PSFHostColor -Level Host -DefaultColor gray -String " Location: <c='white'>$Path_AssessmentFile</c>"
            Write-Host
            Write-PSFHostColor -Level Host -DefaultColor gray -String " Developed by <c='white'>BSGroup Data Analytics AG</c>"
            Write-PSFHostColor -Level Host -DefaultColor white -String "---------------------------------------------------------------------------------------------"
            Write-Host
            Write-Host

        }else {
            $assessmentWorkspace
        }

        
    }    
}