Prerequisite
Power Bi authentication using masteruser(using username and password ) OR Serviceprincipal(tenantId, application Id and application secret)
Export power bi reports into excel format using azure function .Net core V2.
function App configuration file
Http trigger azure Function
[FunctionName("ReportUtilityFunction")]
public IActionResult Run(
[HttpTrigger(AuthorizationLevel.Function, "get", "post", Route = null)] HttpRequest req,
ILogger log)
{
log.LogInformation("C# HTTP trigger function processed a request.");
PowerBiReportExpoter powerBiReportExpoter = new
PowerBiReportExpoter(_configurationManager, log, _powerBiClientConnector);
powerBiReportExpoter.ExportPowerBIReport();
return new OkObjectResult(string.Empty);There are steps to export power bi reports
Get report export Id
Polling
Get stream object and convert into excel.
using Microsoft.Extensions.Logging;
using Microsoft.PowerBI.Api;
using Microsoft.PowerBI.Api.Models;
using Microsoft.Rest;
using System;
using System.Collections.Generic;
using System.IO;
using System.Threading;
using System.Threading.Tasks;
namespace ReportUtilityFunctionApp
{
public class PowerBiReportExpoter
{
private readonly IConfigurationManager _configurationManager;
private readonly IPowerBiClientConnector _powerBiClientConnector;
private readonly ILogger _log;
public PowerBiReportExpoter(IConfigurationManager iConfigurationManager, ILogger log, IPowerBiClientConnector powerBiClientConnector)
{
_configurationManager = iConfigurationManager;
_powerBiClientConnector = powerBiClientConnector;
_log = log;
}
public async Task<ExportedFile> ExportPowerBIReport(ReportDesign report,CancellationToken token,IList<string> pageNames = null, /* Get the page names from the GetPages REST API */string urlFilter = null)
{
const int c_secToMillisec = 1000;
try
{
Export export = null;
int retryAttempt = 1;
do
{
var exportId = await PostExportRequest(report);
var httpMessage = await PollExportRequest(report.PowerBiReportId, exportId, Convert.ToInt16(_configurationManager.PollingTimeOutInMinutes), token);
export = httpMessage.Body;
if (export == null)
{
// Error, failure in exporting the report
_log.LogInformation($"{DateTime.UtcNow}: Error, failure in exporting the report");
return null;
}
if (export.Status == ExportState.Failed)
{
// Some failure cases indicate that the system is currently busy. The entire export operation can be retried after a certain delay
// In such cases the recommended waiting time before retrying the entire export operation can be found in the RetryAfter header
var retryAfter = httpMessage.Response.Headers.RetryAfter;
if (retryAfter == null)
{
// Failed state with no RetryAfter header indicates that the export failed permanently
_log.LogInformation($"{DateTime.UtcNow} : No RetryAfter header, export failed permanently");
return null;
}
var retryAfterInSec = retryAfter.Delta.Value.Seconds;
await Task.Delay(retryAfterInSec * c_secToMillisec);
}
}
while (export.Status != ExportState.Succeeded && retryAttempt++ < Convert.ToInt16(_configurationManager.MaxNumberOfRetries));
if (export.Status != ExportState.Succeeded)
{
// Error, failure in exporting the report
_log.LogInformation($"{DateTime.UtcNow} : Error, failure in exporting the report");
return null;
}
if (export.Status == ExportState.Succeeded)
{
_log.LogInformation($"{DateTime.UtcNow} : Report has been exported successfully");
}
var exportedFile = await GetExportedFile(report.PowerBiReportId, export);
return exportedFile;
}
catch
{
// Error handling
throw;
}
}
private async Task<ExportedFile> GetExportedFile(Guid reportId,Export export /* Get from the PollExportRequest response */)
{
if (export.Status == ExportState.Succeeded)
{
// The 'Client' object is an instance of the Power BI .NET SDK
var fileStream = await _powerBiClientConnector.GetToken(_configurationManager).Reports.GetFileOfExportToFileAsync(_configurationManager.WorkspaceId, reportId, export.Id);
using (MemoryStream ms = new MemoryStream())
{
fileStream.CopyTo(ms);
return new ExportedFile
{
Filebyte = ms.ToArray(),
FileSuffix = export.ResourceFileExtension,
};
}
}
return null;
}
public class ExportedFile
{
public byte[] Filebyte;
public string FileSuffix;
}
private async Task<HttpOperationResponse<Export>> PollExportRequest(Guid reportId,string exportId /* Get from the PostExportRequest response */,int timeOutInMinutes,CancellationToken token)
{
HttpOperationResponse<Export> httpMessage = null;
Export exportStatus = null;
DateTime startTime = DateTime.UtcNow;
const int c_secToMillisec = 1000;
do
{
if (DateTime.UtcNow.Subtract(startTime).TotalMinutes > timeOutInMinutes || token.IsCancellationRequested)
{
// Error handling for timeout and cancellations
_log.LogInformation($"{DateTime.UtcNow} : Timeout and cancellations");
return null;
}
// The 'Client' object is an instance of the Power BI .NET SDK
httpMessage = await _powerBiClientConnector.GetToken(_configurationManager).Reports.GetExportToFileStatusInGroupWithHttpMessagesAsync(_configurationManager.WorkspaceId, reportId, exportId);
exportStatus = httpMessage.Body;
// You can track the export progress using the PercentComplete that's part of the response
_log.LogInformation(string.Format("{0} (Percent Complete : {1}%)", exportStatus.Status.ToString(), exportStatus.PercentComplete));
if (exportStatus.Status == ExportState.Running || exportStatus.Status == ExportState.NotStarted)
{
// The recommended waiting time between polling requests can be found in the RetryAfter header
// Note that this header is not always populated
var retryAfter = httpMessage.Response.Headers.RetryAfter;
var retryAfterInSec = retryAfter.Delta.Value.Seconds;
await Task.Delay(retryAfterInSec * c_secToMillisec);
}
}
// While not in a terminal state, keep polling
while (exportStatus.Status != ExportState.Succeeded && exportStatus.Status != ExportState.Failed);
return httpMessage;
}
public async Task<string> PostExportRequest(ReportDesign report)
{
var powerBIReportExportConfiguration = new PowerBIReportExportConfiguration
{
Settings = new ExportReportSettings
{
Locale = "en-us",
},
// Note that page names differ from the page display names
// To get the page names use the GetPages REST API
// Pages = pageNames?.Select(pn => new ExportReportPage(Name = pn)).ToList(),
// ReportLevelFilters collection needs to be instantiated explicitly
// ReportLevelFilters = !string.IsNullOrEmpty(urlFilter) ? new List<ExportFilter>() { new ExportFilter(urlFilter) } : null,
};
PaginatedReportExportConfiguration paginatedReportExportConfiguration = new PaginatedReportExportConfiguration
{
ParameterValues = report.ParameterValues
};
var exportRequest = new ExportReportRequest
{
Format = FileFormat.XLSX,//_configurationManager.ReportFormat,
PaginatedReportConfiguration = paginatedReportExportConfiguration
//PowerBIReportConfiguration = powerBIReportExportConfiguration,
};
// The 'Client' object is an instance of the Power BI .NET SDK
var export = await _powerBiClientConnector.GetToken(_configurationManager).Reports.ExportToFileInGroupAsync(_configurationManager.WorkspaceId, report.PowerBiReportId, exportRequest);
// Save the export ID, you'll need it for polling and getting the exported file
return export.Id;
}
}
}
Using azure function activity, call above http trigger function from ADF pipeline.