Small and medium business | Business Central, N...

Retrieve all Posted Sales Invoice Lines using API for Power BI

(5) Share

Report

Posted on by hkusulja

Hello,

I need to do some analysis based on Posted Sales Invoice Lines in Power BI. However, I do not see way, which API endpoint should I use, trying to use latest version.

I have found out the URL - https://api.businesscentral.dynamics.com/v2.0/(mytenantid)/myenviornment/ODataV4/Company('mycompany')/salesDocumentLines

However, this API endpoint requires the document number is passed as parameter for particular one Sales Document.

I need to retrieve all lines from all posted sales Invoices.

Which API endpoint should I use?

Using Dynamics 365 Business Central v19.3 SaaS.

Thank you

Categories:

Dynamics 365 Business Central

I have the same question (0)

All responses (26)

Answers (0)

Suggested answer

Inge M. Bruvik 1,065 Moderator on at

Like (3)

Report

RE: Retrieve all Posted Sales Invoice Lines using API for Power BI

I would have used the item ledger entry endpoint and filtered out only the sales transactions.

docs.microsoft.com/.../dynamics_itemledgerentry

Was this reply helpful? Yes No
hkusulja 50 on at

Like (3)

Report

RE: Retrieve all Posted Sales Invoice Lines using API for Power BI

In my cases, Items are not inventory type, instead they are service type.

I also need relation to particular customer through posted sales invoice.

Was this reply helpful? Yes No
Suggested answer

Inge M. Bruvik 1,065 Moderator on at

Like (3)

Report

RE: Retrieve all Posted Sales Invoice Lines using API for Power BI

Then i would have created a custom API that publish the invoice lines i am looking for.

It is pretty easy to do and let you define exactly the data you need.

docs.microsoft.com/.../devenv-develop-custom-api

Was this reply helpful? Yes No
Suggested answer

MahGah 15,529 on at

Like (3)

Report

RE: Retrieve all Posted Sales Invoice Lines using API for Power BI

Hi

Can you check your web service and see if you have Page 133 published. Usually this is not a published page.

Was this reply helpful? Yes No
Suggested answer

MahGah 15,529 on at

Like (3)

Report

RE: Retrieve all Posted Sales Invoice Lines using API for Power BI

Have you published page 133 in your Web Service?

Was this reply helpful? Yes No
Suggested answer

YUN ZHU 93,880 Super User 2025 Season 2 on at

Like (4)

Report

RE: Retrieve all Posted Sales Invoice Lines using API for Power BI

Hi, I checked all Standard APIs in Business Central SaaS. The only other ones available are salesInvoices and salesInvoiceLines.

But salesInvoices only reture the header data.

And as with salesDocumentLines you mentioned, salesInvoiceLines only return one sales invoice line.

So I recommend you to create a new API, or don't use the API, use the Web Service suggested by MahGah.

For example:

Hope this will help.

Thanks.

ZHU

Was this reply helpful? Yes No
hkusulja 50 on at

Like (3)

Report

RE: Retrieve all Posted Sales Invoice Lines using API for Power BI

Hello,

I already have Sales Invoice Header data in other query which is working for me.

I can go to Web Services and add a new line there with object ID 133 as you recommended, but how to filter those in response? I need just from posted sales invoices (no other lines, from purchase, or quotes etc.)

Also, I have already published Object ID 6403 - Sales Document Line Entity. /salesDocumentLines

However, there is issue for expanding some data, since some Items did change their Code.

Also, this is then ODataV4 endpoint, is this going to be deprecated and moved to WebAPI ? (which i need custom programming since native one requires parameter for single invoice)

Was this reply helpful? Yes No
Suggested answer

MahGah 15,529 on at

Like (3)

Report

RE: Retrieve all Posted Sales Invoice Lines using API for Power BI

Hi

What is the business need here? I mean do you want to build a Power BI or your Power Bi is an interactive Power BI. Meaning user enter or select the number then you want Power BI to retrieve data from BC?

We are using header and line table in Power BI and connect them to each other using document No (which is Invoice No in header).

If you want interactive Power BI and BC then maybe look into "Embed PowerApps in Your Power BI Report" idea something like www.youtube.com/watch

Was this reply helpful? Yes No
Suggested answer

Steven Renders 5,672 Moderator on at

Like (3)

Report

RE: Retrieve all Posted Sales Invoice Lines using API for Power BI

It's actually very simple.

Start with the salesInvoices and then expand the salesInvoiceLines column:

let
Source = _apis,
#"Filtered Rows" = Table.SelectRows(Source, each ([apiName] = "salesInvoices")),
#"Expanded Data.1" = Table.ExpandTableColumn(#"Filtered Rows", "Data.1", {"id", "number", "externalDocumentNumber", "invoiceDate", "postingDate", "dueDate", "customerPurchaseOrderReference", "customerId", "customerNumber", "customerName", "billToName", "billToCustomerId", "billToCustomerNumber", "shipToName", "shipToContact", "sellToAddressLine1", "sellToAddressLine2", "sellToCity", "sellToCountry", "sellToState", "sellToPostCode", "billToAddressLine1", "billToAddressLine2", "billToCity", "billToCountry", "billToState", "billToPostCode", "shipToAddressLine1", "shipToAddressLine2", "shipToCity", "shipToCountry", "shipToState", "shipToPostCode", "currencyId", "shortcutDimension1Code", "shortcutDimension2Code", "currencyCode", "orderId", "orderNumber", "paymentTermsId", "shipmentMethodId", "salesperson", "pricesIncludeTax", "remainingAmount", "discountAmount", "discountAppliedBeforeTax", "totalAmountExcludingTax", "totalTaxAmount", "totalAmountIncludingTax", "status", "lastModifiedDateTime", "phoneNumber", "email", "customer", "countryRegion", "currency", "dimensionValue", "paymentTerm", "shipmentMethod", "dimensionSetLines", "salesInvoiceLines", "pdfDocument", "attachments", "ETag"}, {"id", "number", "externalDocumentNumber", "invoiceDate", "postingDate", "dueDate", "customerPurchaseOrderReference", "customerId", "customerNumber", "customerName", "billToName", "billToCustomerId", "billToCustomerNumber", "shipToName", "shipToContact", "sellToAddressLine1", "sellToAddressLine2", "sellToCity", "sellToCountry", "sellToState", "sellToPostCode", "billToAddressLine1", "billToAddressLine2", "billToCity", "billToCountry", "billToState", "billToPostCode", "shipToAddressLine1", "shipToAddressLine2", "shipToCity", "shipToCountry", "shipToState", "shipToPostCode", "currencyId", "shortcutDimension1Code", "shortcutDimension2Code", "currencyCode", "orderId", "orderNumber", "paymentTermsId", "shipmentMethodId", "salesperson", "pricesIncludeTax", "remainingAmount", "discountAmount", "discountAppliedBeforeTax", "totalAmountExcludingTax", "totalTaxAmount", "totalAmountIncludingTax", "status", "lastModifiedDateTime", "phoneNumber", "email", "customer", "countryRegion", "currency", "dimensionValue", "paymentTerm", "shipmentMethod", "dimensionSetLines", "salesInvoiceLines", "pdfDocument", "attachments", "ETag"}),
#"Expanded salesInvoiceLines" = Table.ExpandTableColumn(#"Expanded Data.1", "salesInvoiceLines", {"id", "documentId", "sequence", "itemId", "accountId", "lineType", "lineObjectNumber", "description", "unitOfMeasureId", "unitOfMeasureCode", "unitPrice", "quantity", "discountAmount", "discountPercent", "discountAppliedBeforeTax", "amountExcludingTax", "taxCode", "taxPercent", "totalTaxAmount", "amountIncludingTax", "invoiceDiscountAllocation", "netAmount", "netTaxAmount", "netAmountIncludingTax", "shipmentDate", "itemVariantId", "locationId", "salesInvoice", "item", "account", "unitOfMeasure", "itemVariant", "dimensionSetLines", "location"}, {"id.1", "documentId", "sequence", "itemId", "accountId", "lineType", "lineObjectNumber", "description", "unitOfMeasureId", "unitOfMeasureCode", "unitPrice", "quantity", "discountAmount.1", "discountPercent", "discountAppliedBeforeTax.1", "amountExcludingTax", "taxCode", "taxPercent", "totalTaxAmount.1", "amountIncludingTax", "invoiceDiscountAllocation", "netAmount", "netTaxAmount", "netAmountIncludingTax", "shipmentDate", "itemVariantId", "locationId", "salesInvoice", "item", "account", "unitOfMeasure", "itemVariant", "dimensionSetLines.1", "location"})
in
#"Expanded salesInvoiceLines"

An example is in attachment.

[View:/cfs-file/__key/communityserver-discussions-components-files/758/SalesInvoicesAndLines.pbix:320:240]

You might want to remove all unneeded columns.

Here's also a post with some explanations:

Did you know that the Business Central connector in Power BI supports related tables when using APIs? – think about IT

Was this reply helpful? Yes No
Suggested answer

YUN ZHU 93,880 Super User 2025 Season 2 on at

Like (3)

Report

RE: Retrieve all Posted Sales Invoice Lines using API for Power BI

Hi, The table in the background of page 133 is table Sales Invoice Line (113), not the Sales line, so it does not contain data other than the posted sales lines.

And you can add fitlers like in APIs, more details: https://yzhums.com/6117/#toc8

There is currently no message that the ODataV4 endpoint will be deprecated, only Web Service Access Keys (Basic Auth) for Business Central Online will be Removed with Business Central 2022 release wave 1.

More details:

https://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/upgrade/deprecated-features-platform

Hope this will help.

Thanks.

ZHU

Was this reply helpful? Yes No