Download File:
\nStart File: http:\/\/ift.tt\/2tB6Zp7
\nZipped Folder file Text Files; http:\/\/ift.tt\/2tI91nF
\nFinished File: http:\/\/ift.tt\/2t6W9Uh
\nFinished Data Model File For Last Example: http:\/\/ift.tt\/2tI0d0J
\nEntire page with all Excel Files for All Videos: http:\/\/ift.tt\/1kSFWvs
\nExcel Accounting Seminar: 2017 WAATP Tacoma Accounting Conferences at La Quinta Inn, July 12
\nThis is the second video in a series of two videos.
\nThis 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.
\nTopics in Video:
\n(00:10) Introduction to video topics and Excel files and how to navigate large workbook and video
\n(01:19) Overview of all Power Query & PivotTable steps in the video, including looking at Text Files that we need to import.
\n38. (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.
\n39. (14:26) Power Query To Clean Data. Clean imported data before it is loaded to an Excel Sheet or the Data Model.
\n40. (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.
\n41. (22:50) PivotTable Basics and Monthly Profit Change and % Change Report using Show Values As feature and the PivotTables Group by Month feature.
\n42. (33:43) Build Cross Tabulated Report With Slicer and learn how to use the \u201cShow Report Filter Page\u201d option to create many PivotTables, each on a new sheet with a single click.
\n43. (39:56) Gross Profit Report, including a Formula in a PivotTable (Calculated Field)
\n44. (44:00) Add New Data and Update Reports
\n45. (46:18) Excel 2013 or 2016 Data Model to Reduce File Size Dramatically
\n(50:24) Conclusion & Summary
\nEvent Sponsored by:
\nPuget Sound\/Tacoma Chapter WAATP
\nVice President
\nDarlene Sondergaard
\nLocation:
\nLa Quinta Inn
\n1425 E 27th St,
\nTacoma, WA 98421
\nEvent Details:
\nWednesday, July 12 at 8 AM
\nTags: 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<\/p>\n