Develop Power Query Custom Connectors using .NET

[tianchenfu]

Hi, I'm currently developing connectors for my company's database product. This product uses its own protocol, so we've developed numerous APIs for database access, including a C# API. I've reviewed the Power Query custom connector documentation, but it only mentions how to query using M language. To support our database, it seems I might have to use WebContents and then develop a separate backend in C# or another language to support REST access. However, this would inconvenience our customers, as they would need to download an additional executable file. I'd like to ask if there's a way to develop a Power Query connector using .NET, since we already have a C# API. Please forgive me if my understanding is incorrect.

[v-csrikanth · Accepted solution]

Hi @tianchenfu 

Thanks for using the Microsoft Fabric Community.

Best Approach for combining .NET API with REST Access for Power Query

Step1:

1. Expose Your C# API as a REST Service

• Use ASP.NET Core Web API to expose your database's custom protocol via REST endpoints.
• Design the REST API to handle all necessary operations (e.g., querying data, filtering, sorting).

Example: C# REST API Endpoint

***************************************************************************

[ApiController]

[Route("api/[controller]")]

public class DataController : ControllerBase

{

    private readonly IYourDatabaseApi _databaseApi;

    public DataController(IYourDatabaseApi databaseApi)

    {

        _databaseApi = databaseApi;

    }

    [HttpGet("query")]

    public IActionResult QueryData([FromQuery] string filter)

    {

        var results = _databaseApi.Query(filter);

        return Ok(results);

    }

}

***************************************************************************

Deploy this API on a secure server (e.g., Azure, AWS, or on-premise).

Use HTTPS and implement authentication (OAuth2, API Keys, etc.) to ensure data security.

2. Create a Power Query Custom Connector

Power Query custom connectors are built using the Power Query SDK and written in the M language. 

The custom connector interacts with your REST API.

Install the Power Query SDK:

• Use Visual Studio to install the Power Query SDK.
• Create a new project for your connector.

Define the Data Source:

• Use Web.Contents to connect to your REST API endpoints.
• Pass query parameters dynamically from Power BI to the API.

******************************************************************************

Example: M Code for the Connector

[DataSource.Kind="YourConnector", Publish="YourConnector.Publish"]

shared YourConnector.Contents = (filter as text) =>

let

    Source = Web.Contents("https://api.yourcompany.com/query", [

        Query = [filter = filter],

        Headers = [Authorization = "Bearer " & Extension.CurrentCredential()]

    ]),

    Data = Json.Document(Source)

in

    Data;

******************************************************************************

Add Authentication:

Configure authentication (OAuth2, API keys, etc.) in your connector project.

Example of adding a token:

******************************************************************************

[DataSource.Kind="YourConnector"]

YourConnector = [

    Authentication = [

        OAuth = [

            StartLogin = StartLogin,

            FinishLogin = FinishLogin

        ]

    ]

];

******************************************************************************

Test Your custom connector:

• Debug the connector by loading it into Power BI Desktop.
• Verify that it retrieves data from your API as expected.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Thanks,

Cheri Srikanth

[tianchenfu]

Thanks Cheri, I now understand how to query data using the REST API in Power Query. Best wishes!