D365F&O Accounts Payable Aging report with GL accounts using Power Automate

Hello,

This will be a short blog post where I will highlight the combination of different methods to solve a business case I came across. Customer wanted to see an accounts payable aging report that will show the offset account (for those not familiar with offset account, it means the expense GL account of those unpaid invoices)

My first thought is I don’t think such a report exists OOB in D365F&O (Please comment below if you seen one). After digging around a little bit, I found the following report in

Accounts Payable>Inquiries and reports>Vendor Reports>Account Statement

I was familiar with this report, what surprised me was the ability to get the offset account by ticking the offset account button to “Yes”

I was very happy with the result until I saw the output format of the report. It was your messy standard SSRS report format that comes OOB with the system. Even if you open the report in Excel the format was no where close to being usable to do some research. If you are familiar with D365F&O, you have seen those nasty reports with tons of merged columns and empty space all over the place.

The good news, the report had the data we needed.

 

The solution to this issue has different steps:

1. The first one is to export the report from D365F&O using an excel format, and then have D365F&O email the report directly to your inbox.  The important piece to remember is to use an identifiable email subject that we can use later in Power Automate to find the email.

2. Using Power Automate then build a flow to extract the attachment from your inbox and then save it on One drive or a SharePoint folder. The trigger of the flow is to run when an email arrives with the subject used on the email from D365F&O

Then get the attachments from the email

Then save the file. In my case I am using one drive for business. Make sure to give the report a good file name and type .XLSX after the name so it creates an excel file

That’s it for the flow. It is basically just taking the file from your email and then saving it in a specified folder.

3. This is the real trick. In another Excel file, build an automated way to clean up the data, so that every time the report is ran and saved, the data will be cleaned. That’s where Power Query comes into play, it was designed to tackle these repetitive data clean ups. I won’t go into too many details about the Power Query steps, they are fairly simple.

The first one is to read the dirty data from the file saved in your one drive. To get the file path from your excel file, simply look under info and then click under the file name and copy the file path to the clipboard.

Then click on the data tab, and then get data from other sources>from Web (Remember that one drive is basically an online file storage)

Then paste the file you copied from the account statement file. Remember to erase anything after the ? character. in my case ?web=1

Then select the file content, and Transform data at the bottom

This will load the raw data into the query editor, and from then perform the steps to clean the report. There are plenty of blogs and videos on how to use the user interface to clean up data using power query. If you want me to write about using Power query in this blog please share comments below.

After all steps are done, you should be left with a nice clean, tabular format report.

To finish it off, load the data to your excel file.

The trick here is to change the query settings to refresh the data each time the file is opened. To achieve this, simply right click on the query and then on properties.

and then check on “Refresh data when opening file”

There you have it folks, an OBB way to get clean, usable data from D365 F&O in a more automated way. The goal here is to get the latest clean data every time you run the report from D365F&O automatically.

Old report

 

New Report

The blog was written to get you to think about the possibilities using, Power Automate, and Power Query to get clean reports from SSRS reports in D365F&O.

Until next time

Regards,

Juan Sebastian Grijalba, CPA