Download File:
Start File: http://ift.tt/2tB6Zp7
Zipped Folder file Text Files; http://ift.tt/2tI91nF
Finished File: http://ift.tt/2t6W9Uh
Finished Data Model File For Last Example: http://ift.tt/2tI0d0J
Entire page with all Excel Files for All Videos: http://ift.tt/1kSFWvs
Excel Accounting Seminar: 2017 WAATP Tacoma Accounting Conferences at La Quinta Inn, July 12
This is the second video in a series of two videos.
This video will teach you about how to use Power Query and PivotTables to clean, transform data and make summary PivotTable Reports. Take multiple Text files and import them into a single table which can be used for PivotTable reports and can be updated easily when new data arrives.
Topics in Video:
(00:10) Introduction to video topics and Excel files and how to navigate large workbook and video
(01:19) Overview of all Power Query & PivotTable steps in the video, including looking at Text Files that we need to import.
38. (07:05) Power Query To Import and Append Multiple Text Files with Sales Data. See how to use the From Folder feature to import many Text Files into a Single Proper Data Set thatr you can then use for PivotTable Reports.
39. (14:26) Power Query To Clean Data. Clean imported data before it is loaded to an Excel Sheet or the Data Model.
40. (17:30) Power Query To Merge Lookup Tables With Sales Table. See how to use the Combine, Merge, Left Outer Merge Feature to replace the VLOOKUP Function.
41. (22:50) PivotTable Basics and Monthly Profit Change and % Change Report using Show Values As feature and the PivotTables Group by Month feature.
42. (33:43) Build Cross Tabulated Report With Slicer and learn how to use the “Show Report Filter Page” option to create many PivotTables, each on a new sheet with a single click.
43. (39:56) Gross Profit Report, including a Formula in a PivotTable (Calculated Field)
44. (44:00) Add New Data and Update Reports
45. (46:18) Excel 2013 or 2016 Data Model to Reduce File Size Dramatically
(50:24) Conclusion & Summary
Event Sponsored by:
Puget Sound/Tacoma Chapter WAATP
Vice President
Darlene Sondergaard
Location:
La Quinta Inn
1425 E 27th St,
Tacoma, WA 98421
Event Details:
Wednesday, July 12 at 8 AM
Tags: Excel, Microsoft Excel, Excel 2016, Highline College, Mike Girvin, excelisfun, Excel Magic Trick, Excel For Accountants, Excel for Accounting, WAATP Excel, WAATP Excel seminar, Power Query, Get & Transform, PivotTables, Pivot Tables, Power Query in Accounting, PivotTables in Accounting, Clean Data in Excel, Import Multiple Text Files into Excel, Power Query to Import Multiple Text Files, Transform Data in Power Query, Merge Tables, Combine Tables, Left Outer Merge, Replace VLOOKUP with Merge Table, Gross Profit Pivot Table Report, Cross Tabulated Report, PivotTable, Monthly Revenue Report, Basics of PivotTables, Pivot Table Basics, Power Query Basics, Introduction to Power Query