Revealed: Data Consolidations with Microsoft Power BI Tools
- 3.5 hours on-demand video
- 4 articles
- 52 downloadable resources
- Full lifetime access
- Access on mobile and TV
- Certificate of Completion
Get your team access to 4,000+ top Udemy courses anytime, anywhere.
Try Udemy for Business- Use the power of Excel 2016, Power Query and Power Pivot to create structure, reduce complexity, and improve flexibility
- Creating a systems-based approach to data transformations
- Consolidating data between multiple business entities
- Data transformations using Microsoft Excel, Power Query, and Power Pivot
- Learn to 'clean' your trial balance data and put into a format suitable for analysis
- Managing the Data Model by creating relationships between data sets
- Creating Dashboards to present the data using Visual Basic and simple macros
- Course Layout: a.) Transforming the Data b.) Creating Dashboards c.) Making Decisions
- Sample files are provided to follow along and test your understanding real-time
- Apply these techniques to your organization
- Familiarity with Microsoft Excel
- Knowledge of basic Finance and Accounting principals and understand the difference between an Income Statement and a Balance Sheet
- A strong desire to harness the power of Power Query, Power Pivot and Power BI
This course will teach you how to systematically transform and consolidate financial data using Microsoft Excel, Power Query, and Power Pivot. I will show you how to turn your organization's existing Trial Balance into a meaningful query that can easily be replicated each time you regenerate your data. We will construct relationships in the data model, allowing us to turn the initial Trial Balance into a Global Income Statement by connecting data across multiple entities.
My guarantee to you:
After taking this course you'll learn a new way to use Power BI products to manage financial consolidations.
Does your financial close process suffer from any of these common ailments?
Unstructured - No commonality in account structure? No corporate hierarchies for dimensions such as accounts, entities, departments and products?
Complex - Use of multiple spreadsheets for consolidation? Poor controls, no process standardization and limited communication between users?
Inflexible - Lack of multidimensional financial reporting that can quickly adapt to changing business needs?
Use the power of Excel 2016, Power Query, Power Pivot and the Data Model to solve these problems.
Create structure - define a common chart of accounts and reporting dimensions shared by all locations, departments and reports
Reduce complexity - dramatically improve controls by having 'one version of the truth' accessible to multiple users; eliminate need for multiple spreadsheets while communicating close status to all users throughout the process
Introduce flexibility - create updatable multidimensional hierarchy structures that quickly meet accounting and Finance reporting requirements
Become the financial systems superstar at your current company or go somewhere else and be their superstar.
- Finance and Accounting professionals who perform data transformations and consolidations
- All financial analysts who need a better way to handle large amounts of data
- Those who want to improve or eliminate their manual, error-prone spreadsheet data transformations and replace with a simpler, more reliable system
- Those who want to improve their data analytics and Power Query talents
Download these sample files to your computer
I suggest something like C:\Doug\FinCon\(place files here)
*Note:
There are also documents attached to the individual lessons for your reference. These have the final sheet for the lesson after all transformations and tasks have been completed. Feel free to disregard these and continue applying transformations in your same starting workbook throughout the entirety of the course. These extra resource sheets are just provided to help you by looking at the final answer of each lesson.
Key Concept
Connect Trial Balance data to Chart of Accounts metadata using Excel Data Model
I am a nut for keeping things orderly.
It's the only way I can efficiently manage my financial systems.
The purpose of this section is to show how to use native Excel features to create a 'control center' dashboard.
It involves use of colors, macros and psychology.
Let's take a look . . . (Watch the video)
Your action items for each lesson:
Download the Excel file
Watch the video, follow along and read the attached pdf file
Recreate the dashboard within your own Excel workbook from the previous lesson
The only way to learn is by doing.
Questions?
Contact me at doug@dougburkedata.com
The purpose of this lesson is to begin building our dashboard control center
Some tasks:
Add a new worksheet
Colorize it using specific colors
Add an image to make it look more professional
There are two Excel workbooks attached to see the starting point and ending point with the final answer.
FinApp_S02_L01.xlsx - - our workbook before macros
FinApp_S02_L01.xlsm - - our workbook with macro ability enabled (which we'll use in next lesson)
I will also recreate the Excel workbook dashboard (FinApp_S02_L01.xlsx) so you can see its creation from the beginning (no tricks up my sleeve).
You should follow along and create the macros with me in your own workbook.
The purpose of this lesson is to create and add macros our dashboard control center
Some tasks:
Create a macro to select a sheet
Review the macro code in Visual Basic
Add control buttons to run the macros
There is one Excel workbook attached to see the final answer
FinApp_S02_L02.xlsm ~ our workbook with two macros enabled
I recreated the Excel workbook macros by starting with file FinApp_S02_L01.xlsm .
You should follow along and create the macros with me in your own workbook.
The purpose of this lesson is to learn how to refresh your data and metadata connections.
Some tasks:
Select Data ribbon > Connections
Review the connections created for each query
Trace where the connections are used throughout the workbook
There is one Excel workbook attached to see the final answer
FinApp_S02_L03.xlsm - - one workbook with two connections to two queries
You should follow along in your own sheet and review the connections.
The purpose of this lesson is to learn how to create macros that hide / unhide worksheets used with the dashboard.
Some tasks:
Create new macro
Modify macro to handle multiple worksheets
Add buttons & format objects to make a professional looking dashboard
There is one Excel workbook attached to see the final answer
FinApp_S02_L04.xlsm - - one workbook with two connections to four queries
You should follow along and recreate the macros so you understand the process.
The purpose of this lesson is to learn how to do additional cleanup of your Power Pivot reports
Some tasks:
Review data
Look for blank rows, ‘totals’ and anything that is suspicious
Clean at the source (Power Query)
There are two Excel workbooks attached to see my starting point and ending point with the final answer.
FinApp_S03_L01_begin.xlsx - - our workbook before updates
FinApp_S03_L01_end.xlsm - - our workbook at end of lesson (after updates are made)
You should follow along and recreate the transformations in your own workbook so you understand the process.
The purpose of this lesson is to learn how to sort columns in your Power Pivot reports (even when it does not seem like it can be done)
Some tasks:
Identify the column you want to sort (such as 'Accounts')
Return to Power Query and add an index column
Use the 'Sort by Column' feature in the Data Model to update your Power Pivot report
There are two Excel workbooks attached to see my starting point and ending point with the final answer.
FinApp_S03_L02_begin.xlsx - - our workbook before updates
FinApp_S03_L02_end.xlsm - - our workbook at end of lesson (after updates are made)
Note: I use the terms "Power Query Report" and "Power Pivot Report" interchangeably in this lesson
You should follow along and recreate the transformations in your own workbook so you understand the process.
The purpose of this lesson is to hide system member names from users to reduce visual clutter
Some tasks:
Open 'Relationships' section of the Data Model
Right-click member and select 'Hide from Client Tools'
Verify that member name no longer appears in Power Pivot report
There are two Excel workbooks attached to see my starting point and ending point with the final answer.
FinApp_S03_L03_begin.xlsm - - our workbook before updates
FinApp_S03_L03_end.xlsm - - our workbook at the end of lesson (which we'll use in next lesson)
You should follow along and recreate these steps in your own workbook so you understand the process.
The purpose of this lesson is to learn how to create custom reporting data columns in Power Query that can be used in Power Pivot reports
Some Tasks
Identify need for a custom column
Update Power Query source data (custom columns > unpivot columns)
Refresh Data Model and show result in Power Pivot
There are two Excel workbooks.
FinApp_S03_L04_begin.xlsx - - our workbook before updates
FinApp_S03_L04_end.xlsm - - our workbook at end of lesson (after updates are made)
You should follow along and recreate these steps in your own workbook so you understand the process.
Task:
Create an income statement using Microsoft Power Pivot.
There are two Excel workbooks attached to see my starting point and ending point with the final answer.
FinApp_S04_L01_begin.xlsm - - our workbook before updates
FinApp_S04_L01_end.xlsm - - our workbook at the end of lesson (which we'll use in next lesson)
You should follow along and recreate these steps in your own workbook so you understand the process.
Task:
Load data for a second legal entity into a new query.
There are two Excel workbooks attached to see my starting point and ending point with the final answer.
FinApp_S04_L02_begin.xlsm - - our workbook before updates
FinApp_S04_L02_end.xlsm - - our workbook at the end of lesson (which we'll use in next lesson)
You should follow along and recreate these steps in your own workbook so you understand the process.
Some Tasks:
Combine the E01 and E02 Income Statements using the append function.
Add the combined Income Statement to your Data Model.
There are two Excel workbooks attached to see my starting point and ending point with the final answer.
FinApp_S04_L03_begin.xlsm - - our workbook before updates
FinApp_S04_L03_end.xlsm - - our workbook at the end of lesson (which we'll use in next lesson)
You should follow along and recreate these steps in your own workbook so you understand the process.
Some Tasks:
Add a new relationship in the data model
Create a Global Income Statement using power Pivot
There are two Excel workbooks attached to see my starting point and ending point with the final answer.
FinApp_S04_L04_begin.xlsm - - our workbook before updates
FinApp_S04_L04_end.xlsm - - our workbook at the end of lesson (which we'll use in next lesson)
You should follow along and recreate these steps in your own workbook so you understand the process.
Some Tasks:
Explore Excel 'shapes' functionality
Add a text box to your dashboard to detail the steps for dashboard users
There are two Excel workbooks attached to see my starting point and ending point with the final answer.
FinApp_S05_L01_begin.xlsm - - our workbook before updates
FinApp_S05_L01_end.xlsm - - our workbook at the end of lesson (which we'll use in next lesson)
You should follow along and recreate these steps in your own workbook so you understand the process.
Some Tasks:
Explore Excel 'shapes' functionality
Add a text box to your dashboard to detail the steps for dashboard users
There are two Excel workbooks attached to see my starting point and ending point with the final answer.
FinApp_S05_L0--_begin.xlsm - - our workbook before updates
FinApp_S05_L01_end.xlsm - - our workbook at the end of the course!!!
You should follow along and recreate these steps in your own workbook so you understand the process.