Excel Magic Trick 1470

Power Query (Get & Transform) Add Daily Rainfall 5 AM to 5 AM Next Day


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.
(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.
(02:08) Look at Text Files
(02:46) Create a Parameter in Excel and Import it into Power Query
(06:03) Importing Text Files using From Folder feature
(09:39) Look at Custom Function that Power Query automatically creates
(11:35) Convert Dates to Decimal Numbers so we can use Column in a Formula
(12:42) Create Custom Column to subtract Start Time
(13:44) Remove Other Columns and Rearrange Columns
(14:15) Use Group By feature in Power Query and compare and contrast PivotTable in Excel to Group By in Power Query
(15:55) Load Report to Excel Sheet
(16:35) Change Parameter in Excel and refresh Power Query Report
(17:06) Load new Files to Folder and Refresh Power Query Report
(18:43) Verify that Excel Files NOT imported
(19:13) If Folder Path Changes, Change it in Source Step
(19:54) Summary


Download “Excel Magic Trick 1469-1481” Excel-Magic-Trick-1469-1481.zip – Downloaded 197 times – 4 MB

Be the first to comment

Leave a Reply