web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Small and medium business | Business Central, N... / Data Tables in Powerbi
Small and medium business | Business Central, N...
Answered

Data Tables in Powerbi

(0) ShareShare
ReportReport
Posted on by HzJ 60

Dear Champs,

I have 5 companies in my environment and wanted to know the consolidated sales (ITEM WISE as per Sales Invoice) in Powerbi. 

The Item Master, Customer and Vendor Master are SYNCED and SAME in all 5 companies in my environment.

I can filter the sales using the various brands I have (Brand is a dimension in my item card).

Question 1: Do I have to connect the sales invoice Line Data Table from all 5 companies separately in Powerbi? Or Is there a consolidated Tables for all companies in Business Central that I can use for all 5 companies in the same environment.

Question 2: In the Sales Line Table (ObjectID: 526 - SalesInvoiceLines) - How do I subtract the Value of Sales Returns for those particular Items? Are the 'sales returns' stored in the same table or a separate one?

Pls assist. Thanks.

Huzefa Rasheed

Dubai

I have the same question (0)
  • Suggested answer
    JAngle Profile Picture
    JAngle 150 on at

    Q1: Take a look at this blog: thinkaboutit.be/.../

    I found it helpful when building a similar report requirement.

    Q2: returns result in two extra pieces of data in different tables. The sales line represents “open” returns, so not fully processed. Fully processed one’s for logistics sit in the return receipt line table. Fully processed from a financial standing exist in sales credit memo line. For working with Power BI you cannot expose tables directly. You need to use pages or queries. You can expose more data to Power BI with the web services page, accessible with the search feature in BC. Add to the list here and when you do “Get Data” in PBI you will see all the published web services you can access.

  • Suggested answer
    KC Lai Profile Picture
    KC Lai 335 on at

    Try to use Value Entries table, in which both sales invoice, credit notes could be found. 

    Use Page when publish VE. 

    You may consider designing Query in AL codes to expose Value Entry for performance. But this is optional.

  • HzJ Profile Picture
    HzJ 60 on at

    Thanks josh - Both your answers helped a lot.

    Specially the consolidation of data from different similar tables was really helpful and makes life easier.

    QUESTION: In the Sales Invoice Line Table from which I am getting my sales summations - I cant seem to find the below:

    a) The Column that gives me the 'conversion rate' that was used for that foreign currency sales lines (or invoice). Is it in this table or do I need to see another table for this data? 

    Thanks

    Huzefa

  • Verified answer
    JAngle Profile Picture
    JAngle 150 on at

    It is data in the table but is typically not exposed in the page. You may need to have some small development done here to have the data in the web service.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Introducing the 2026 Season 1 community Super Users

Congratulations to our 2026 Super Stars!

Congratulations to our 2025 Community Spotlights

Thanks to all of our 2025 Community Spotlight stars!

Congratulations to the January Top 10 Community Leaders

These are the community rock stars!

Leaderboard > Small and medium business | Business Central, NAV, RMS

#1
OussamaSabbouh Profile Picture

OussamaSabbouh 2,091 Super User 2026 Season 1

#2
YUN ZHU Profile Picture

YUN ZHU 1,032 Super User 2026 Season 1

#3
Dhiren Nagar Profile Picture

Dhiren Nagar 946 Super User 2026 Season 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans