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

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

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

Copilot Dynamics 365 Finance & Supply

-
Image
Microsoft has recently released two Copilot applications to work with Dynamics 365 Finance & Supply Chain. These solutions are still in preview, so it should only be uses in sandbox environments to evaluate and learn how they will work with your organization. Prior to installing it is important to make sure your environment meets the requirements. Dynamics 365 Finance & Supply Chain requires the latest hotfix on Dynamics 365 Finance version 10.0.34 (10.0.1591.107) and version 10.0.35 (10.0.1627.70) and later. In addition, the solutions rely on the Power Platform and Dataverse, so it is important to have the environments connected and user security set appropriately. For more details on the prerequisites and the installation instructions you can follow the Microsoft installation documents. Copilot in Microsoft Dynamics 365 Finance Copilot in Microsoft Dynamics 365 Supply Chain Let's look at what these two copilots provide to users Copilot in Microsoft Dynamics 365 Finance C...
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