Deprecated: Creation of dynamic property CF\WordPress\DataStore::$logger is deprecated in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-content/plugins/cloudflare/src/WordPress/DataStore.php on line 23
Deprecated: Creation of dynamic property CF\WordPress\Proxy::$pluginAPI is deprecated in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-content/plugins/cloudflare/src/WordPress/Proxy.php on line 31
Deprecated: file_get_contents(): Passing null to parameter #2 ($use_include_path) of type bool is deprecated in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-content/plugins/codelights-shortcodes-and-widgets/codelights.php on line 20
Warning: session_start(): Session cannot be started after headers have already been sent in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-content/plugins/automatic-video-posts/vendor/ternstyle/wordpress/src/class/wordpress.php on line 53
Warning: Cannot modify header information - headers already sent by (output started at /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-content/plugins/cloudflare/src/WordPress/DataStore.php:23) in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-includes/rest-api/class-wp-rest-server.php on line 1758
Warning: Cannot modify header information - headers already sent by (output started at /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-content/plugins/cloudflare/src/WordPress/DataStore.php:23) in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-includes/rest-api/class-wp-rest-server.php on line 1758
Warning: Cannot modify header information - headers already sent by (output started at /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-content/plugins/cloudflare/src/WordPress/DataStore.php:23) in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-includes/rest-api/class-wp-rest-server.php on line 1758
Warning: Cannot modify header information - headers already sent by (output started at /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-content/plugins/cloudflare/src/WordPress/DataStore.php:23) in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-includes/rest-api/class-wp-rest-server.php on line 1758
Warning: Cannot modify header information - headers already sent by (output started at /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-content/plugins/cloudflare/src/WordPress/DataStore.php:23) in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-includes/rest-api/class-wp-rest-server.php on line 1758
Warning: Cannot modify header information - headers already sent by (output started at /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-content/plugins/cloudflare/src/WordPress/DataStore.php:23) in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-includes/rest-api/class-wp-rest-server.php on line 1758
Deprecated: Creation of dynamic property ternplugin\youtube_video::$post is deprecated in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-content/plugins/automatic-video-posts/vendor/ternstyle/plugin/src/class/youtube_video.php on line 64
Warning: Cannot modify header information - headers already sent by (output started at /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-content/plugins/cloudflare/src/WordPress/DataStore.php:23) in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-includes/rest-api/class-wp-rest-server.php on line 1758
Warning: Cannot modify header information - headers already sent by (output started at /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-content/plugins/cloudflare/src/WordPress/DataStore.php:23) in /home1/cassanoc/public_html/bonbonsguide.com/wp/wp-includes/rest-api/class-wp-rest-server.php on line 1758
{"id":465,"date":"2017-12-11T10:39:19","date_gmt":"2017-12-11T17:39:19","guid":{"rendered":"https:\/\/bonbonsguide.com\/wp\/?p=465"},"modified":"2017-12-11T10:39:19","modified_gmt":"2017-12-11T17:39:19","slug":"excel-magic-trick-1470-power-query-get-transform-add-daily-rainfall-5-am-to-5-am-next-day","status":"publish","type":"post","link":"https:\/\/bonbonsguide.com\/wp\/blog\/2017\/12\/excel-magic-trick-1470-power-query-get-transform-add-daily-rainfall-5-am-to-5-am-next-day\/","title":{"rendered":"Excel Magic Trick 1470: Power Query (Get & Transform) Add Daily Rainfall 5 AM to 5 AM Next Day"},"content":{"rendered":"Download Files: Start Excel File: http:\/\/ift.tt\/2BT9DI6
\nDownload Files: Text Files for Import: http:\/\/ift.tt\/2ALvzpk
\nDownload Files: Finished Excel File: http:\/\/ift.tt\/2BUj1eu
\nEntire page with all Excel Files for All Videos: http:\/\/ift.tt\/1kSFWvs
\nLearn 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.
\nTopics:
\n1. (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.
\n2. (02:08) Look at Text Files
\n3. (02:46) Create a Parameter in Excel and Import it into Power Query
\n4. (06:03) Importing Text Files using From Folder feature
\n5. (09:39) Look at Custom Function that Power Query automatically creates
\n6. (11:35) Convert Dates to Decimal Numbers so we can use Column in a Formula
\n7. (12:42) Create Custom Column to subtract Start Time
\n8. (13:44) Remove Other Columns and Rearrange Columns
\n9. (14:15) Use Group By feature in Power Query and compare and contrast PivotTable in Excel to Group By in Power Query
\n10. (15:55) Load Report to Excel Sheet
\n11. (16:35) Change Parameter in Excel and refresh Power Query Report
\n12. (17:06) Load new Files to Folder and Refresh Power Query Report
\n13. (18:43) Verify that Excel Files NOT imported
\n14. (19:13) If Folder Path Chages, Change it in Source Step
\n15. (19:54) Summary<\/p>\n
Related Videos:
\nExcel Magic Trick 1469: Add Daily Rainfall 5 AM to 5 AM Next Day: Helper Column & PivotTable
\nExcel Magic Trick 1471: Add Daily Rainfall 5 AM to 5 AM Next Day: Array Formula (Most Automatic)<\/p>\n