Connecting to a local API via data gateway

[stevemccall1]

Hi,

Hope someone can help.  I've set up a local API that is accessible via a data gateway in PowerApps and want to query a dataset against this.  I can't find any documentation on how to do this - is it possible?  If so, can someone point me in the right direction.

[Adamboer]

Yes, it is possible to query a dataset against a local API in PowerApps. Here are the general steps to do so: Create a connection to the local API in PowerApps. You can do this by going to the "Data" tab, selecting "Custom connectors", and then clicking "New custom connector". Follow the prompts to enter the necessary information for the API. Once the connection is established, you can use it to create a data source in PowerApps. In the "Data" tab, select "Add data source" and then select the custom connector you just created. Next, you can use the data source to create a dataset. In the "Data" tab, select "Create" and then select the data source you just added. Finally, you can query the dataset by using the standard PowerApps functions such as Filter, Sort, and Lookup. It's worth noting that the exact steps and functions may vary depending on the specific API and dataset you're working with. You may need to consult additional documentation or seek assistance from a developer familiar with PowerApps and APIs.

[stevemccall1]

Thanks - Can you give me a bit more info on how to do this please?

For this API - I will be passing a postcode as a query parameter and getting back coordinates info.  The request looks like this: http://112.220.1.4:8000/postcodes/{postcode} and returns

{
"status": 200,
"result": {
"postcode": "L1 6DP",
"quality": 1,
"eastings": 334623,
"northings": 390674,
"country": "England",
"nhs_ha": "North West",
"longitude": -2.984936,
"latitude": 53.408811,
"european_electoral_region": "North West",
"primary_care_trust": "Liverpool",
"region": "North West",
"lsoa": "Liverpool 060E",
"msoa": "Liverpool 060",
"incode": "6DP",
"outcode": "L1",
"parliamentary_constituency": "Liverpool, Riverside",
"admin_district": "Liverpool",
"parish": "Liverpool, unparished area",
"admin_county": null,
"date_of_introduction": "198001",
"admin_ward": "Central",
"ced": null,
"ccg": "NHS Cheshire and Merseyside",
"nuts": "Liverpool",
"pfa": "Merseyside",
"codes": {
"admin_district": "E08000012",
"admin_county": "E99999999",
"admin_ward": "E05000889",
"parish": "E43000166",
"parliamentary_constituency": "E14000793",
"ccg": "E38000101",
"ccg_id": "99A",
"ced": "E99999999",
"nuts": "TLD72",
"lsoa": "E01033762",
"msoa": "E02006932",
"lau2": "E08000012",
"pfa": "E23000004"
}
}
}

Ideally - I will be going through a list of data with postcodes and geting back the lat long for each row, so I think will have set up as a function.

[lbendlin]

use http://112.220.1.4:8000/postcodes/
as the base URL and then provide the dynamic part (the post code) in the RelativePath portion.

[stevemccall1]

Hi,

I just get this error:

How do I get PowerQuery to use the data dateway?  The API is only accessible via the gateway.  

For reference - this works fine if I go to a web API 

But I'm unable to use that API for my solution.

[lbendlin]

Sorry, I had assumed that you had already set up the gateway. It is always required for on-premise data sources.

[stevemccall1]

Yes - the gateway is set up and works fine when accessing via powerapps, but I don't know how to access using PowerBI.

[lbendlin]

You need to create another connection in the gateway. Power BI works different from PowerApps . You cannot share connections.

[stevemccall1]

Ok - that sounds reasonable - how do I do that?

[lbendlin]

go to Power Platform admin center (microsoft.com) and add a new Web connection with the base URL.

[stevemccall1]

Ok - I've done this in the admin center.  It's showing as status Online but still getting the same "unable to connect to remote server" error in PowerBI.

[lbendlin]

Do you have the April 2023 version of Power BI Desktop?

[stevemccall1]

Yep - 

[lbendlin]

There were some comments on the interwebs that that might be buggy. Try reverting to the March version.

Re: DataSource.Error: Web.Contents failed to get c... - Microsoft Power BI Community

[stevemccall1]

Still the same unfortunately.  Even with the March version

[lbendlin]

Are you able to consume that API from your PC via other means? Like, through a browser or wget or curl ?

[stevemccall1]

No - it is on a VM in azure - only accessible via the data gateway.

[lbendlin · Accepted solution]

Well, that's not really "local".  How are you planning to do development work?  Running Power BI Desktop on a VM that is in the same subnet?

[stevemccall1]

Ah, so it's not possible for PowerBI desktop to use the data gateway directly?

What if I create a custom connector? Use custom data connectors with the on-premises data gateway - Power BI | Microsoft Learn 

[lbendlin]

Still no. Power BI Desktop must have direct line of sight to the on-premise data sources.

[stevemccall1]

Ok , thanks - I've managed to get access directly and it's all working as expected!