PowerBI on Dynamics 365 - GL/Main Tables

[Anonymous]

Hello,

I have a requirement wherein I need to connect to Dynamics 365 source from PowerBI Pro - and report GL Main Accounts balances (Budget vs Actual and Debit vs Credit), but I am unable to find the correct D365 tables where these columns are available.

I tried downloading the dataset for Dynamics365 Financials Content pack, wherein I see the details of Main Accounts (entity) and measures (see screenshot),  but do not see the measures (see second screenshot) when connected to actual tables in D365 from PowerBI.

Kindly help in getting the right D365 tables wherein these measures are available.

[Oshin2010]

Hi Ajay,

Did you manage to do it?  I am facing similar issue.  I need to generate Trial Balance by dimension.  

How did you do it?

[Anonymous]

Hi Oshin2010, any luck on how to bulid a trial balance with dimensions in Power BI?

[Oshin2010]

Hi Ajay,

I am having a similar issue.  I need to build a Trial balance by dimensions.  

Did you manage to find your tables?

Os

[v-shex-msft]

Hi @Anonymous,

I'd like to suggest you use import function to load this pivot table from excel workbook.

Regards,

Xiaoxin Sheng

[Anonymous]

Hi Xiaoxin Sheng,

Thanks for your response.

The problem that I am facing is, I am unable to find the correct table & column names which I have to use to build PowerBI reports for Credit vs Debit and Budget vs Actual expenditure for GL Main accounts.

Any information on this would be of greatl help.

Thanks,

Ajay

[Birdjo · Accepted solution]

@Anonymous, if you're a system admin you can do the following:

1. Open the form you want to get in Dynamics 365.

2. Right click on the form.

3. Hover over form information

4. Click on Form name: XXXXXX

5. Click Administration

6. Scroll down to find the query statement.

This will give you the query used to generate that form. You can try to copy it to Power BI.

[Birdjo]

Hello,

You can look at the General Ledger entities here. 

[Anonymous]

Hi Birdjo,

Thanks for your response 🙂

I checked the link that you shared, but could not get details on the column/attributes required - where can I possibly get this information ?

Thanks,

Ajay