1. Create an Index Column starting from 0
2. Add Column Export page No where Export Page No = Number.RoundDown((IndexValue/ 150000), 0) +1 to create Export Page No
3. Create a Powershell Script to export records by Export Page No.. You may refer the script below:
4. While I can get the records exported in chunks of 150,000... I noticed that export strangely failed when the size was set for anything more than. There are only 12-13 columns in the table ad it shouldn't ideally fail for this kind of table. I have added an additional check
Import-Module MicrosoftPowerBIMgmt
Import-Module MicrosoftPowerBIMgmt.Profile
Import-Module ImportExcel
$password = "MyPassword" | ConvertTo-SecureString -asPlainText -Force
$username = "MyEmail@email.com"
$credential = New-Object System.Management.Automation.PSCredential($username, $password)
$workspaceId = "WorkspaceIdThatcabBeSeenInUrl"
$datasetId = "DataSetIdOfPBIXReport"
$dateParameter = "2023-06-01"
$exportPageSize = 30000
$pageSize = 25
$sleepPeriodShort = 10
$sleepPeriod = 10
$sleepPeriodToExecRefresh =10800
<####################################################################################
Extract outputs and Write to XLSX Files
####################################################################################>
Write-Host "START: Export to XLSX" Get-Date -Format "yyyyMMdd HH:mm:ss"
Connect-PowerBIServiceAccount -Credential $credential
try {
Write-Host "Started for " $dateParameter
$execRequestUrlCount = "datasets/" + $datasetId + "/executeQueries"
$execRequestBodyCount = @"
{
"queries":
[
{"query": "
EVALUATE SUMMARIZECOLUMNS(UA[Date], FILTER(UA, UA[Export Page No]=MAX(UA[Export Page No])), \"ExportPage\", MAX(UA[Export Page No]) )
"
}
],
"serializerSettings": {"includeNulls": true}
}
"@
# Creating the Result Variable by passing through the DAX Query to the Power BI PowerShell Module
$execResultCount = Invoke-PowerBIRestMethod -Method POST -Url $execRequestUrlCount -Body $execRequestBodyCount
#Write-Host "Completed for " $dateParameter
#Write-Host $execResultCount
# Take the results and convert it from JSON
$parsedCount = $execResultCount | ConvertFrom-Json
$outputDate = (($parsedCount.results[0].tables[0].rows[0][0]).PSObject.Properties | Select -First 1 ).Value
$outputCount = (($parsedCount.results[0].tables[0].rows[0][0]).PSObject.Properties | Select -Last 1 ).Value
Write-Host $outputDate $outputCount
if ($outputCount -gt 0) {
for ($i = 1; $i -le $outputCount; $i++) {
$filePath = ".\OneDrive\SearchAPI\UA_" + $dateParameter + "_" + $i + ".xlsx"
$execRequestUrl = "datasets/" + $datasetId + "/executeQueries"
$execRequestBody = @"
{
"queries":
[
{"query": "EVALUATE FILTER(UA, UA[Export Page No]= $i)"
}
],
"serializerSettings": {"includeNulls": true}
}
"@
$execResult = Invoke-PowerBIRestMethod -Method POST -Url $execRequestUrl -Body $execRequestBody
Write-Host $i
# Take the results and convert it from JSON
$parsed = $execResult | ConvertFrom-Json
# This is where I take the results and export it to XLSX
$output = $parsed.results[0].tables[0].rows
$output | Export-Excel -Path $filePath
}
}
}
catch {
Write-Host $_.Exception.Message -foregroundColor red -backgroundColor yellow
}
Disconnect-PowerBIServiceAccount
}
Write-Host "END: Export to XLSX " Get-Date -Format "yyyyMMdd HH:mm:ss"
