Introduction to Power Query
1. Introduction to Power Query
Hello, my name is Lyndsay Girard, and I will be your instructor for this course: Introduction to Power Query in Excel. By the end of this course, you will become familiar with many of the key features of Excel's Power Query Editor. From data extraction to preparation and loading for analysis, you will harness the power to streamline your next Excel-based report.2. What is Excel Power Query?
So, what is Excel Power Query? It is a powerful data processing tool built into Microsoft Excel. This is also the same tool that is integrated with Microsoft's Power BI - so learning Power Query is powerful knowledge for both platforms. Power Query ultimately enables users to import, shape, and load data into Excel Workbooks in a more streamlined and efficient manner than what would be done in traditional manual Excel methods. Power Query simplifies manual processes in data workflows by enabling the automation of complex data preparation tasks. Above all, Power Query offers a user-friendly interface that does not require programming language -- an invaluable tool for data professionals and analysts!3. Excel Workbook vs Power Query Editor
So, what is the difference between Excel Workbooks and Excel's Power Query editor? Excel Workbooks are the familiar spreadsheet software optimal for manual data entry and formatting. It is optimized for analysis, reporting, and various visualizations. The Excel Power Query editor is the data processing companion to Excel. It integrates data processing into data workflows that can be automated.4. The Power of Power Query
Like a well-crafted recipe, the Power of Power Query is ultimately in its ability to conduct an entire workflow through a reproducible sequence of steps. Much like mixing, seasoning, and cooking a variety of ingredients through a documented recipe creates a delicious batch of cupcakes,5. The Power of Power Query
Power Query brings together data from multiple sources, transforms and shapes the data, and then, when the data preparation is complete,6. The Power of Power Query
it presents a beautifully curated dataset, ready to load into an Excel workbook. This process is also known as a data workflow.7. ETL in Excel Power Query
You might be familiar with the acronym ETL or Extract, Transform, and Load. ETL is the essence of data workflows and is enabled by tools like Excel Power Query. Let's break down the components of ETL.8. ETL in Excel Power Query
Extract involves gathering data from various sources. Power Query lets you connect to different data sources, such as databases, spreadsheets, web services, and files.9. ETL in Excel Power Query
Transform, which includes a series of transformations to make data suitable for analysis, ensures that the data is consistent, accurate, and in a standardized format.10. ETL in Excel Power Query
Loading involves storing the data in a structured manner, making it readily available for reporting, analysis, and business intelligence.11. ETL
In this chapter, we will be focusing on "Extract", which largely encompasses data importing. This involves bringing in data from various sources into the structured workflow.12. ETL
More specifically, in the data import phase of the overall workflow, we will tackle things like loading datasets, naming and editing columns and rows, and entering data, among others.13. ETL
We will also cover some of the enabling features that Power Query offers. In our first chapter, we will review how Power Query handily documents all query steps in an applied steps log. This is extremely helpful for maintaining reproducibility and keeping detailed step-by-step instructions on how the final queries were derived.14. ETL
Lastly, we will also introduce within the "Load" portion of the workflow by performing data loads from Excel Power Query into your Excel Workbook.15. Data Sources
In this course, we will work with various fictitious datasets, all related to the healthcare sector. We will assess daily census datasets, which document a daily snapshot of activity across several hospitals. We will also review a drug administrations dataset, which is essentially a transaction log of all medications administered to patients in a hospital over a period of time. Lastly, we will also assess a patient experience survey questionnaire and the responses that were provided.16. Let's practice!
Okay, let's practice!Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.