Start Excel File: [ Ссылка ]
Text Files for Import: [ Ссылка ]
Finished: [ Ссылка ]
Entire page with all Excel Files for All Videos: [ Ссылка ]
Learn about how to take a rainfall data set with Time-Date Stamps and add rain totals for each day and have the ability to change the definition of the 24 hour period by subtracting an Hour Increment. See how to use Power Query (Get & Transform) to import Multiple Text Files and create a Custom Column and use the Group By feature to create the final report. Then see that the report will update when we get new Text Files.
Topics:
1. (00:11) Introduction and description of process of Importing Text Files, Creating an Excel Parameter, Create Custom Column, Group By to create Report and Update after new Text Files arrive.
2. (02:08) Look at Text Files
3. (02:46) Create a Parameter in Excel and Import it into Power Query
4. (06:03) Importing Text Files using From Folder feature
5. (09:39) Look at Custom Function that Power Query automatically creates
6. (11:35) Convert Dates to Decimal Numbers so we can use Column in a Formula
7. (12:42) Create Custom Column to subtract Start Time
8. (13:44) Remove Other Columns and Rearrange Columns
9. (14:15) Use Group By feature in Power Query and compare and contrast PivotTable in Excel to Group By in Power Query
10. (15:55) Load Report to Excel Sheet
11. (16:35) Change Parameter in Excel and refresh Power Query Report
12. (17:06) Load new Files to Folder and Refresh Power Query Report
13. (18:43) Verify that Excel Files NOT imported
14. (19:13) If Folder Path Chages, Change it in Source Step
15. (19:54) Summary

Related Videos:
Excel Magic Trick 1469: Add Daily Rainfall 5 AM to 5 AM Next Day: Helper Column & PivotTable
Excel Magic Trick 1471: Add Daily Rainfall 5 AM to 5 AM Next Day: Array Formula (Most Automatic)

свернуть