Excel Magic Trick 1351

Power Query: Merge Text & Excel Files, Custom Columns, Total Row & Slicer

DESCRIPTION

Using Power Query (Get & Transform), see how to Import & Merge Text & Excel Files, Clean Data, Add Helper Columns for Company Name and Revenue, and add a Total Row & Slicer for Power Query Report:
(00:16) Introduction and View of Final Report
(02:13) Import Text Files with from Folder option
(05:40) Clean Data with Split Feature
(06:20) Create Custom Columns (Helper Columns) for Company Name (if then else Function)
(08:50) Import Excel Table
(10:00) Merge Units Table and Price Table using Left Outer Join (like VLOOKUP in Excel or Relationships in Power Pivot)
(12:17) Create Custom Columns (Helper Columns) for Revenue (Number.Round function)
(13:55) Edit Query when a Field name is misspelled
(15:17) Group By Product & Company Name to get Total Revenue
(16:52) Load to Excel Sheet
(17:55) Add Totals Row
(18:34) Add Slicer
(19:26) Why we use Power Query
(19:48) Add new Text Files to Folder and Update (Magic of Power Query)
(20:53) Summary

WORKBOOKS

Download “Excel-Magic-Trick-1341-1360.zip” Excel-Magic-Trick-1341-1360.zip – Downloaded 211 times – 23 MB




Be the first to comment

Leave a Reply