Hugh Johnson explains how to pivot and unpivot Power BI and Excel data and get the best of both worlds.
It should be no surprise to you that Power BI and Excel work really nicely together. For anyone working as a finance director or controller, one of the killer times for using Power BI and Excel together is when it comes to budgets, targets and forecasts.
This comes down to what Power BI and Excel do really well and, in turn, where they are weak. They both complement, and compensate for, each other really nicely.
If you want to work with Power BI and Excel for budgeting, you will soon come across the problem that you will want to store your data in each tool in a different way. This can be overcome by “pivoting” your data after you export it from Power BI to Excel and “unpivoting” it as you bring it back into Power BI. I explain how to do both in this article.
It may sound a bit cumbersome, but much of this process can be automated once it is done for the first time. Once set up, you have a very flexible model that you can update quickly as part of a new, more agile, budgeting process.
Different data structures in your Excel sheet vs Power BI
As I mentioned, you will probably want to store your budget and actual data in Power BI in a different form to Excel. The Excel form would be something like this:
In this example, we have six rows (one for each account we are tracking) and 12 columns (one for each month we are tracking).
However, in Power BI you will probably store the data (and it will be exported this way) in a vertical table like this:
For the same data we now have 72 rows and three columns.
You therefore need to pivot any “Actuals” CSV file downloaded from Power BI to Excel to get the format that you want, and when you re-import to Power BI, you will need to unpivot it to get it into the form you need in Power BI. These steps are explained below in this article, but firstly let’s discuss briefly why you would want to go to this trouble.
Why use Power BI and Excel for budgeting?
When I talk about budgets, I could equally mean targets or forecasts. They are all simply numbers against which we want to track our actual performance and measure variance. It is a continuous, repeating cycle:
Now let’s have a look at how well Excel and Power BI assist with these different stages:
|Review past performance||Not so good||Good|
|Prepare upcoming budget||Good||Good (if the budget setting can be automated)|
|Track variance||Not so good||Good|
|Publish reports||Not so good||Good|
I am making the assumption that you already have an operating model in Power BI that is automatically updated from your accounting software – otherwise you are more likely to be better off just sticking with Excel.
What we notice is that Power BI is really good at automating and presenting analysis. Instructed correctly, it might even update your sales forecasts every night, depending on the nature of your business.
Where it sucks though, is with data entry. So Power BI might spit out a forecast, or actuals from the previous period, but if you want to adjust this then Excel is the way to go. Simply click on the value and month you want to modify and enter a new value, or formula. So simple, so fast and so flexible. No wonder you love Excel!
So, if you want to accelerate your budgeting process, a combination of Power BI and Excel can work really well. You just need to follow a few steps to make it easy.
Step 1 - Set up nominal code Groups to track in Power BI
In my budgeting process I actually only want to track certain nominal codes. In this example I have six sales nominal codes that I would like to track:
- 4100 - Product Type 2 AE
- 4110 - Product Type 3 AE
- 4120 - Product Type 4 AE
- 4130 - Product Type 5 AE
- 4140 - Product Type 6 AE
- 4150 - Product Type 7 AE
To make life easy, I can define a Group in Power BI and assign these six nominal codes to it.
Here is the Group that I have set up, called “SalesBudgetGroup”:
Step 2 - Create “Actuals” visualisation in Power BI
If you want to export your “Actuals” for a given period from Power BI to Excel, then the first thing that you will need to do is to create the visualisation that represents that data you wish to export.
Having the SalesBudgetGroup already set up makes this very easy. I use the Matrix visualization option set as follows:
For your information the “Sales” value is a Measure that sums up all sales invoices in the given context. The Column “Month” and Filter “LastYear” are analysis periods that I have set in my Dates Table that is automatically aligned with my financial year.
I get a visualisation like this:
Step 3 - Export the actuals from Power BI to Excel
By clicking on the ellipse at the top right-hand corner of the visualisation, you can then simply export the data. You can do this from within Power BI Desktop or in Power BI Pro on the web. An advantage of the latter is that if your data volume is very high, you can opt to export the summarized data which, after all, is all you need for your budget setting process.
What you will get is a CSV file with the filtered data shown in the visualisation – but in this form:
This is not in the form that you would like. You need to create a Pivot Table.
Step 4 - Create a Pivot Table of your downloaded data
Note that just as in my Power BI matrix visualisation, I choose “Month” as the Column, “Sales” as the Value and “AccountName” as the Row.
Now I cannot easily edit this chart to create my new budget, so I will save it as a CSV file that I can re-open and work away to set my new budget.
Step 5 - Open your CSV and prepare it for budget entry
You may notice that when I created the Pivot chart in Excel, it reordered my months to start with January. I don’t want this. I want to start at April, since this is the start of my financial year. Also, these months were for my last financial year ending March 2018. What I want is a new set of dates ending in Mar 2019. So what I did was:
- Drag Jan, Feb and Mar to the end.
- Replace the Month text values with Apr 2018, May 2018 etc and dragged across.
Step 6 - Amend the budget values as desired
This is where Excel really comes into its own, and why we went to the trouble of exporting to Excel in the first place. I can share the sheet with other people to fill in the values or use some formula to update them.
Here is an example where I have done this:
Step 7 - Save the Excel sheet on OneDrive and import to Power BI
The next step is to save the Excel sheet on OneDrive and import into Power BI.
Why save on OneDrive?
You don’t have to do this, but it really helps with automation later. If down the line you want to adjust these budgets, all you need to do is edit them and if you are using Power BI Pro then the numbers will automatically update in Power BI.
Import your updated budgets to Power BI
In your Power BI Desktop model, select “Get Data” and the Excel budget sheet that you have prepared.
You will see a dialogue like this:
Select “Load” and the data will be brought into Power BI
Step 8 - Incorporate your revised budget data into your Power BI model
This final step is best shown by video, so I have prepared one here for you. Essentially there are two main steps you need to do:
- Use Query Editor in Power BI to “Unpivot” the Excel Budget data and bring it into the correct form. You only need to do this once, since the steps will be remembered by your model.
- Create the relationships between this new “Budgets” dataset and the rest of your model.
Summary and what next?
In this article, I have explained the technical process of getting historical data out of Power BI to support a budgeting process and then bringing the budgets into Power BI so that you can then report variance against budget.
It might seem a bit long-winded, but in fact it is a very simple process. The good news is that now you have a Budget sheet on OneDrive that you can simply update at any time through the year and these revised budgets will automatically be pulled into your Power BI model.
This helps you to achieve the new kind of agility that many businesses need today for the budgeting process.
Unlike the very rigid style of budgeting that comes with many accounting software packages, this is a lot more flexible. You are not constrained to budgets against Nominal Codes. You can use the same technique to track sales targets against customers or customer groups, or to track pairs of Sales and Purchase nominal codes.
What I have not discussed is what to do with this budget data once it is in your Power BI model. A subject for another day.
- How to import an budget spreadsheet into Power BI for variance analysis - YouTube
- Power BI: Agile reporting of actual vs forecast, target or budget - AccountingWeb
- ”Why Budgeting Kills your Company” - Harvard Business School
- ”Budgets Don’t Work: Here’s how businesses can do it differently” - Forbes
- DAX Function Reference
About Hugh Johnson
I am a blogger on Power BI and your accounting data and the creator of Accounting Insights for Sage 50 which is the only Microsoft Power BI Content Pack available for Sage 50. Please feel free to try it out in connected to your own Sage 50 system on the web or with Power BI Desktop.
I am a senior vice president of Suntico, an online financial data platform that works with popular small business accounting software. I am responsible for the company’s customer division. This covers products, marketing, sales, customer on-boarding, customer service and renewals. I am also in charge of our product strategy and road map.
I hold a Master of Engineering from City University London (awarded with commendation and the highest marks in my year) and a Post Graduate Diploma in International Selling from Dublin Institute of Technology (awarded with distinction and the highest marks in my year).
My personal passions are high performance Fireball dinghy racing and food.