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.
