Skillwave Training

ALT (Skillwave Training logo) Image of a cyborg monkey leaping into the air, cheering with its fist raised. A thought bubble next to the monkey says: "Let’s see, it’s fall back, spring ahead... YES, an extra hour of sleep this weekend!"

ALT (Skillwave Training logo) Power Query Recipe Card 60.910 Convert UTC DateTime to local DateTime Step 1: Add a UTC time zone (if your data has time zones, skip to Step 2) Right click the [Original] column -> Change Type -> DateTime Go to Add Column -> Custom Column Name: DateTimeLocal Formula: DateTime.AddZone([Original],0) Right click the [Original] column -> Remove Column Step 2: Convert the [DateTimeLocal] column to your local time zone Select the [DateTimeLocal] column -> Transform -> Time -> Local Time Right click the [DateTimeLocal] column -> Change Type -> <Date or DateTime as desired> NOTE: This recipe is Daylight Savings Time aware! If you do not want this volatility in your transformation, see Recipe 60.920 to lock in a time zone.

ALT News from the Data Monkey Brought to you by Excelguru and Skillwave (Image of a cyborg monkey in a superhero style crouch) October 27, 2026 In This Issue: - From the AKA Sessions - New video blog on loading web-hosted CSV files into Excel - What's New in Power BI - Latest Meet-up Recording and Next Event Information - Upcoming Courses and Conferences - Monkey Tools Updates - Available Training Options Ken had an awesome time last week in Sofia for this year's installment of Bulgaria Excel Days - Workshop Edition. There's nothing better than teaching a live, in person course, and hanging out with friends old and new. Now he just has to catch on everything waiting here for him back in the office!

ALT (M)agic Tricks for Data Wizards (Icon of a laptop with a magic wand on the screen and a wizard's hat draped over the top right corner) Tips & Tricks for Power Query in Excel and Power BI (Microsoft Excel and Power BI icons)

ALT Testimonial: "I love the speed, topics and the fact we learned and then [get a] demo." R.A. (5 stars) Dimensional Modeling for the Excel and Power BI Pro (Microsoft Excel and Power BI icons) Both the Excel and Power BI versions of the course are included with registration! (Skillwave Training logo)

ALT Is your company based in British Columbia? (Government of British Columbia and WorkBC logos) (Image of a woman holding a folder in one hand and reaching out to shake someone's hand with the other) Your company can receive up to $300,000 for skills training from the BC Government! (Microsoft Power BI and Excel icons) The BC Employer Training Grant provides funding to small, medium and large enterprises to support skills training. And both the on-demand and private courses we offer at Skillwave could qualify! Learn more about the program: https://skw-t.com/bctraining View our course catalog at skillwave.training (Skillwave Training logo)

ALT Now you can master data cleaning and transformation (Database icon with a sweeping broom on top) (Skillwave Training logo)

ALT Master Your Data in Excel & Power BI Pattern Difficulty: Lo Code Extract to New Column Based on Data in Next Row Recipe Preparation: Perform any possible preliminary cleanup Add [Index] column: Add Columns -> Index Column -> From 0 Add [Index.1] column: Add Columns -> Index Column -> From 1 Extract Data Into New Column: Go to Home -> Merge Queries As New -> Merge against itself Match [Index.1] (top) against [Index] (bottom) Expand the [Compare] column only, creating [Compare.1] Sort by the [Index] column (ascending) Create a Conditional Column: =if [Compare.1]= "pattern" then [Target] else null Right click the new column -> Fill -> up/down (as needed) Filter the [Compare.1] column to remove the pattern Remove the [Index], [Index.1] and [Compare.1] columns Finish cleanup (Screenshot of the Target and Compare columns in the Power Query Editor, with the Index cells highlighted to show the pattern to use in the Conditional Column formula.)

ALT Taming NaN Values in Your Source Data (Image of a woman sitting at a table working intently on a laptop) (Skillwave Training logo)

ALT News From the Data Monkey Brought to you by Excelguru and Skillwave October 13, 2026 In This Issue: - From the AKA Sessions - New video blog on understanding and fixing blanks in PivotTables - What's New in Excel - Latest Meet-up Recording and Next Event Information - Upcoming Courses and Conferences - Monkey Tools Updates - Available Training Options Yesterday was Thanksgiving here in Canada, and we just wanted to take a moment to say "thank you" to all of our newsletter subscribers, students, meet-up participants, and those who follow us on social media. YOU are the reason we do what we do, and we absolutely love the Excel and Power BI community! Every course enrollment, event registration, newsletter subscription, and like or comment on our social channels mean the world to us. Thank you for being a part of it!

ALT (M)agic Tricks for Data Wizards (Icon of a laptop with a magic wand on the screen and a wizard's hat draped over the top right corner) Tips and Tricks for Power Query in Excel and Power BI by Ken Puls and Miguel Escobar

ALT Screenshot of a PivotTable showing the Total Sales, Sales Prior Year, Change vs Prior Year, and % Change vs Prior Year for each of the 12 months of the selected year. Formatting has also been applied to help highlight increases vs decreases for each month.

ALT (Skillwave Training logo) Power Query Academy - Intermediate to Advanced: "All the classes were really good. Since they already come with the templates, it's easy to follow along." Mazureyk Temoteo (5 stars) Free with registration: Master Your Data with Excel and Power BI and Power Query Recipes

ALT Turning Durations Into Hours A student asked how to compare hours logged in a project management system against data from time-tracking software. Ken Puls’ solution used Power Query to clean and transform the data from both sources into a common data type, which could then be more easily compared! (Screenshot of the Power Query Editor displaying the initial time tracker data in the Screen Time columns as miniscule decimals. The Transform > Duration > Total Hours options from the ribbon are highlighted, which then displays the converted values in Screen Time column.) (Skillwave Training logo)

ALT At the core of every BI solution is the Data Model. But do you really know how to work with it properly? Dimensional Modeling for the Excel and Power BI Pro (Image of a man sitting in front of a laptop with a cup of coffee beside him. His left hand is on his chin and he's looking up and to the right.) Start Learning (Skillwave Training logo)