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 running in
Post a Comment

Covid 19 Dashboard - Article 1 - Starting the process - DATA (The growing CSV file)

-

As I mentioned in my earlier post on my COVID 19 Dashboard, I am going to write a few different blog articles describing not only how I built it, but some things I learned in the process.

The current list of planned topics include:
  • ·         Working with growing CSV files
  • ·         Using index and merge when transforming data
  • ·         Using time functions to calculate the daily change
  • ·         Reporting based on the population
  • ·         Working with maps, setting data types correctly
  • ·         Possibly more to come depending on how long this all goes….

Let’s get started at the beginning.  To build any Power BI report, you must have data, so my adventure began on the web.  I searched various sites for data, and there is plenty of it out there.  However, I wanted to find something that would continue to be updated and had not only international data, but also USA State data.  While looking at www.covidtracker.com, I found all their data was available in GitHub.  I went here to see what was available.  I found they had time series data available for both global and US cases and deaths.  This looked very promising as all the data was in CSV files and it was updating automatically.  Finally, I could use the RAW version of the data with the corresponding URL with a Web data connection in Power BI.

I am sure many of you have brought in CSV data before, so I won’t go through all the steps.  After the data was available in Power Query, I was able to transform the data by using the first row as headers, but I quickly noticed instead of having a separate row for each day, the file had a separate column for each day.  For example, the data had over 80 columns when I made my connection.  This did not concern me, because I am familiar with “Unpivot Columns”.  This will allow me to move use date column to a row.  If you are not familiar with this feature, Microsoft has some great support here.


I went ahead and used “Unpivot Columns”, did some other basic transformations, and applied my changes, so I could start on the report.  I thought everything was good, until the next day, when I tried to refresh the data.  All my connections where good, but no matter what I did the next day did not appear on any of my reports.  Remember, I mentioned the data has a separate column for each day, so every night the source data gets a new column for the latest day of statistics.  I decided to look at my query in Power Query Advanced Editor.


That is when I noticed the number of Columns were hard coded in my query.  As in the screenshot, Columns=84.  After doing some research, I discovered that this parameter was not necessary.  If it is removed from the query, Power Query will build the columns based on the number of columns in the first row of the file.  Since my file has the header in the first row, I was comfortable in knowing all my columns would be identified.  I simply removed “Columns=84,” and my data now refreshes correctly.

I do not work with a lot of CSV files, and they do not typically grow on a regular basis.  However, if you ever must deal with a growing CSV file, make sure to check for the number of columns being hard coded in your query.

In my next article, I will discuss some of the other transformations I did with this data.

Comments

Post a Comment

Popular posts from this blog

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 calculations (YTD, MTD, etc.).   So there must be
Read more

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 running in
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