Power BI: Accessing Data From Azure Hosted ASP.NET Core API to create Chart
In the modern days of the application development the Power BI has been used a lot to turn related and unrelated data into the coherent, visually immersive and interactive insights. The data can be received from Excel Spreadsheets, SQL Server Database, cloud-based applications, etc. As shown in Figure 1, the Power BI can access data from various sources.
Figure 1: The Get Data Features
As shown in Figure 1, Power BI Uses various sources for accessing data so that it can be used by the business decision makers to get data for data transformation and use it in Visualize form to create charts
Important Note: To Create database in Azure SQL and Publish API to cloud, you must have the Azure Subscription.
Currently, most the data-oriented applications use REST APIs for data communications and Data fetching. Naturally, if we are using the Power BI for Data Visualization and Data Intelligence then what if the Data is to be received from the REST API then how we can access it in Power BI? In this article we will see the process of accessing the Data in Power BI from the REST API. Figure 2 explains the process of data access.
Figure 2: Power BI Accessing Data from REST API
In this article, we will use the Power BI to show the Expenses data in Visual Chart form. To access the data, in AZURE SQL I have already created a database named Company. with Expenses Details in it. Listing 1 shows the script for Expenses Table.
CREATE DATABASE Company;
Use Company;
CREATE TABLE [dbo].[Expenses]( [ExpensesId] [float] Primary Key, [VendorName] [nvarchar](255) NULL, [PaymentDate] [datetime] NULL, [ExpensesType] [nvarchar](255) NULL, [AmountPaid] [float] NULL, [PaymentMethod] [nvarchar](255) NULL, [PaidBy] [nvarchar](255) NULL )
Listing 1: The Expenses Table
The ASP.NET Core API is created using Visual Studio 2022 targeted to .NET 8. The ASP.NET Core 8 uses Entity Framework Core. The ASP.NET Core API project uses Database First approach to generate Table mapping and creating Expenses Entities. The Listing 2 shows command to generate entities.
dotnet ef dbcontext scaffold "Data Source=[DATABASE-SERVER-NAME];Initial Catalog=Company;Integrated Security=SSPI;TrustServerCertificate=True" Microsoft.EntityFrameworkCore.SqlServer -o Models -t Expenses
Listing 2: The command to generate entities using Database First Approach
The API project has the following code to that uses the data access layer generated using Entity Framework Core using Database First approach to fetch data from the Company database created in Azure SQL. The code in Listing 3 shows the Expenses API created using Minimal API.
using API_Expenses.Models;
using Microsoft.EntityFrameworkCore;
var builder = WebApplication.CreateBuilder(args);
// Add services to the container.
builder.Services.AddDbContext<CompanyContext>(
options => options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection"))
);
builder.Services.Configure<Microsoft.AspNetCore.Http.Json.JsonOptions>(options =>
{
options.SerializerOptions.PropertyNamingPolicy = null; // Use original property names
options.SerializerOptions.WriteIndented = true; // Pretty print JSON
options.SerializerOptions.PropertyNameCaseInsensitive = true; // Case-insensitive property names
});
// Learn more about configuring Swagger/OpenAPI at https://aka.ms/aspnetcore/swashbuckle
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();
var app = builder.Build();
// Configure the HTTP request pipeline.
app.UseSwagger();
app.UseSwaggerUI();
app.UseHttpsRedirection();
app.MapGet("/expenses", async() =>
{
using var db = new CompanyContext();
var expenses = await db.Expenses.ToListAsync();
return Results.Ok(expenses);
});
app.Run();
Listing 3: The API
Publish the API to Azure. Figure 3, shows the published API Swagger in browser.
Figure 3: The Browser Access
We get the Expenses Data. Now let's use Power BI Desktop to create a visual. The Power BI desktop can be downloaded from this link.
Open the Power BI Desktop, select the Blank Report, as shown in Figure 4
Figure 4: The Blank Report
Click on the Get Data, Web, as shown in Figure 5.
Figure 5: The Get Data from Web
In the From Web dialog box, select the Basic radio button and enter the API URL as shown in Figure 6.
Figure 6: The From Web Box
Click on OK button. The Power BI Desktop, will fetch the data by making the call to the REST API and the data will be shown in the Power Query View as shown in Figure 7
Figure 7: The Power Query View
As shown in Figure 7, the expenses data is loaded. In the Power Query View, we can perform Data Transformation. To load the data in the Power BI, click on the Close & Apply button. The model data will be shown in the Power BI desktop as sown in Figure 8
Figure 8: The Model Data
Let's design the Line and Stack Chart to show expense amount paid to each vendor. To design the visual, we will show VendorName on x-axis and on Column y-axis we will show AmountPaid, since the visual is Line and Stack Chart, on the Line-y-axis we will show the AmountPaid as a Line. The Column Legend will have ExpenseType.
Drag-Drop the Line and Stack Chart on the canvas and as explained above use VendorName, AmountPaid, ExpenseType to see the Visual as shown in Figure 9
Figure 9: The Visual
So, we have the data from REST API and the Visual is Created.
To See the value updates, visit to Azure SQL and from the Company database, update the AmountPaid to VendorName Ajrag by multiplying by 20. To refresh the modified data, in Power BI Right Click on the expenses model and select the Refresh data as shown in Figure 10.
Figure 10: The Refresh Data
The Power BI Desktop will perform data refresh, and the Visual will be updates as shown in Figure 11.
Figure 11: The Updated Visual
That's it. This is how we can use Poer BI Desktop to connect to REST API and fetch data to create a visual.
Conclusion: Power BI Desktop Integration with REST API helps Power BI Developers to create an interactive Visual.
