Leila Gharani – Master Excel Power Query – Beginner to Pro (including M)

Leila Gharani – Master Excel Power Query – Beginner to Pro (including M)BECOME THE EXCEL HERO WITH POWER QUERYDiscover the smarter, faster way to handle data with our Power Query course. You’ll be able to turn every data task into a chance to stand out. Become the go-to Excel problem-solver at work with this essential skill.This course is for everyone, no matter what job you do. We’ll start from scratch and before you know it, you’ll become a Power Query expert.Think of this course as your all-in-one solution. While you might find snippets of Power Query knowledge on YouTube, they fall short. Ours is comprehensive, covering niche, real-world topics that free content usually doesn’t touch. It’s about making your work life not only easier but also more enjoyable.What you’ll achieve with our Power Query CourseEffortless Efficiency!Yes, efficiency can be effortless once you discover a new way of handling your data. The only regret? Not discovering this sooner – which is what we hear the most from our members. But remember the saying: The best time to get started was yesterday. The second best time is today.Become the Office StarEven if you prefer to stay low-key, your newfound Power Query skills will speak volumes. You’ll naturally become known as the go-to problem-solver in your office, a reliable expert whom colleagues admire and turn to for solutions.Master Problem-Solving TechniquesStart using outside-the-box techniques to tackle Excel challenges. Whether it’s combining data from various sources, importing from PDFs or the web, or tidying up messy data filled with spaces, blank cells or messed up columns, this course will teach you smarter techniques, saving you time and energy.How Power Query Can Change Your LifeMake Your Excel Life Easier with this Power Query TrainingInside your Microsoft Excel window sits a magic button.It’s not called “Power Query” – instead it’s called “Get Data” (in the Data tab).Your data could be ANYWHERE. It could be sitting right there on the sheet you’re on. It could be in another Excel file, in a text file, on the web or in a PDF document.The instant you click this button, a new world opens up, helping you to quickly complete tasks – whether it’s cleaning up mixed-up rows or messy columns, combining data with other files or anything else you need done.Once you know your way a round – even just a little bit – you get to do things that only Excel gurus used to be able to do.Now these advanced techniques are just a “click” away for you.They can save you from pulling your hair out as you clean data.You can replace hours and hours of manual labor.And you can ship reports to your boss and colleagues faster – winning you praise and promotions.The best part: Power Query remembers your steps!Next time you need to do the same thing, you just open your file and click “Refresh” – then sit back and enjoy the show (i.e. watch the magic happen as your data updates automatically).Let me give you more details – let’s visit a typical day in the life of an Excel user. Start using Excel in the Power Mode. Just by finishing the FIRST hour, you’ll be amazed at what you can achieve with Power Query. It will give you tons of ideas on how to make your work easier.Here’s Your Learning JourneyLeila Gharani introduces you step-by-step to Excel’s biggest time-savings feature.You’ll be up and running with Power Query in just an hour. You can view the detailed curriculum below.See anything that jumps out? Which lesson do you think you’d begin with?Section 1: IntroductionApplications of Power QueryImportant: Excel Versions, Expectations & Course Pathways(For older Excel versions) Workaround for Loading as Pivot TableDOWNLOAD ALL Files & How to Use ThemQuick Check-inSection 2: Power Query EssentialsWhat You Can Achieve by the EndWhat is a Proper Data Set?(In case you need to) Links to Brush up Your Excel)Section 3: The Power of Power QueryAnalyze Large Data Quickly! Pivot Table or Power Query?Power Query Overview: Import Large Data from Another FilePower Query Editor & Basic TransformationQuick Insights on Data Quality & Distribution (Hidden Feature)Formula Bar, Applied Steps & M CodeClose & Load Destinations (How to Change Them)Refresh Data & PQ Refresh OptionsImport CSV File & Extract Text Based on PatternMerge Data with Another File (Pivot Table from Multiple Files)Old School Method: What if There Was NO Power Query?Activity: Get Familiar with PQSection 4: Important Power Query Tips & TricksUploading Data From ExcelThe Hidden Table Method (Hidden Feature)Handling Changes to SourceData TypesData Types vs. Formatting & Null ValuesPower Query Navigation Shortcuts (Become Pro)Finding & Correcting Errors in DataMore Data Views: Duplicate OR Reference Query?Keeping an Eye on Query DependenciesDelete, Manage, Copy Queries & Backup Results (Become Pro)CHALLENGE – Find and Correct the MistakeQUIZ – Power Query Tips & TricksBest Practice for Power QuerySection 5: Helpful Power Query TransformationsText Transformations (Format, Extract & more)Merging Columns & What to Watch Out ForFill & Replace Values to Create Proper DatasetsSort Data including Multiple LevelsRemove Duplicates including Multiple ColumnsNumber Transformations & What to Watch Out ForWorking with Filter (AND & OR Conditions)CHALLENGE – Can you Find the Mistake? Problem with FilterChange Type & Remove Columns TrapKey Takeaways – Helpful Power Query TransformationsSection 6: Powerful Power Query TransformationsColumn From Examples – Extract Patterns QuicklyAllocate Data to Groups or Buckets (Become Pro)Conditional Columns in Power QueryAggregating (Grouping) Data on Multiple LevelsGroup By for All Rows (Become Pro)Unpivot Columns – BasicsUnpivot & How to Overcome Common ErrorsPivot Columns – BasicsProblem with Split by Delimiter (that’s easy to miss)Split Column by Rows instead of ColumnsCHALLENGE – Summarize Sales By StateSolution: Summarize Sales By State (Bonus Map Chart Included)Section 7: Date & Time TransformationsDate Transformations (Extract Age, Weekday etc.)Creating Dates from Text or Columns (Become Pro)Time Transformations (Calculating Hours worked)Date & Number Errors When Importing Data (Locale)CHALLENGE – Pivot Table for Hours Worked by Month & Week NumberSolution: Pivot Table for Hours Worked by Month & Week NumberQUIZ – Date & Time TransformationsSection 8: Custom Column & Basic M ManipulationImportant Basic Power Query M LogicWhy Use Custom ColumnsIntroduction to “Add Custom Column”Custom Columns Type Compatibility & Intrinsic FunctionsSkipping Steps in Power Query (Become Pro)Adjusting FILTER & Conditional Columns to Reference a Dynamic VariableDrill-Down in Power QueryCustom Formulas for Template Creation (Hidden Tip)QUIZ – Custom Columns and Basic MKey Takeaways – M BasicsSection 9: Power Query Data Sources (Online)Connecting to different SourcesImport Data from a WebsiteAutomatically Connect to Files on Websites (Hidden Feature)Import Data from ODATAGet Google Sheet Data with Power QueryConnect to Outlook Online (Microsoft Exchange)Connect to SharePoint or OneDrive for BusinessPro Tip: How to Change Source from Local to SharePointCHALLENGE – Google Sheets Survey “Your Dinner Plans”BONUS FREE LIBRARY & NEW Custom Data TypesSection 10: Combining / Appending DataWhy Append Data? The Difference Between Merge & AppendCombine / Append Data from Multiple WorkbooksCombine All Files in a Folder (with Excel Tables)Combine All Files in a Folder (Without Excel Tables)How to Adjust Folder Path from Local to SharePoint DriveCombine All Sheets in a File (Pivot Table from Multiple Sheets)Overcome Potential Errors when Combining Sheets (Become Pro)Consolidate Data from Multiple Sheets in the Current WorkbookCHALLENGE – Consolidate Data from Folder (with a twist)Solution: Consolidate Data from Folder (with a twist)Tips & Key Takeaways When Appending in Power QuerySection 11: PROJECT – Report on HR DataTest Your PQ Knowledge by Completing This ProjectCalculate Years Worked in Each PositionCalculate Years Worked in the CompanySplit First, Last & Middle Names to Separate ColumnsKey Takeaways – Data Analysis & Preparation with Power QuerySection 12: All Merge Options – Join Kind Options ExplainedOverview of Merge Options and Join KindsLeft Outer Join & Right Outer JoinMerge Based on Multiple Columns (Become Pro)Can You Find the Mistake? Merging Text ColumnsMerge Data to Get Multiple Match Results & Bonus TipInner & Full Join in Power QueryLeft & Right Anti Join when Merging in Power QueryHow to Use Fuzzy Match in Power QueryFuzzy Match with Transformation Table (Hidden Feature)CHALLENGE – Someone Hacked the Salary File – Find Mismatches in DataSolution: Someone Hacked the Salary File – Find Mismatches in DataKey Takeaways – Power Query Merge KindsSection 13: When to Use Power Pivot & Load to Data ModelWhen to Load Data to the Data ModelAvailability of Power PivotPivot Table from Multiple Excel TablesPower Pivot Table with Data Model & Power QueryCreate a Calendar Table in Power PivotPivot Slicers & TimeLine with Power Pivot & Power Query (Become Pro)Key Takeaways – Data Model & Power QuerySection 14: Power Query Advanced to ProWhat You Can Achieve as an Advanced Power Query UserYou’ve Come So Far!Section 15: Solving Complex Data Analysis Problems with MergeLearning Objectives for this SectionMessy Data from Multiple Rows to One Row (Create Proper Data Set)Search and Replace Bulk ValuesCalculate Value Difference to Previous RowApproximate Match Lookup with MergeCHALLENGE: Create a Report based on Multiple ParametersSolution: Create a Report based on Multiple ParametersSection 16: Advanced Problem Solving using the Power Query InterfaceLearning Objectives for this SectionAssign Unique Number to GroupAdvanced Unpivot TechniquesAdvanced Pivot TechniquesIncremental Data Load & Self Referencing Query (Hidden Tip)Key Takeaways: Advanced Problem SolvingSection 17: PROJECT – Create a Modern Excel Dashboard with Power Query & Pivot TablesExcel Dashboard Project OverviewPrepare a Draft of the DashboardImport Master data from External Workbook with Power QueryImport Data from Text File with Power QueryCreate the Data Model & Define Relationships in Power PivotCreate Logic for Latest and Previous Month in Power QuerySetup Calculations with Pivot Tables for Latest MonthLink Excel Shapes to Data & Linked Picture TrickTop 3 Sales Managers & Numbers (Sorted Excel Pivot Table)Linked Table for Sales by Product CategoryExcel Pivot Chart for Monthly SalesPivot Slicer Connected to Multiple Pivot TablesFinalize the Excel DashboardKey Takeaways: Excel Dashboard ProjectSection 18: Understanding M Formula Language (Let, Lists, Records & Improving Performance)M Language – How M Thinks (let Expression & Values)Defining & Invoking Custom M FunctionsReference Guide for Standard M FunctionsLists & Records and how to Reference a Specific Cell in a TableSummarizing Brackets & Lookup Operators in M codeCreating Lists and Tables inside Power QueryUnderstand Each Keyword & the Purpose of _ (underscore)Using Power Query ParametersIF Then & Lookup Operators to Lookup Values in Previous RowError Handling – Bulk Replace Lookup with Try OtherwiseSpeed up Queries: Table.Buffer & How to Test Impact (Hidden Tip)Query Folding – Improve Performance for Relational DatabasesQUIZ: Test Your Knowledge – Understanding MKey Takeaways & Power Query M Formula Language SpecificationsSection 19: Working With List & Table FunctionsWhat You Learn in this SectionCreate a Calendar Table From Start to End DatePower Query Text Functions (Text.Contains, Text.Replace)How to Use List Functions in Custom ColumnMerge Values from Rows into One CellSection 20: Creating & Invoking Custom FunctionsWhat You Learn in this SectionUnpivot & Consolidate Data From Multiple Sheets (with Custom Function)Rank by Group (Nested Rank) with Power QueryRunning totals by Month (Invoking functions)How Far You’ve Come!Section 21: Flexible Importing of Data with the Help of M FunctionsWhat You Learn in this SectionBetter Collaboration: File Path as Parameter in Power QueryBetter Collaboration: File Path from a Cell (Formula.Firewall Error)Import from Folder: Mixed Letter Case Headers & Trailing SpacesImport All Sheets BUT Data Starts from Different RowsCHALLENGE: Combine Sheets & Get Name from Cell ValueSolution: Combine Sheets & Get Name from Cell ValueSection 22: PROJECT – Power Query and Power BIProject Overview of Power BI DashboardInstall Power BI Desktop & Quick OverviewUpload & Transform Data with Power Query in Power BISetup Data Model & Create Relationships in Power BICreate Your Dashboard & Reports within MinutesPublish Dashboard to Power BI ServiceKey Takeaways – Power Query & Power BISection 23: Final WordsWrapping up & What’s NextWhat You’ve Achieved! It doesn’t matter if you’re a beginner, either..You don’t need to be an advanced Excel userYou don’t need to understand “M Logic”You don’t need to be working with BIG dataYou don’t need to invest hours of learning Power Query before reaping the benefitsIn fact, I give you this challenge…Go through just a couple modules. Pick whichever ones jump out at you. Then, test your new skills at work. Give it an honest effort.I promise your eyes will open to all the hours you’ve been wasting.If you’re not impressed? We have a 14-day refund policy and will gladly return your enrollment fee.But I don’t think that will happen, because once you get the hang of Power Query, you’ll never look back. Nobody does.There are no reviews yet.Add a Review Cancel replyYou must be logged in to post a review.