Soon I will be starting a new series of posts around Dynamics GP and how to get data out of it with Power BI or Excel via Power Query. There is no shortage of reporting options available for Dynamics GP customers, with many ISV (3rd party) products out there to augment some out-of-the-box options (Report Writer, SmartLists). However, as with accounting departments everywhere, there is a heavy reliance on Excel as the "go-to" tool for reporting and data analysis.
There are a lot of customers who do a lot of "export this SmartList to Excel and then do X" to get data ready. I've found many users do things in Excel that are repeatable yet are unaware of Power Query and more advanced ETL (Extract, Transform, and Load) techniques to automate some of the work. The other part of the coin here is there are many times when users or management want to get dashboards and higher-level metrics on how they are doing, not just data dumps to wade through. I hope to cover a bit of both here.
Background
Both Excel and Power BI rely on Power Query for the underlying ETL data transformation, and in this series, I will show some tips and tricks around that based on how I've used it to help some of my clients.
Before starting this, I put a poll up on LinkedIn and Twitter to get some feedback. While the engagement on the poll was fairly low, the vast majority of voters were looking for useful queries or tips (47%), starting from scratch (29%), and data prep (12%).
Approach
I may change my mind on this (!!) but I am hoping to go through different modules group by group with the same structure of posts for each.
"Starting from scratch" will be first in the context of Excel before getting into Power BI. This won't be for new Excel users, but for users unfamiliar with Power Query and I suspect there will be several posts working through things that anyone can use with either Excel or Power BI. Later on, I will revisit "starting from scratch" in the context of Power BI.
In between those two sections will be data prep, including useful queries and tips as I work through some of the tables and views to get data out of the modules we're discussing.
At the end, I'll start to put together some reports in Power BI to visualize this data before returning to the beginning to repeat with another set of modules.
Data & Software Versions
I'll be using Dynamics GP 18.4 (aka Fall 2021 release) for this exercise, with Fabrikam data to make it generic. Even though the sample data is often "not good", it's the context of what tables or views I'm using that will be important as it should be translatable to real-world corporate data if using real company databases instead of TWO.
For Excel, I'm using Microsoft 365 and am on the Current Channel, currently version 2112 for what it's worth.
For Power BI, I'm using the Windows Store version, currently the December 2021 build. That source of Power BI is updated monthly so it is even more likely that there could be things I show that are "new" but for the most part, what I plan to show is not around details of new features. The build I'm on will change regularly and I'll do my best to post version info on the blogs for reference.
As I work through things, I may be showing screenshots of Power BI but the work up until the visualization should be repeatable in Excel if the visuals are not the end goal in following along.
Feedback welcome
With a series like this, feedback is very useful to help frame examples or avenues to demonstrate in future posts.
