Power Query in Excel using Business Central API

[Syndicate_Admin]

Hi All, 

I'm new to this community but have a feeling I'll be spending a bit of time here. Currently, I'm looking for some help.

I am an experienced report builder and Business Central consultant who is comfortable using Power Query in both Power Bi and Excel (depending on the intended outcome) but I am not a technical user with detailed understanding of APIs and web services.

Traditionally, when connecting Excel to Business Central as a data source, I have used Odata web service feeds. These are simple to use but often require a web service creating within Business Central. 

As of last year, Power Bi uses v2.0 API's that connect to many more Business Central tables without the need to configure any additional web services. This is also Microsofts stated preferred method moving forward.

I am trying to use the API's within Excel power query to reap these benefits of more connections and future proofed reports. (I assume web services will be deprecated at some point).

However, whilst I have successfully connected to one of these APIs (after a bit of URL trial and error), I cannot expose the data in a table format for more than one record at a time.

Here is my connection successfully made to the sales order table. using this URL. 

= Json.Document(Web.Contents("https://api.businesscentral.dynamics.com/v2.0/SANDBOX/api/v2.0/salesOrders?company=Mnfr_Template"))

If I click on 'List' and then select a record, I can see all the data for that record. I can also convert this into a table rather than the default list format that opens.

Try as I might, I have no idea how to show a table of all of the records. If anybody can advise how to show the full contents of the table, I'd be very grateful.

[Syndicate_Admin]

Hi Guy,

How did you solve this issue? 

[Anonymous]

Hi @Guy_McKenzie,

For a structured column, such as List, Record, or Table, there are related values associated with the column. Structured columns have an Expand icon Expand column icon in the column header. You can expand them.

Work with a List, Record, or Table structured column (Power Query)

Best Regards,

Stephen Tao

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

[Syndicate_Admin]

If anybody knows the syntax to return the lines from a sales order/invoice table, that would be very helpful.

[Syndicate_Admin]

Please ignore, I have literally just resolved this.

Thank you