Essential Power Query for Management Consultants
What you’ll learn
Perform Essential Operations in Power Query
Use simple tricks that will help you use faster Power Query
Clean up data in Power Query
Merge Data in Power Query
Append Data in Power Query
Partially Automate data collection and data transformation with the help of Power Query
Get lifetime access to all resources (including files shown in the course, presentations)
Prepare data for further analysis efficiently
Requirements
Basic or intermediate Excel
Basic knowledge of economics
Description
What is the aim of this course?During many consulting projects, you will have to improve the quality of data, before you start analyzing them. This process can be immensely improved by Power Query. Power Query enables you also to automate certain repetitive tasks without resorting to Programming in Excel VBA. In this course, I will teach you how to use Power Query during consulting projects.In the course you will learn the following things:What are queries and what is Power Query?How to perform essential transformation with Power QueryHow to add columns in Power Query using conditions, rules, AIHow to use Power Query to clean the dataHow to merge data with Power QueryHow to add/append data with Power QueryWhen to use and when NOT to use Power QueryThis course is based on my 15 years of experience as a consultant in top consulting firms and as a Board Member responsible for strategy, performance improvement, and turn-arounds in the biggest firms in Retail, FMCG, SMG, and B2B, and services sectors that I worked for. I have carried out or supervised over 90 different performance improvement projects in different industries that generated a total of 2 billion in additional EBITDA. On the basis of what you will find in this course, I have trained in person over 100 consultants, business analysts, and managers who now are Partners in PE and VC funds, Investment Directors and Business Analysts in PE and VC, Operational Directors, COO, CRO, CEO, Directors in Consulting Companies, Board Members, etc. On top of that my courses on Udemy were already taken by more than 167 000 students including people working in EY, McKinsey, Walmart, Booz Allen Hamilton, Adidas, Naspers, Alvarez & Marsal, PwC, Dell, Walgreens, Orange, and many others.I teach through case studies, so you will have a lot of lectures showing examples of analyses, and tools that we use. To every lecture, you will find attached (in additional resources) the Excels as well as additional presentations, and materials shown in the lectures so as a part of this course you will also get a library of ready-made analyses that can, with certain modifications, be applied by you or your team in your work.Why have I decided to create this course?Consulting projects are quite often fast-paced and require you a lot of hard work.Since you will have always less time than you need, it’s a good idea to increase your efficiency, especially when it comes to preparing data and analyzing it. Power Query is one of the main tools that will help you 10x the speed of your work. It will also help you automate a lot of work. Most firms, don’t give you the full toolbox that you need in this area. This may lead to huge frustration during consulting projects and a lot of inefficiencies.Therefore, I have decided to create this course that will help students understand and use it in practice Power Query during consulting projects. The course will give you the knowledge of essential features of Power Query and insight into real-life case studies that will make your life during a consulting project much easier. Thanks to this course, you will know how and when to use Power Query during consulting projects. You will master essential features of Power Query, that will make your life much easier.To sum it up, I believe that if you want to become a world-class Management Consultant or Business Analyst you have to have a pretty decent understanding of essential Power Query. That is why, I highly recommend this course to Management Consultants or Business Analysts, especially those that do a lot of data analyses. The course will help you become an expert in Power Query on the level of McKinsey, BCG, Bain, and other top consulting firms.In what way will you benefit from this course?The course is a practical, step-by-step guide loaded with tons of analyses, tricks, and hints that will significantly improve the speed with which you understand, and analyze businesses. There is little theory – mainly examples, a lot of tips from my own experience as well as other notable examples worth mentioning. Our intention is that thanks to the course you will learn:What are queries and what is Power Query?How to perform essential transformation with Power QueryHow to add columns in Power Query using conditions, rules, AIHow to use Power Query to clean the dataHow to merge data with Power QueryHow to add/append data with Power QueryWhen to use and when NOT to use Power QueryYou can also ask me any question either through the discussion field or by messaging me directly.How the course is organized?The course is divided currently into the following sections:Introduction. We begin with a little introduction to the course as well as some general info on how the course is organizedEssential Excel Tables. We will start by talking about Excel Tables. Excel Tables will help you save a lot of time in Power Query. Additionally, the output from Power Query will be returned to an Excel file usually in the form of an Excel Table or a Pivot Table. Therefore, it’s crucial to understand how Excel Tables work and what interesting features you can use to present the data. In this section, we will look at the basic characteristics of Excel Tables.Essential Power Query. During most consulting projects you will be using the essential features of Power Query. Since we believe in the 80/20 rule we will start by discussing those essential features.Case studies devoted to Cleaning Data. As we have mentioned, Power Query will be very useful in improving the quality of data. In this section, we will go through a few case studies devoted to cleaning data.Merge Data. In certain situations, to get the report you want you will have to merge data from different queries. In this section, we will see how this can be done.Append Data. In many situations, you will have to combine data that have the same structure but are spread over different sheets or files. To achieve this, we will use the Append option in Power Query.You will be able also to download many additional resources1. Useful frameworks and techniques2. Analyses presented in the course3. Additional resources4. Links to additional presentations, articles, and movies5. Links to books worth readingAt the end of my course, students will be able to…Perform Essential Operations In Power QueryUse simple tricks that will help you use faster Power QueryClean up data in Power QueryMerge Data in Power QueryAppend Data in Power QueryPartially Automate data collection and data transformation with the help of Power QueryGet lifetime access to all resources (including files shown in the course, and presentations)Prepare data for further analysis efficientlyWho should take this course? Who should not?Management ConsultantsBusiness AnalystsFinancial ControllersFinancial AnalystsData AnalystsInvestment AnalystsWhat will students need to know or do before starting this course?Basic or intermediate ExcelBasic knowledge of economics
Overview
Section 1: Introduction
Lecture 1 Introduction
Lecture 2 A little bit about me
Lecture 3 How to deal with Blurry image
Lecture 4 How to reach additional resources
Section 2: Essential Excel Tables
Lecture 5 Essential Excel Tables – Introduction
Lecture 6 What are Excel Tables?
Lecture 7 How to create a Table in Excel
Lecture 8 Additional features of the Excel Tables
Lecture 9 The elements of the Excel Tables
Lecture 10 Functions based on an Excel Table – Introduction
Lecture 11 Functions based on an Excel Table – Exercise
Lecture 12 Changing the name of the Excel Table
Lecture 13 Changing the look of the Excel Table
Section 3: Essential Power Query
Lecture 14 Essential Power Query – Introduction
Lecture 15 What is a Query
Lecture 16 Proper Tables / Proper Data Sets
Lecture 17 Simple Query in Power Query – Part 1
Lecture 18 Simple Query in Power Query – Part 2
Lecture 19 How to edit existing Queries?
Lecture 20 How to copy existing Queries
Lecture 21 Basic Actions in Power Query – Part 1
Lecture 22 Basic Actions in Power Query – Parameters (Part 2)
Lecture 23 How to Add columns – Time & Date Options
Lecture 24 How to Add columns – Number and Text Options
Lecture 25 Conditional Column (Similar to IF funtions)
Lecture 26 Custom Columns
Lecture 27 Columns from Examples
Lecture 28 Index & Duplicate Columns
Lecture 29 Essential Transformations
Lecture 30 Uploading data when the input data are in the output file
Lecture 31 Uploading data that requires transformation to a Proper Table
Lecture 32 Change in the Source
Lecture 33 How to delete a Query
Lecture 34 The Step-by-Step Approach used by Power Query
Lecture 35 Edit Steps of Existing Power Query – Part 1
Lecture 36 Edit Steps of Existing Power Query – Part 2
Lecture 37 Revenue Calculation – Case Introduction
Lecture 38 Revenue Calculation – Data Available
Lecture 39 Revenue Calculation – Solution
Lecture 40 DIY Chain – Case Introduction
Lecture 41 DIY Chain – Data Available
Lecture 42 DIY Chain – Solution – Part 1
Lecture 43 DIY Chain – Solution – Part 2
Lecture 44 Unpivot columns – Introduction
Lecture 45 Unpivot columns – Exercise
Lecture 46 What you can use the Power Query for
Lecture 47 Benefits of Power Query
Section 4: Case studies devoted to Cleaning Data
Lecture 48 Case studies devoted to Cleaning Data – Introduction
Lecture 49 Chain of Clinics – Case Introduction
Lecture 50 Chain of Clinics – Data Available
Lecture 51 Chain of Clinics – Solution – Tabular Form
Lecture 52 Chain of Clinics – Solution – Pivot Like Form
Lecture 53 Cosmetics Producer – Case Introduction
Lecture 54 Cosmetics Producer – Data Available
Lecture 55 Cosmetics Producer – Solution
Lecture 56 Convenience Store – Case Introduction
Lecture 57 Convenience Store – Data Available
Lecture 58 Convenience Store – Solution – Option 1
Lecture 59 Convenience Store – Solution – Option 2
Section 5: Merge Data
Lecture 60 Merge Data – Introduction
Lecture 61 2 ways to combine data
Lecture 62 Merging Data in Excel vs in Power Query
Lecture 63 Bonus System – VLOOKUP with Exact Match – Left Outer
Lecture 64 Bonus System – VLOOKUP with Approximate Match – Full Outer
Lecture 65 Fuel Consumption Analysis – Case Introduction
Lecture 66 Fuel Consumption Analysis – Data Available
Lecture 67 Fuel Consumption Analysis – Solution – Part 1
Lecture 68 Fuel Consumption Analysis – Solution – Part 2
Lecture 69 Merge Data based on 2 columns – Case Introduction
Lecture 70 Merge Data based on 2 columns – Data Available
Lecture 71 Merge Data based on 2 columns – Solution
Lecture 72 Overview of Merge Options
Lecture 73 Comparison of Projects – Data Available
Lecture 74 Comparison of Projects – Left Outer
Lecture 75 Comparison of Projects – Right Outer
Lecture 76 Comparison of Projects – Inner
Lecture 77 Comparison of Projects – Full Outer
Lecture 78 Comparison of Projects – Anti Outers
Section 6: Append Data
Lecture 79 Append Data – Introduction
Lecture 80 Combine all sheets – Case Introduction
Lecture 81 Combine all sheets – Data Available
Lecture 82 Combine all sheets – Solution
Lecture 83 Combine different files from 1 folder – Case Introduction
Lecture 84 Combine different files from 1 folder – Data Available
Lecture 85 Combine different files from 1 folder – Solution – Part 1
Lecture 86 Combine different files from 1 folder – Solution – Part 2
Section 7: Conclusions
Lecture 87 Bonus Lecture
Management Consultants,Business Analysts,Financial Controllers,Financial Analysts,Data Analysts,Investment Analysts
Course Information:
Udemy | English | 4h 27m | 1.99 GB
Created by: Asen Gyczew
You Can See More Courses in the Business >> Greetings from CourseDown.com