Revealed: Data Consolidations with Microsoft Power BI Tools

Name: Revealed: Data Consolidations with Microsoft Power BI Tools
Rating: 4.4 (22 reviews)

All The Power BI Consolidation Secrets Microsoft Was Supposed To Show You, But Never Did

Created byDoug Burke

Last updated 2/2019

English

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

Course content

7 sections • 30 lectures • 3h 28m total length

Brief Introduction3:40
Download the Sample Files1:21
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.
Hello, World!9:33
Key concept
Power Query can do many data transformations.
We will learn them throughout this course.

Purpose of This Section0:33
Section 1, Lesson 01 - Load metadata - Chart of Accounts (Power Query)10:00
Key concept
Loading data to Power Query and cleaning that data is simple once you're shown how it's done
Section 1, Lesson 02 -Load metadata - Chart of Accounts (Power Query)7:55
Key Concept
Connect and load Chart of Account metadata file using Power Query
Link data to metadata (Data Modeling)10:45
Key Concept
Connect Trial Balance data to Chart of Accounts metadata using Excel Data Model
Analyze data (Power Pivot)8:43
Key Concept
Create Power Pivot Report using your linked Trial Balance data and Chart of Accounts metadata
Learn By Doing - Build your Financial Consolidations Data Section2:04

Purpose of This Section2:10
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
Section 2, Lesson 01 - Create the Dashboard7:55
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.
Section 2, Lesson 02 - Add Navigation Macros10:42
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.
Section 2, Lesson 03 - Refresh the Dashboard5:11
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.
Section 2, Lesson 04 - Clean Up the Dashboard13:27
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.
Learn By Doing - Let's build a Financial Consolidations Dashboard1:41
Congratulations on finishing this section!

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

Purpose of This Section0:17
Section 3, Lesson 01 - Data Cleaning your Power Pivot Reports10:39
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.
Section 3, Lesson 02 - Sorting your Power Pivot Reports6:30
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.
Section 3, Lesson 03 - Hiding Report Member Names4:28
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.
Section 3, Lesson 04 - Adding Report Data Columns12:41
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.
Learn By Doing - Build your Financial Consolidations Report2:03
Congratulations on finishing this section!

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

Purpose of This Section2:46
Section 4, Lesson 01 - Create an Income Statement12:03
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.
Section 4, Lesson 02 - Data Load for a Second Legal Entity14:25
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.
Section 4, Lesson 03 - Combining Income Statements (E01 and E02)14:51
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.
Section 4, Lesson 04 - Global Income Statement14:29
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.

Section 5, Lesson 1 - Add a Dashboard Text Box6: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.
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.
Section 5, Lesson 2 - Fix Broken Dashboard Links10:32
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.

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

Revealed: Data Consolidations with Microsoft Power BI Tools

What you'll learn

Explore related topics

Course content

Let's Get Started!3 lectures • 15min

Trial Balance (The Data)6 lectures • 40min

Trial Balance (The Dashboard)6 lectures • 41min

Trial Balance (The Decisions)6 lectures • 37min

Income Statement (The Data)5 lectures • 59min

Income Statement (The Dashboard)2 lectures • 17min

SUCCESS!!!!2 lectures • 1min

Requirements

Description

Who this course is for: