Excel BI - Power Query

Power Query brings the power of Data to the ordinary business and the Excel user.

Power Query enhances self-service business intelligence (BI) for Excel with an intuitive and consistent experience for discovering, combining, and refining data across a wide variety of sources including relational, structured and semi-structured, OData, Web, Hadoop, Azure Marketplace, and more.

Power Query brings the power of Data to the ordinary business and the Excel user.


COURSE SUMMARY

Power Query is an Excel add-in, available in Excel 2010 Professional Plus and Excel 2013. In Excel 2016 it is known as Get and discover data. Power Query can be described as SQL for Excel users (without the need for knowing SQL). It allows uses find data from multiple sources, combine data, transform data, and make the data usable for reporting and data analytics.

LEARN HOW SET UP QUERIES TO EXTRACT DATA FROM MULTIPLE SOURCES, TRANSFORM DATA OF DIFFERENT TYPES AND LEARN HOW TO USE POWER QUERY TO AUTOMATE DAILY TASKS.

MICROSOFT'S EXCEL, WHICH INCLUDES POWER QUERY, IS NOW PART OF A BI SYSTEM. IF YOU WANT TO IMPROVE YOUR EXCEL SKILLS FAR BEYOND THE BASIC LEVEL AND BRING BUSINESS ANALYTICS AND DATA TO YOUR ORGANISATION THEN THIS COURSE IS FOR YOU


CONTENTS AND OVERVIEW

In section one of this course we will start at the very basics and learn how to set up Power Query. In this section you will find sample data to download, so you can practice along.

Section two of this course works though a case study on how an organisation can use internal data of different file types, such as Text files and Excel files, and then combine this data with External data from the web to accurately calculate the potential market size, the actual market size and the portion of the market held by the company. This case study will give you a solid working knowledge of Power Query.

In section 3 we will look at some more advanced features of Power Query. You will be introduced to the language use, which is known as M, you will learn how to query not just files of different types but also Folders of data. You will also learn how to sort “Bad Data” and user Power Query to automate reoccurring tasks.


Who Should Attend?

This course is suited to those with an interest in Excel, Data Analyst's, Data, BI, Reporting and is well suited for Accountants, Auditors, Internal Auditors, Business Analysts, Finance staff, Banking staff, Management and those involved with Compliance and Risk assessment and BI.

• This course is meant for those involved in the Analysis of Data, Finance & Accounting and Reporting roles.

• This course is for you if you are looking to learn Excels self-service BI functions.

• This course is not for you if you are new to Excel.


What’s included?

Instruction by an expert

12 months access 24/7

Certificate of completion


How long will it take?

Completion Time 2 Hours



Intended Audience: Excel Users

How to take this course
Download and set up Power Query
Getting set up
Course Files
Case Study - Scenario
How to connect to an Excel file
Activity 1
How to connect to an Text file
Activity 2
How to connect to web data
Activity 3
How to Merge and Analyse the data
Activity 4
Connect to Folders of Data
Activity 5
Bad Data
Activity 6
Unpivot data and carry out same query on different file
Activity 7
Useful Resources
Course conclusion

What's included

  • 11 Video Lessons
  • 10 Text Lessons

  • Works on all devices
  • Certificate of completion

Paula Guilfoyle

Instructor

My Name is Paula, and I am a qualified CPA with over 15 years’ experience in Accounting and Business. I have worked across a broad range of industries and levels of seniority. During this time I used Excel as my spreadsheet tool of choice, attending many courses and regularly updating my skills. It was my love for Excel that started my journey as an e-learning educator. As a manager and an Accountant, I noticed a skills gap amount Accountants and finance staff in Excel. In 2010 I launched www.theexcelclub.com with 1 course aimed at this market. I am now author and presenter of many online courses and can boast that I have thought over 10,000 students!

In addition to being an Accountant and an Online Educators I am also a wife and a mother of two, my youngest is 2 and eldest is 7. I currently work as an Accountant for a global company, which makes family life very very busy. I am always stuck for time, and finding a balance is very difficult. I’m sure all working parents understand the pressures involved.