RPA with Dynamics 365 Finance & Supply Chain

-
Image
Are you looking for ways to automate some of your routine accounting functions, like reconciling sub ledgers to the general ledger?   Microsoft has recently created some examples using Power Automate Desktop (think of it like Task Recording on steroids). It can not only automate steps through Dynamics 365, but it can also automate Excel and other applications. In one example from Microsoft they automate the AR to GL reconciliation: Automatic running of the AR aging and the customer/ledger reconciliation reports. Power Automate then extracts data from both reports in Excel and compares the balances. Power Automate then posts a Team message letting you know the subledger is in balance. Power Automate saves the reports with the date in the file name, so there is backup. Microsoft Directions & Example   I working with the example, I needed to make a couple of adjustments to the sample flow that was provided but I was able to get it to work. Here is a video of the flow r...
Post a Comment

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

Comments

Post a Comment

Popular posts from this blog

RPA with Dynamics 365 Finance & Supply Chain

-
Image
Are you looking for ways to automate some of your routine accounting functions, like reconciling sub ledgers to the general ledger?   Microsoft has recently created some examples using Power Automate Desktop (think of it like Task Recording on steroids). It can not only automate steps through Dynamics 365, but it can also automate Excel and other applications. In one example from Microsoft they automate the AR to GL reconciliation: Automatic running of the AR aging and the customer/ledger reconciliation reports. Power Automate then extracts data from both reports in Excel and compares the balances. Power Automate then posts a Team message letting you know the subledger is in balance. Power Automate saves the reports with the date in the file name, so there is backup. Microsoft Directions & Example   I working with the example, I needed to make a couple of adjustments to the sample flow that was provided but I was able to get it to work. Here is a video of the flow r...
Read more

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

-
Image
There are many different articles available on Time Intelligence in Power BI.   Many new users to Power BI do not really understand the importance of having a date table in their data model.   I have spoken on this topic on many different occasions and continue to receive a lot of questions about the topic.   In this article I am going to try and cover a few tips on how to work with dates in your Power BI model. Many new users simply use the Auto Date & Time function of Power BI.   This function automatically creates a hidden date table for each field in the model with a date or date/time data type.   This function is enabled by default and it allows for slicing by Year, Quarter, Month and Day.   The problem with Auto Date and Time is that it only works with a standard calendar (Jan to Dec).   It requires a datetime column in the fact table.   It does not work with multiple fact tables and it does not support time intelligence calculatio...
Read more

Using Full Page Apps in Dynamics 365 Finance

-
Image
Have you ever wanted to put a website or a model-driven Power App in one of your existing Dynamics 365 Finance workspaces or forms?  You might already know you can create a new workspace with a website, but did you know you can add the website to an existing workspace? Here is an example of the Vendor invoice entry workspace with the Invoice Capture Power App embedded. Prior to using this feature, you need to turn on "Saved views support for workspaces" in feature management. Once this feature is enabled you can follow the following steps to add the Invoice Capture app or any model-driven Power App or website to an existing workspace. Open the vendor invoice entry workspace From Options - choose Personalize this page From personalize menu - select the 3 dots - and then Add an app Once that is selected (the screen goes gray) - click on the section header where you want to add the app (in my example I put it in Links) On the Add an app menu - choose Website Give it a name (my e...
Read more