AP Automation in Dynamics 365 Finance and Supply Chain

As a solution architect, prospects and clients often ask me for ways to improve their Accounts Payable process.  They receive hundreds of invoices every month, and it can take considerable amount of time to enter all of the detail as well as attaching the invoice for reference during the approval process.  This is an area where we have often needed to bring in an ISV solution, but that could cause other issues.  Microsoft has been doing considerable work around automation for AP and they recently released a public preview of their Invoice Capture solution. I have had the opportunity to set this up in one of my test environments so I could get an idea of what they have to offer.  As we would expect, the Invoice Capture solution is built on the Power Platform.  It uses a model driven Power App along with Power Automate Flows to integrate with Dynamics 365 Finance.  However, since it is a solution, they have done much of the work for you, including creating the standard flows to load the

I finally took a close look at Power BI

I have been working in the Business Intelligence space for the past several years.  During this time, I have watched the rise of Microsoft Power BI from a distance.  They were a competitor, so while I kept an eye on them, but I did not really spend that much time in the tool.  I knew the software I was selling and supporting must be better.  We have been around longer and we actually sell our software.  You can get started with Power BI for free or upgrade to Power BI Pro for only $9.99 a month.  How good can it be at that price point?

Well with some recent employment changes, I have now had more time to actually dig deeper into Power BI, and while it is far from perfect, I have to admit I am beginning to understand what all of hype is about.  Power BI is a serious player in the Business Intelligence space.

Probably one of the best features of Power BI is the community.  There are a lot of people using it and because of this there are a lot of blogs, posts and videos available on-line.  I was even able to find some free training classes like edX.org’s Analyzing and Visualizing Data with Power BI.  So, I am going to try and take a different approach and not simply review Power BI, because you can already find many reviews on-line.  I am going to highlight a few of the unique, powerful features I found in my review.

Let’s start with the data.  This is where any BI project must begin.  If you don’t have complete, clean data, then your visualizations and reports will be not give an accurate view.  As I am sure you are aware, Power BI can connect to many different data sources, including SQL, Excel, Oracle, Facebook, Google, and on and on, but connecting to the data is only the first step.  Often the data is not complete and clean, so you need to take some steps to get the data ready.  I had heard a lot about DAX, the functions and operators used by Power BI to build formulas and expressions.  I was not excited to learn another syntax, but I was surprised how easy Microsoft has made it. 

In my review, I was connecting to multiple tables from a relational database and to get the data ready I needed to go through a few steps including adding some additional columns and combining some tables.  I found that Power BI has some easy to use functions to accomplish these steps without really having any programming knowledge.

The functions I used included Merge Columns, Column from Examples, Conditional Columns, Merge Queries and Append Queries.  I found it very easy and straightforward to use these functions to add the columns I needed and even join tables together without really understanding any coding.  Of course, it is still important to understand data relationships and make sure you are relating the multiple tables together correctly.  One of my favorite features I found in editing my query was the Applied Steps.   This allows you to step through each change or function you create as you are preparing your data.  Applied Steps also allows you to reorder the steps with a simple drag and drop.  For example, I had created a merged column to combine first and last name.  Later I added a second similar table using append query.  Since the merge column was first, it did not merge the appended data.  I simply dragged the append query before the merge column and my problem was solved.  I did not have to start over and I did not ever use any code.  It was very simple.
Once I had my data ready, I started creating visualizations.  Power BI is very strong in the visualizations.  It offers all the standard options you would expect, including line charts, bar charts, pie charts, scatter charts, matrix reports and many more.  However, if you don’t find what you are looking for you can simply go to Visuals Library for Power BI.  I was looking for a WordCloud analytic and it was available in the Visuals Library.  I simply downloaded it and then imported it into Power BI Desktop.  That is all it took and I was able to create my analytics. 
I also needed to have a calculation in one of my reports.  My sample data had baseball statistics including hits and at bats, but I wanted to see the batting average.  I used the New Measures function to create a measure for Batting Average.   This did require using DAX, but there is a lot of information available on-line to help with the formulas.  Also, they have recently added Quick Measures to help with common calculations including time based measures, aggregates and filtered values.
Power BI certainly brings a lot to the table.  Not only in visualizations, but also in data connections and manipulation.  There are a lot of functions, features and benefits available.  This gives users a lot of opportunity and I look forward to continuing to dive deeper into the product.  However, I want to offer one word of caution.  While there is a lot of talk about Power BI being a “self-service” data analysis tool, it is still a very complex tool.  Make sure you get some training and truly understand how all the data you are working with relates.  Like any powerful tool, wonderful things can be created in the right hands, but in the wrong hands it can do a lot of damage.  There is nothing worse than delivering an incorrect report in a board meeting.  There are many Power BI experts, don’t be afraid to ask for help.


Popular posts from this blog

Creating a date table with a fiscal year in your Power BI data model

Showing the last 12 months based on a date slicer

Adding a new entity to Dual-write in Dynamics 365 Finance