Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
tianchenfu
New Member

Develop Power Query Custom Connectors using .NET

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.

1 ACCEPTED SOLUTION
CSrikanth_21
Community Support
Community Support


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
 
 

View solution in original post

2 REPLIES 2
CSrikanth_21
Community Support
Community Support


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
 
 

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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

Top Kudoed Authors