How to Combine System and Manual Data in a Power BI Data Model

I have been working fairly extensively with Finance teams, leveraging Power BI to build financial Models, dashboards and reports. (See some of my recent posts Financial Dashboards, Personalized Dashboards and Q&A). Regardless of the team we’re working with, a very common scenario we run into, is the need to combine System data with Manual data. To clarify:-

  • System Data: Represents the data coming from official system sources (could be a Data Warehouse, SAP etc.)
  • Non-System/Manual Data: This may be manual data maintained by humans or this may be “System” data that is not tracked in the official system. Could be your own SQL/Access Database on the side or a system used only by your department and not the whole company.

20161129 Mashup How to Combine System and Manual Data in a Power BI Data Model
Mashups can be fun, if done the right way

While there are many scenarios where you would need to combine System and Non-System data, for this post I want to cover one such specific scenario. Watch below or continue reading… (link to download files at the end of the post).

Let’s say you have pulled the Data table and the Lookup tables directly from a System (official) source. However, due to some reason you are not 100% happy with the way a given Lookup table categorizes your data. For example, let’s say we are pulling expenses from the system, but need to make a few tweaks to how they are categorized in the system.

Here the changes needed are:

  • “Office Supplies” needs to be categorized under “Office Expenses”
  • “Professional Services” and “Other Services” need to be grouped together as “Miscellaneous”

20161129 End Result 01 How to Combine System and Manual Data in a Power BI Data Model
Need to override some of the system mapping

There are many scenarios where we see such a requirement

System mapping is out of date: A change has occurred in the operating system which is not yet reflected in the Reporting Systems. But of course, it needs to be reflected in the end report.
I have seen this one play out so many times. Often teams rush headlong on a project to deliver some cool new change. The impact on reporting, or the need for reporting around this change is wholly ignored. Only when the change is live, does someone raise this issue.

Of course the DGP (Data Gene Person) on the team had been screaming his/her head off about this for so long, but was ignored by everyone. And now, they turn to him/her and say. “Jim/Jane, just make it work!”. Sigh!

System is chronically behind the Business Needs: This is a slight variation of the one above. While the above can potentially be solved by adequate management; this one is perhaps unavoidable. In my experience, business always moves ahead of IT/System. Business has a bias for action, for change; business is constantly evolving. IT teams, even the best I’ve seen, have a bias for stability (read inaction). You need to prove something is needed, before the change can be implemented.

That means, there would always be a gap between where the business and business needs are versus what the IT Systems deliver. This is not a ding on IT, just a reflection on the motivations behind the two sides.
(Have a different take on this? Let us know by leaving a comment.)

Big Boss likes to see data a specific way: The higher up the totem pole we go, I realize the room for discussion gets smaller. Often when I am creating a report for a CTO/CFO, I may not even have direct access to them. In many cases, I am told that the report needs to be delivered in a specific format, with specific groupings.

The classic approach (think the Old Excel world) would involve taking over the whole mapping table. In our case, we would make a copy of the Accounts table in Excel and then edit the specific values that need to be changed. This gives you complete control over the mapping table.

20161129 Classic Approach 01 How to Combine System and Manual Data in a Power BI Data Model
Copy the complete System Table and modify the values as needed

And this gives us the end result we desire. But if you have ever used this approach, you already know the largest pitfall. It is hard to synchronize system changes. As items get added, deleted, modified in the System table, it is really hard to sync all those changes to your Manual table

As items get added, deleted, modified in the System table, it is really hard to sync all those changes to your manual table

What we really want is a smarter approach.

We really do not want complete control over the system mapping table. All we want is for the specific changes needed to be reflected, almost layered upon whatever the system mapping table is providing. Instead of copying over the complete system table, we only track the exceptions in our manual table. Then we would smartly combine these manual exceptions with the system data using Power Query. Only tracking exceptions in the manual table makes for a much more compact manual table and also ensures that all the system changes (additions, modifications, deletions) would flow through to our model unmolested – without us having to manage them manually.

Only track exceptions in the manual table

20161129 Modern Approach 03 How to Combine System and Manual Data in a Power BI Data Model
Only track the exceptions in our manual table

Here is a graphical view (Query Dependencies view) from Power Query.

20161129 Modern Approach 01 How to Combine System and Manual Data in a Power BI Data Model
Overlay the Manual data on top of System Data

Here is the Power Query code.

20161129 Modern Approach 02A How to Combine System and Manual Data in a Power BI Data Model
Power Query to splice in System and Manual data (click to enlarge)

I have found this Left-Anti Merge approach to be more deterministic. As opposed to Appending Manual data and then removing duplicates or some other approach. Surely there would be other ways to do this, but this works for me.

And here is the desired end result in the report:

20161129 End Result Complete How to Combine System and Manual Data in a Power BI Data Model
Our manual exception mapping have been overlaid on the system mapping

As I mentioned in the opening, I have encountered the need of combining system and manual data in nearly every Power BI* project I have been associated with. And M (Power Query) & DAX (Power Pivot) engines have enabled me build robust and elegant solutions to effortlessly combine system and manual data.
(* I use Power BI in the generic sense, encompassing both Power BI and Excel/Power Pivot/Power Query world.)

Tell us what your experiences have been combining

  • system and manual data
  • system and external data
  • or generally data from multiple sources

Do you have any of your favorite tricks that have helped you?

Download Files: ZIP

Power On!
– Avi Singh
LinkedIn Twitter

Let’s block ads! (Why?)

PowerPivotPro