Business Intelligence Workflow with Excel 2019 from Pluralsight

This course equips data analysts and Excel power users with the Business Intelligence knowledge and best practices required to import data into Excel from different data sources, clean the data, and transform this data into any desired format.

Data transformation from different sources is key when working in Excel. In this course,

, you will be equipped with Business Intelligence knowledge, tools, and best practices required to import data into Excel from different data sources, clean the data, and transform this data into any desired format.

, you will see how Power Query is an easy-to-use, yet powerful tool to import data from different sources.

, you will learn how Power Query can cleanse and transform data without having to write any code.

, you will also study What-if analysis which allows users to use several data sets in one or more formulas to explore various results.

, you will study Cube functions which help to pull data from Analysis Services into Excel.

When you are finished with this course, you will have an understanding of the insertion and transformation process data goes through when imported into Excel.

This course equips data analysts and Excel power users with Business Intelligence knowledge and best practices.

Learn how Power Query is an easy-to-use, yet powerful tool to import data into Excel, and cleanse and transform data without having to write any code. Then study Cube functions which help to pull data from Analysis Services into Excel.

You likely already know your way around Excel but if you have only ever imported data into Excel by copy and pasting data from different sources, prepare to be blown away by this advanced-level course.

If you're using Excel 2010 or 2013, then you can download Power Query as a separate free add-on. If you're using Excel 2016 or later, then don't worry - you already have Power Query.

What's inside

Syllabus

Course Overview

Importing Data from Different Data Sources

Queries in Power Query

Transform and Consolidate Data

What-if Analysis

Cube Functions

Good to know

Know what's good

, what to watch for

, and possible dealbreakers

Taught by Amruta Mahajan, who are recognized for their work in data transformation

Teaches what-if analysis, which helps users extend their foundational studies of Excel

Develops skills in data import, transformation, and cleansing

Teaches advanced-level data analysis for experienced Excel users

Explores Power Query, which is highly relevant to modern Excel data analysis

Activities

Be better prepared before your course. Deepen your understanding during and after it. Supplement your coursework and achieve mastery of the topics covered in Business Intelligence Workflow with Excel 2019 with these activities:

Read 'Data Analysis with Excel' by Greg Harvey

Show steps

This book is designed to assist in preparing for this course by providing a foundation in data analysis using Excel.

View Excel Workbook For Dummies on Amazon

Show steps

Read the book
Complete the exercises in the book

Review relational database theory

Show steps

Refresh your understanding of the concepts of relational databases to lay a foundation for learning about business intelligence and data transformation in Excel.

Browse courses on Relational Data Model

Show steps

Review a textbook or online course on relational database theory.
Complete practice exercises on data modeling and normalization.

Review fundamental Excel skills

Show steps

Reviewing fundamental Excel skills will set you up for success in this course by ensuring you are comfortable with basic functions and operations.

Browse courses on Excel Basics

Show steps

Create and format a new spreadsheet
Enter and manipulate data
Use basic formulas and functions
Create simple charts and graphs

Five other activities

Expand to see all activities and additional details

Show all eight activities

Review the Syllabus

Show steps

Familiarize yourself with key concepts by reviewing the course syllabus and identify areas in which you might need more preparation.

Show steps

Read the course syllabus thoroughly
Identify key concepts, terms, and tools
Note down any prerequisites or areas where you may need additional support

Create a collection of Excel templates and resources

Show steps

Making a collection of Excel templates and resources will provide you with a valuable repository of tools that you can use for your coursework.

Show steps

Gather Excel templates and resources from various sources
Organize the templates and resources into a central location

Practice importing data from different sources

Show steps

This activity will solidify your understanding of how to import data into Excel from different sources, which will be an essential skill to have for your course.

Browse courses on Data Import

Show steps

Import data from a text file
Import data from a CSV file
Import data from a web page
Import data from a database

Learn how to use Power Query to clean and transform data

Show steps

Gaining proficiency with Power Query will greatly benefit you in this course by giving you the skills to efficiently clean and transform data.

Browse courses on Power Query

Show steps

Watch a tutorial on using Power Query
Follow along with the tutorial and practice using Power Query
Apply Power Query to your own datasets

Create a data model using Power BI

Show steps

Demonstrating your proficiency in data modeling using Power BI via a data visualization project will provide practical application to the principles you'll learn in class.

Browse courses on Data Modeling

Show steps

Gather data from different sources
Create a data model using Power BI
Create visualizations using Power BI

Career center

Learners who complete Business Intelligence Workflow with Excel 2019 will develop knowledge and skills that may be useful to these careers:

Business Intelligence Analyst

Business Intelligence Analysts use data analysis and data visualization to help businesses make informed decisions. They use their skills in data mining, data analysis, and data visualization to identify trends, patterns, and opportunities.

See salaries and explore the career path for Business Intelligence Analyst

Data Analyst

Data Analysts use their skills in research, analysis, and data interpretation to solve business problems. They collect, clean, and analyze data from a variety of sources to identify trends and patterns. This course can help build a foundation for a career as a Data Analyst by providing the knowledge and skills needed to import, clean, and transform data into a usable format.

See salaries and explore the career path for Data Analyst

Data Engineer

Data Engineers design, build, and maintain the infrastructure that supports data storage and processing. They use their skills in data architecture, data engineering, and cloud computing to build and manage data pipelines and data warehouses.

See salaries and explore the career path for Data Engineer

Data Scientist

Data Scientists use their skills in mathematics, statistics, and computer science to solve complex business problems. They use their knowledge of data analysis and data mining to build predictive models and develop data-driven solutions.

See salaries and explore the career path for Data Scientist

Data Architect

Data Architects design and build the data architecture for an organization. They use their skills in data modeling, data integration, and data governance to create a data architecture that meets the needs of the business.

See salaries and explore the career path for Data Architect

Database Administrator

Database Administrators manage and maintain databases. They use their skills in database management, data recovery, and data security to ensure that databases are running smoothly and that data is secure.

See salaries and explore the career path for Database Administrator

Systems Engineer

Systems Engineers design, build, and maintain computer systems. They use their skills in systems engineering, computer science, and networking to build and maintain computer systems that meet the needs of the business.

See salaries and explore the career path for Systems Engineer

Information Security Analyst

Information Security Analysts protect computer systems and networks from cyberattacks. They use their skills in cybersecurity, network security, and data security to identify and mitigate security threats.

See salaries and explore the career path for Information Security Analyst

Security Analyst

Security Analysts protect computer systems and networks from cyberattacks. They use their skills in cybersecurity, network security, and data security to identify and mitigate security threats.

See salaries and explore the career path for Security Analyst

Cybersecurity Analyst

Cybersecurity Analysts protect computer systems and networks from cyberattacks. They use their skills in cybersecurity, network security, and data security to identify and mitigate security threats.

See salaries and explore the career path for Cybersecurity Analyst

Network Engineer

Network Engineers design, build, and maintain computer networks. They use their skills in networking, network security, and network management to build and maintain networks that meet the needs of the business.

See salaries and explore the career path for Network Engineer

Software Engineer

Software Engineers design, develop, and maintain software applications. They use their skills in software engineering, programming, and computer science to build and maintain software systems.

See salaries and explore the career path for Software Engineer

Business Analyst

Business Analysts analyze business processes and systems to identify areas for improvement. They use their skills in business analysis, process improvement, and data analysis to help businesses improve their efficiency and effectiveness.

See salaries and explore the career path for Business Analyst

Management Consultant

Management Consultants advise businesses on how to improve their performance. They use their skills in business analysis, process improvement, and data analysis to help businesses identify and solve their business problems.

See salaries and explore the career path for Management Consultant

Financial Analyst

Financial Analysts analyze financial data to make investment recommendations. They use their skills in financial analysis, data analysis, and modeling to help investors make informed investment decisions.

See salaries and explore the career path for Financial Analyst