Excel: Power Query (Get & Transform)

سرفصل های دوره

Power Query is a feature in Excel that allows you to quickly import data from multiple sources and easily clean, transform, and reshape it to suit your needs. Follow along with Excel MVP Oz du Soleil as he shows you how to use this powerful, time-saving tool. Oz demonstrates how to import, merge, rearrange, and clean data, as well as how to repeat the process with one click if the data changes. Discover how to split columns, unpivot data, and use joins to merge, segment, and compare datasets. Oz offers dozens of techniques and tips in this comprehensive training.

01 - Introduction

01 - Make your data useful with Power Query

02 - 1. What Is Power Query (Get & Transform)

01 - Power Query example

02 - Differences between Excel and Power Query

03 - 2. Working with Queries

01 - Data types explained

02 - Query data from a table or range

03 - Query data from another Excel file

04 - Load data only as a connection

04 - 3. Working with Columns

01 - Fill up and fill down

02 - Split column by delimiter

03 - Split into rows

04 - Add conditional and custom columns

05 - Add column by example

06 - Merge columns

07 - Sort and filter data in Power Query

05 - 4. Working with Formulas

01 - Use IF formulas

02 - Nest IF and AND

03 - AddDays to determine the deadline

06 - 5. Pivoting and Unpivoting Data

01 - Pivot data in Power Query

02 - Pivot and append data

03 - Pivot and dont aggregate

04 - Unpivot data in Power Query

05 - Unpivot warnings

07 - 6. Grouping

01 - Group By feature

08 - 7. Appending Queries

01 - Two data sets

02 - Multiple tables

03 - Query data from a folder and import multiple files

04 - Append multiple sheets

09 - 8. Midterm Challenge

01 - Midterm challenge Get a count of all colors

10 - 9. Merging Data with Joins

01 - Overview of joins in Power Query

02 - Walk through all six joins

03 - Joins Left or right

04 - Outer join versus XLOOKUP

05 - Merge with multiple fields

06 - Approximate match equivalent of VLOOKUP Binning

07 - Approximate match equivalent of VLOOKUP Conditional column

08 - Cross Join

11 - 10. Drill Down to Create Variables

01 - Drill down to create a variable in Power Query

12 - 11. Fuzzy Matching

01 - Fuzzy matching by percentage

02 - Merging inconsistent data with a transformation table

13 - 12. Apply Your Learning with Real-World Challenges

01 - Challenge setup

02 - Real-world challenge 1 Projects

03 - Real-world challenge 2 Donors

04 - Real-world challenge 3 Soccer team

14 - Conclusion

01 - Next steps

139,000 تومان

خرید اشتراک افزودن به سبد خرید

خرید دانلودی فوری

در این روش نیاز به افزودن محصول به سبد خرید و تکمیل اطلاعات نیست و شما پس از وارد کردن ایمیل خود و طی کردن مراحل پرداخت لینک های دریافت محصولات را در ایمیل خود دریافت خواهید کرد.

تولید کننده: LinkedIn Learning (Lynda)