Account Schedule With Power BI in Dynamics 365 Business Central

Did you know that Microsoft Power BI can connect account schedule data in Microsoft Dynamics 365 Business Central? Follow the simple steps shown below to try this out youself:

• Click search icon
• Type Account Schedule KPI Web Service Setup
• Click Account Schedule KPI Web Service Setup

• Enter information on General header tab
• Web Service Name will appear in the Service Name field on the Web Services page
• More info about fields on General header tab
• Add account schedules on Account Schedules line that you would like to see data from Power BI
• Click Publish Web Service

• Click search icon
• Type Web Services
• Click Web Services

Make sure Web Service Name that you entered on General header tab appears on Web Services page. ‘Powerbifinance’ is Service Name in this example.

• Open Power BI Desktop
• Click Get Data > More

Online Services > Dynamics 365 Business Central > Connect

• Enter your credentials to sign in
• Expand name of environment > Company > Web Services (legacy)
• If you don’t know your name of environment, click the help button (?) top right corner > Help & Support
• You will find the name of your environment under the Troubleshooting section

 

• Find and select the Web Service Name, ‘powerbifinance’ in this example
• Click Transform Data
• Do some data transformations if needed
• Click Close & Apply

 

Add fields (No, KPI_Name, Net_Change_ for income statement related account schedules) with a table visual.

• Add slicers for Date and Account_Schdule_Name
• Set Date and Account_Schedule_Name slicers
• Set the table visual properties below
  • Sort by No
  • Sort ascending
  • Totals Off

If you don’t see all your chart of accounts for the account schedule, check your account schedule.

Show column must be set to ‘Yes’. After you set Show to Yes for the account schedule, click the Refresh button to retrieve data from Business Central.

For balance related account schedules, use Balance_at_Date_Actual field instead.

If you’d like to learn more about using Power BI with Dynamics 365 Business Central, please contact us.