Excel Power Tools for Data Analysis

Macquarie University via Coursera

Coursera based on 1,221 ratings

Likes

Overview

Welcome to Excel Power Tools for Data Analysis. In this four-week course, we introduce Power Query, Power Pivot and Power BI, three power tools for transforming, analysing and presenting data. Excel's ease and flexibility have long made it a tool of choice for doing data analysis, but it does have some inherent limitations: for one, truly "big" data simply does not fit in a spreadsheet and for another, the process of importing and cleaning data can be a repetitive, time-consuming...

Syllabus

Welcome and critical information

Welcome to Excel Power Tools for Data Analysis. In this course, you will learn about importing and transforming data with Power Query, working with huge datasets in Power Pivot, and creating interactive reports with Power BI. This introductory material will orient you into the course. We encourage you to think about your goals for the course and share them with your fellow learners.

Get and Transform (Power Query)

Often the first steps when analysing data are to import the data and combine different datasets together. In Excel, you can use Get and Transform, previously known as Power Query, to with this. In this module, you will learn how to import data from various sources and the different ways to combine datasets depending on your requirements.

Transforming data in the Query Editor

Once your data is imported and combined, you then move on to transforming it. A common operation is to pivot data between wide and long formats. You can group data and split a column into multiple columns. Power Query has a few extra options that a normal PivotTable doesn't have.

Power Pivot and the Data Model

An Excel workbook can handle up to 1 million rows, which sounds like a lot but sometimes you have more data than that. The Data Model in Excel is only limited by the amount of memory your computer has. You can also define database-like relationships between tables. Then you can visualise your data using Power Pivot and cube functions, and create PivotTables.

Visualising Data with Power BI

We are moving out of Excel with this module. Power BI is Microsoft's Business Intelligence tool. You can put into practice the skills that you have learned in Power Query, M, and DAX, to create dynamic and interactive reports and dashboards in Power BI. Once you have the report looking how you want, share it with others.