Forum Discussion
Advocate IConnecting to Dynamics 365 Business Central API v2.0
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.
Advocate INope. 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).
Create a custom function to load Business Central sales order lines into Power BI. See this article on how to do it: