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

Notifications

Announcements

Season of Giving Solutions is Here!
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)
  • Verified answer
    JAngle Profile Picture
    JAngle 137 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.

  • 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

  • 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.

  • Suggested answer
    JAngle Profile Picture
    JAngle 137 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.

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

News and Announcements

Season of Giving Solutions is Here!

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the December Top 10 Community Leaders!

These are the community rock stars!

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

#1
OussamaSabbouh Profile Picture

OussamaSabbouh 1,532

#2
Khushbu Rajvi. Profile Picture

Khushbu Rajvi. 768 Super User 2025 Season 2

#3
YUN ZHU Profile Picture

YUN ZHU 724 Super User 2025 Season 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans