Optional Step 2 - PowerShell - Append Model CSVs into Single Excel File.txt

# Define the base folder path
$baseFolderPath = "C:\Power BI Backups"

# Check and set the execution policy
$currentPolicy = Get-ExecutionPolicy -Scope CurrentUser
if ($currentPolicy -eq 'Restricted' -or $currentPolicy -eq 'Undefined' -or $currentPolicy -eq 'AllSigned') {
    Write-Host "Current execution policy is restrictive: $currentPolicy. Attempting to set to RemoteSigned."
    Set-ExecutionPolicy -ExecutionPolicy RemoteSigned -Scope CurrentUser -Force
    Write-Host "Execution policy set to RemoteSigned."
} else {
    Write-Host "Current execution policy is sufficient: $currentPolicy."
}

# Ensure required modules are installed, and imports them. If import fails, error and exit early
$requiredModules = @( 'ImportExcel', 'MicrosoftPowerBIMgmt' )
foreach ($module in $requiredModules) {
    if( -not (Import-Module $module -PassThru -EA ignore) ) {
       Install-Module -Name $module -Scope CurrentUser -Force
    }

    Import-Module $Module -ErrorAction 'stop' # In the rare case Install-Module fails, you probably want a terminating error
}

# Define the report backups path
$reportBackupsPath = Join-Path -Path $baseFolderPath -ChildPath "Model Backups"

# Get the most recent date folder within the 'Model Backups' directory
$mostRecentDateFolder = Get-ChildItem -Path $reportBackupsPath -Directory | Sort-Object LastWriteTime -Descending | Select-Object -First 1
$currentDateFolder = Join-Path -Path $reportBackupsPath -ChildPath $mostRecentDateFolder.Name

# Define the output Excel file path in the base folder
$outputExcelFile = Join-Path -Path $baseFolderPath -ChildPath "ModelDetail.xlsx"

# Check if the Excel file already exists
if (Test-Path -Path $outputExcelFile) {
    $excelExists = $true
} else {
    $excelExists = $false
}

foreach ($csvFile in (Get-ChildItem -Path $currentDateFolder -Filter *.csv)) {
    # Get the base name of the file (without extension) for the worksheet name
    $worksheetName = [System.IO.Path]::GetFileNameWithoutExtension($csvFile.FullName)
    
    # Import the CSV file
    $csvData = Import-Csv -Path $csvFile.FullName

    if ($excelExists) {
        # Append data to the existing Excel file
        $csvData | Export-Excel -Path $outputExcelFile -WorksheetName $worksheetName -AutoNameRange -Append
    } else {
        # Create a new Excel file with the data
        $csvData | Export-Excel -Path $outputExcelFile -WorksheetName $worksheetName -AutoNameRange
        $excelExists = $true
    }
}

    Write-Output "CSV files appended to $outputExcelFile"