Connecting to Dynamics 365 Business Central API v2.0

[xembledev]

I am working on creating a dashboard using the official connector to BC. I was able to set the connection authentication and while exploring I can see all the endpoints being displayed effectively; so I can see the treeview with the API and Web service endpoints displayed and selectable. If I click on an endpoint like the "Sales Invoice" endpoint, the preview is loaded correctly, and if I import data, it will be brought up into Power BI.

The problem comes when I try to use a different detail endpoint such as "Sales Invoice Lines" or any detailed endpoint in this fashion as "Purchase Order Lines" and the like. Error thrown is telling me that "You must specify an Id or a Document Id to get the lines".

Now, I understand what the error is trying to tell me. If I inspect this API via Postman, and I add parameters to the GET request I am able to query the lines correctly. I am lost on how to get this interface to work from Power BI and load all related lines at once. So far I have only been able to get Sales via web service using OData V4.

Thank you in advance !

[mortenseifert · Accepted solution]

Hi, I ran into the same problem this week. When you have loaded the salesInvoices you need to go to "Transform data".

Here you have two options. 

a) Create the product of the two tables, i.e. fields from the salesInvoice will be repeated for each salesInvoiceLine, or

b) Create a new table with the lines and create the relation your self

a) Create Cartesian Product

All the way to your right, youh have the related tables. Press the little "split" icon and select expand.

b) New table

If you want to have the lines in it's own table, you need to do the following.

1. Right click the column with the table and select "Add as New Query"

2. Select the new table choose the action "To Table" 

3. Now choose the "split" icon (as is option a) - this time uncheck "Use original column name as prefix"

4. Close and Apply and create the relation between the two tables.

[xembledev]

@mortenseifert Thank you for your detailed explanation.

[mortenseifert]

Glad I could help ✔

[V-lianl-msft]

Hi @xembledev ,

Power BI desktop asks for a Document ID.You can try to use the Http methods of the REST API’s to get the data

Please Enter your companies API URL with ID.For example: Your Endpoint + API URL +(ID).

https://ruudvg.com/2019/05/26/microsoft-business-central-api-beta-create-a-sales-order-and-sales-ord... 

https://yzhums.com/20690/ 

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

[xembledev]

Thank you for the reply. As detailed in my original post, I have used the Sales Invoice Lines HTTP GET endpoint via postman to retrieve just individual sales invoice line detail, and that is working as intended, no problem whatsoever.

What I do not understand is how is Power BI going to load all my sales invoice lines if this endpoint is asking me go individually (one by one) ? There is no endpoint (Get Operation) that can retrieve all "sales invoice lines" at once like there is for the plain "sales invoice" or like there is using OData.

[mpmsltd]

Were you able to solve this issue? I'm having the same problem

[xembledev]

Nope. The only way I made it work was using OData endpoints, this is, the old ones you can find on Business Central as "Web Services". In the newer API, you have different endpoints for Sales Quotes, Sales Invoices and Sales Orders; so instead, I had to use the old "Sales Documents" (which includes all these three mentioned so you have to filter or use $filter in odata).

[Bluedialog]

Create a custom function to load Business Central sales order lines into Power BI.  See this article on how to do it: 

https://www.bluedialog.com/post/get-business-central-sales-order-lines-using-a-power-query-custom-fu...