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.
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)

[DougHExcel]

Excel magic trick with PowerQuery! Thanks Mike.

[drsteele]

Thanks Mike, this is splendid! That drill-down is the easiest way to create a parameter. It looks like your weather data is millimeters of rain.

[RobMichaels]

Neat trick passing the 'Hour Required' parameter to the query. Thanks!

[marcelbeug]

Nice video again Mike!
One correction and one suggestion:At step 14 (change folder) not only the Source step in query RainFallFinishedReport must be changed, but also the Source step in query Sample File (as the steps from the main query were copied over).
My suggestion would be to output the Hours query to Excel (somewhere out of sight), and use conditional formatting to indicate if the queries must be refreshed after changing the Hours value in the original Excel table: if the 2 values differ, then the queries must be refreshed.

[karljolivet]

Great trick that I will apply for my work. You make the best videos

[SyedMuzammilMahasanShahi]

Thanks Mike for such a wonderful video.

[OzduSoleilDATA]

Very VERY nice! It's nice to know about that Drill-Down and use of the single number in calculations.
Also, I've seen that problem with the Existing Sheet grayed out. Same solution: put the result on a different sheet, then move it.

[mohamedchakroun]

I am keeping up the pace Mike :-) thanks

[marcopdenoordt]

Hey Mike, Again an awesome tutorial. Keep up the good work! Can you do a kind of Vlookup in powerquery with custom columns?

[johnborg]

Thanks Mike. One Question: After you filtered the File Extension to .txt, howcome you did not Remove other the other columns before you clicked on the Binery??

[cd-uxot]

Is power query available for Excel for Mac 2011? It's for a client not for me!

[mohdazeem]

Hello sir, Plz tell me a formula for space between Number and Alphabet only
for ex.
123ABC45
2155B100
but I want to changing in other column like as-
123 ABC 45
2155 B 100
Plz help me sir.

[ismailismaili]

U make me think that power Query is still has a lot to do in