API calls via Custom Connector

[GibbieMcHarg]

Hi,

We're connecting to the Sage 200 API using a custom Power BI connector provided by Sage. There is an endpoint to GET nominal data which works fine but there is also a nested table to get tax information. When we connect to the API using Postman and connecting to the URL directly with a client key, the $expand function works and returns the nested data. However, in Power Query, the $expand function doesn't work. At this stage I'm unsure if the issue is 1: our knowledge 2: Power Query limitations or 3: The Sage custom Power BI connector. 

Does anyone have experience of doing something like this via API in Power Query using a custom connector of any kind and / or using Web as a data source? and would there be a difference in behaviour in using each data source? 

TIA!

[Anonymous · Accepted solution]

Hi @GibbieMcHarg ,

I'm assuming you want:

GET http://xxxx/xxx/xxx/xxx?$expand=Friends,Trips

Refer to this document to first connect and get the data using the root URL and then add steps to convert it to the desired output based on the data structure.

 Handling transformations with Web.Contents for Power Query connectors - Power Query | Microsoft Lear...

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[GibbieMcHarg]

Excellent, thanks Gao - this is exactly what I'm looking for and answers my issue. 

Gibbie