Revealed: Data Consolidations with Microsoft Power BI Tools
4.4 (10 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
73 students enrolled

Revealed: Data Consolidations with Microsoft Power BI Tools

All The Power BI Consolidation Secrets Microsoft Was Supposed To Show You, But Never Did
4.4 (10 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
73 students enrolled
Created by Doug Burke
Last updated 1/2019
English
English [Auto-generated]
Current price: $48.99 Original price: $69.99 Discount: 30% off
5 hours left at this price!
30-Day Money-Back Guarantee
This course includes
  • 3.5 hours on-demand video
  • 4 articles
  • 52 downloadable resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
Training 5 or more people?

Get your team access to 4,000+ top Udemy courses anytime, anywhere.

Try Udemy for Business
What you'll learn
  • 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
Requirements
  • 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
Description

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.

Who this course is for:
  • 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
Course content
Expand all 30 lectures 03:28:48
+ Let's Get Started!
3 lectures 14:34

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.

Download the Sample Files
01:21

Key concept

Power Query can do many data transformations.

We will learn them throughout this course.

Hello, World!
09:33
+ Trial Balance (The Data)
6 lectures 40:00
Purpose of This Section
00:33

Key concept 

Loading data to Power Query and cleaning that data is simple once you're shown how it's done

Section 1, Lesson 01 - Load metadata - Chart of Accounts (Power Query)
10:00

Key Concept

Connect and load Chart of Account metadata file using Power Query

Section 1, Lesson 02 -Load metadata - Chart of Accounts (Power Query)
07:55

Key Concept

Connect Trial Balance data to Chart of Accounts metadata using Excel Data Model

Preview 10:45

Key Concept

Create Power Pivot Report using your linked Trial Balance data and Chart of Accounts metadata

Analyze data (Power Pivot)
08:43
Learn By Doing - Build your Financial Consolidations Data Section
02:04
+ Trial Balance (The Dashboard)
6 lectures 41:06

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:

  1. Download the Excel file

  2. Watch the video, follow along and read the attached pdf file

  3. 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
 

Purpose of This Section
02:10

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.

  1. FinApp_S02_L01.xlsx - - our workbook before macros

  2. 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.

Preview 07:55

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

  1. 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.

Section 2, Lesson 02 - Add Navigation Macros
10:42

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

  1. FinApp_S02_L03.xlsm - - one workbook with two connections to two queries


You should follow along in your own sheet and review the connections.

Section 2, Lesson 03 - Refresh the Dashboard
05:11

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

  1. 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.

Section 2, Lesson 04 - Clean Up the Dashboard
13:27

Congratulations on finishing this section!


Please email me at doug@dougburkedata.com with any questions.

Learn By Doing - Let's build a Financial Consolidations Dashboard
01:41
+ Trial Balance (The Decisions)
6 lectures 36:38
Purpose of This Section
00:17

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.

  1. FinApp_S03_L01_begin.xlsx - - our workbook before updates

  2. 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.

Section 3, Lesson 01 - Data Cleaning your Power Pivot Reports
10:39

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.

  1. FinApp_S03_L02_begin.xlsx - - our workbook before updates

  2. 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.

Preview 06:30

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.

  1. FinApp_S03_L03_begin.xlsm  - - our workbook before updates

  2. 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.

Section 3, Lesson 03 - Hiding Report Member Names
04:28

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.

  1. FinApp_S03_L04_begin.xlsx - - our workbook before updates

  2. 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.

Section 3, Lesson 04 - Adding Report Data Columns
12:41

Congratulations on finishing this section!


Please email me at doug@dougburkedata.com with any questions.

Learn By Doing - Build your Financial Consolidations Report
02:03
+ Income Statement (The Data)
5 lectures 58:34
Purpose of This Section
02:46

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.

  1. FinApp_S04_L01_begin.xlsm - - our workbook before updates

  2. 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.

Section 4, Lesson 01 - Create an Income Statement
12:03

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.

  1. FinApp_S04_L02_begin.xlsm  - - our workbook before updates

  2. 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.

Section 4, Lesson 02 - Data Load for a Second Legal Entity
14:25

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.

  1. FinApp_S04_L03_begin.xlsm  - - our workbook before updates

  2. 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.

Section 4, Lesson 03 - Combining Income Statements (E01 and E02)
14:51

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.

  1. FinApp_S04_L04_begin.xlsm  - - our workbook before updates

  2. 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.

Section 4, Lesson 04 - Global Income Statement
14:29
+ Income Statement (The Dashboard)
2 lectures 17:00

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.

  1. FinApp_S05_L01_begin.xlsm  - - our workbook before updates

  2. 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.

Section 5, Lesson 1 - Add a Dashboard Text Box
06:28

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.

  1. FinApp_S05_L0--_begin.xlsm  - - our workbook before updates

  2. 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.

Section 5, Lesson 2 - Fix Broken Dashboard Links
10:32
+ SUCCESS!!!!
2 lectures 00:55
BONUS - - Congratulations! Some Further Resources and Contact Information
00:30
Please Rate and Review
00:24