Awesome trick!
09.03.2026 17:08
👍 1
🔁 0
💬 0
📌 0
Awesome trick!
A student asked why a #PivotTable counts 3 Lines of Credit as “used” when one of them has a zero balance and shouldn’t be counted. 🤷🏾 And do you need #PowerPivot and #DAX to fix it?
Nope! @excelguru.ca shows it takes just simple data-prep adjustment in #PowerQuery:
Want to master data cleaning and transformation?
Want to master #DataCleaning and #DataTransformation, and automate your #BusinessIntelligence? Our #PowerQuery Academy has helped thousands of #DataAnalysts in diverse fields to use Power Query and save hours of time and manual effort.
🎯Get started today with our FREE intro course: skw-t.com/pqfun
This little tip works across #MicrosoftWindows applications and even across computers if you set it up that way. Change your copy/paste game forever by holding onto your copy history. So much more functional that the classic CTRL + C, CTRL + V cycle! 👍
www.youtube.com/watch?v=TFgE...
#ExcelTips
Image of a a cyborg monkey sitting thoughtfully while resting its chin on its hand. A thought bubble is next to it that says: Let's see, it's fall back, spring ahead... Ah nuts, one less hour of sleep this weekend!" (Skillwave Training logo)
![(Skillwave Training logo)
Power Query Recipes
Master Your Data in Excel and Power BI
Pattern Difficulty = Easy
Convert UTC DateTime to Local DateTime Recipe
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.x to lock in a time zone.
Course Subscribers: Click the icons to see this recipe in action! (Icon for Power Query Academy video)
For more data patterns check out our courses at www.skillwave.training
Recipe: 60.910.2306](https://cdn.bsky.app/img/feed_thumbnail/plain/did:plc:ihdq52obivvf6sq3lnalnwrv/bafkreiechihojcilzdc7tw7gf5wtu3jt6uvcyla4fpmgaboneyeqnnjtwa@jpeg)
(Skillwave Training logo) Power Query Recipes Master Your Data in Excel and Power BI Pattern Difficulty = Easy Convert UTC DateTime to Local DateTime Recipe 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.x to lock in a time zone. Course Subscribers: Click the icons to see this recipe in action! (Icon for Power Query Academy video) For more data patterns check out our courses at www.skillwave.training Recipe: 60.910.2306
It's that time of year again... daylight savings begins in North America on Sunday, March 8. Luckily, our #PowerQuery recipe for converting a UTC DateTime to a Local DateTime is daylight savings aware. Get the full recipe collection at skw-t.com/pqrecipes
#Excel #PowerBI #DataAnalytics
estimonial "I found it really interesting to see how the general ledger came together dynamically... The most exciting part was learning how easy it is to integrate transactional data into a data model using a chart of accounts as the primary key. It was only possible because you had set up all the data models and DAX measurements." Andrew K. (5 stars) Building Financial Statements in Excel with Power Pivot (Caricature of Ken Puls) Developed and delivered by Microsoft MVP and Chartered Professional Accountant, Ken Puls (Skillwave logo) skillwave.training
If you use #Excel for #FinancialModeling, you'll want to check out our Building #FinancialStatements with #PowerPivot course by FCMA and FCPA, @excelguru.ca. See why students give it an average rating of 4.9 / 5 ⭐
Register today at skw-t.com/buildfs
Questions like these are exactly why our AKA sessions exist – to help you discover the critical settings and practices that aren't obvious but make huge differences. Both our #SelfServiceBI (skw-t.com/ssbi) and #Excel Fundamentals (skw-t.com/excelfun) Academies include access, or go to skw-t.com/aka
It’s easy – just go to Options > Global > Data Load and uncheck the “Auto date/time for new files” setting. Its quick, its easy and it can help prevent massive performance issues down the road.
🧭Takeaway: Sometimes the biggest performance gains come from turning things off, not adding more.
The fix? Either mark your calendar table as a proper date table, or disable it by unchecking the “Auto date/time” setting under Options > Current File > Data Load. Both will remove the hidden calendar tables immediately. 📆
In fact, Ken always turns this off when installing Power BI on a new PC.
The Hidden Tables Bloating Your Power BI Files (Image of a woman sitting at a desk with a laptop, rubbing her eyes under her glasses in frustration) (Skillwave Training logo)
During an AKA session, @excelguru.ca revealed a critical #PowerBI setting that most users never know about: auto date/time intelligence. PBI creates hidden date tables for every date column in your model, which can bloat your file size. One file dropped 90% in size just by turning this off! 🤯
🧵
Last Chance (Skillwave Training logo) Love Your Data (Image of a cyborg monkey in a superhero style crouch) Save 20% on all Skillwave courses* with code: LOVE20 *Excludes 'Ask Ken Anything’ Offer ends at midnight on February 28, 2026 skillwave.training
Unlock the full potential of #BusinessIntelligence and make data-driven decisions. 💛 Today’s your last chance to save 20% when you use code LOVE20 to register in any course. Don’t miss out, go to skw-t.com/lovedata
(Ends at midnight, excludes AKA subscriptions, can't be combined w/ other offers.)
#PowerQuery is a data connection technology that enables you to discover, connect, combine, and refine data sources for #DataAnalysis. It adds ETL capability to #Excel and #PowerBI. 💪
Start learning how it can save you a ton of time and manual effort with our FREE intro course: skw-t.com/pqfun
Love Your Data Save 20% on all our courses* with this code: LOVE20 Valid February 14-28, 2026 (Skillwave Training logo) skillwave.training *Excludes 'Ask Ken Anything’
We'll help you kickstart your #BusinessIntelligence and #DataAnalytics journey!
❤️ Use code LOVE20 to save 20% when you register in any of our #Excel and #PowerBI training at skw-t.com/lovedata
(Valid until Feb 28, excludes Ask Ken Anything subscriptions. Can't be combined with other offers.)
Okay, maybe you knew the tip from last week's Monkey Shorts on how to zoom in the #DAX font size. But do you know how to do it in the #PowerQuery editor? 🤔 Today, @excelguru.ca shows how to to control the zoom level and font size in the PQ editor: www.youtube.com/watch?v=NThh...
#Excel #PowerBI
Love Your Data Save 20% on all our courses* with this code: LOVE20 Valid February 14-28, 2026 (Skillwave Training logo) skillwave.training *Excludes 'Ask Ken Anything’
Kickstart your #BusinessIntelligence and #DataAnalytics journey!
❤️ Use code LOVE20 to save 20% when you register in any of our #Excel and #PowerBI training. See our course at skw-t.com/lovedata
(Offer valid until Feb 28, 2026 and excludes AKA sessions. Cannot be combined with any other offers.)
It's perfect for #FinancialStatements and custom reporting where standard aggregation doesn't work.
🧭Takeaway: Master your filter context, and you can control every single cell in your #Excel reports.
The AKA sessions shine in moments like these, where you need to go beyond basic #ExcelFormulas. 💡
@excelguru.ca walked through using a VAR and SWITCH statement to detect filter context. By checking if a field "has one value" versus "multiple," you can write different #DAX logic for individual rows vs subtotals vs grand totals. This pattern gives incredibly granular control over every line.
When Grand Totals Don't Add Up (On Purpose) A student had a question about controlling what appears in PivotTable totals. They needed the grand total to display a specific count rather than summing the values above. Ken Puls walked through using a VAR and SWITCH statement to detect filter context. By checking if a field "has one value" versus "multiple," you can write different DAX logic for individual rows versus subtotals versus grand totals. Takeaway: Master your filter context, and you can control every single cell in your reports. (Skillwave Training logo)
A question came up in an AKA session about controlling what appears in #PivotTable totals. The student had room costs where individual rows showed correctly, but they needed the grand total to display a specific count (88 rooms) rather than summing the values above it (which would give 176). 🧵
News from the Data Monkey Brought to you by Excelguru and Skillwave (Image of a cyborg monkey in a superhero type crouch) February 24, 2026 In This Issue: - New Video on Excel TRIMRANGE() & the Dot Operator - From the AKA Sessions - Latest Meet-up Recording and Next Event Information - Upcoming Courses and Conferences - Available Training Options Another month has flown by with Ken hosting and presenting at various Power BI and Modern Excel Meet-ups, helping students in the live 'Ask Ken Anything' sessions, and leading his first course of the year for Chartered Professional Accountants of BC. This week, Ken is excited to be delivering two days of live, on-site training for a private client. He LOVES being in person with students! However, if you can't join him live, we have the next best thing: 20% off all of our on-demand video courses (excluding 'Ask Ken Anything' subscriptions). All you have to do is register by February 28, 2026 and apply this code at checkout: LOVE20
The latest edition of our newsletter is hitting inboxes now! 📨
If you'd like to stay up-to-date on the latest content from our experts, what’s new in #Excel and #PowerBI, info on courses, events, promos, and more, subscribe at skw-t.com/news
📗 You'll also our free #PowerQuery eBook.
Love Your Data Save 20% on all our business intelligence courses* with this code: LOVE20 Go to skillwave.training/learn Valid February 14-28, 2026 *Excludes 'Ask Ken Anything’ (Skillwave Training logo)
Now’s the time to transform your #DataAnalysis skills and supercharge your career!
🩷 Register today in any of our courses at skw-t.com/lovedata and save 20% with code LOVE20
(Offer valid until Feb 28, 2026, excludes the Ask Ken Anything sessions, and can't be combined with any other offers.)
Yes, Power Query is what adds ETL functionality to Excel and Power BI!
Ever extend a schedule in a dataset in #Excel and wonder, “Did my formula actually pick up the new rows?” 🤔
Here, @excelguru.ca explains how to use the TRIMRANGE() function and the DOT (.) operator to build formulas that automatically adjust to changing data ranges:
Love Your Data Save 20% on all our business intelligence courses* with this code: LOVE20 Go to skillwave.training/learn Valid February 14-28, 2026 *Excludes 'Ask Ken Anything’ (Skillwave Training logo)
Now’s the time to transform your #DataAnalysis skills and supercharge your career!
🩷 Register today in any of our courses at skw-t.com/lovedata and save 20% with code LOVE20
(Offer valid until Feb 28, excludes subscriptions to the Ask Ken Anything sessions, and can't be combined with other offers.)
What does ETL mean?
ETL stands for:
1️⃣ Extract
2️⃣ Transform
3️⃣ Load
It's a data integration used to combine data from multiple sources into a single dataset for #DataAnalysis.
#PowerQuery adds ETL to #Excel and #PowerBI, and will save you hours of time and effort. Get started with our FREE intro course: skw-t.com/pqfun
(Skillwave Training logo) skillwave.training Love Your Data Save 20% on all our business intelligence courses* with this code: LOVE20 - Building Financial Statements in Excel with Power Pivot - Self Service BI Academy for Excel and Power BI - Supercharge Power BI Online - Power Query Academy Intermediate to Advanced - Excel Fundamentals Academy -Power BI for the Business Analyst Online - Data Visualization: The Art and Science - Dimensional Modeling for the Excel and Power BI Pro - PL-300 Exam Preparation for Power BI - Demystifying DAX Intermediate to Advanced *Excludes 'Ask Ken Anything’ Valid February 14-28, 2026
Ready to revolutionize your #DataAnalytics game?
💜 Enroll in any of our #Excel and #PowerBI courses by February 28, 2026, and use code LOVE20 to save 20%. Go to skw-t.com/lovedata
(Offer excludes subscriptions to the Ask Ken Anything sessions.)
#DataAnalysis #DAX #PowerQuery #DataModeling #DataViz
Like @excelguru.ca, the Data Monkey needs corrective eyewear in order to be able to read properly. 👓 In this Monkey Shorts, we'll demonstrate how to quickly scale the #DAX font size (in both #Excel and #PowerBI), making it easier to author/review your DAX formulas:
(Skillwave Training logo) Testimonial "I love the flow and layout of the course. It fit logically and built on past elements quite seamlessly." Mike M. (5 stars) Supercharge Power BI Online (Caricature of Matt Allington with the covers of his 'Supercharge Excel' and 'Supercharge Power BI' books next to him) Registration includes digital editions of both of Matt Allington’s best-selling books. skillwave.training
Woot woot! Another 5-star review for our introductory #DAX course, Supercharge #PowerBI. 🌟
This structured, hands-on training will help you learn to write DAX and gain more powerful insights from your data. Learn more at skw-t.com/supercharge
♥️ Enroll by Feb 28 with this code to save 20%: LOVE20
(Skillwave Training logo) skillwave.training Love Your Data Save 20% on all our business intelligence courses* with this code: LOVE20 - Building Financial Statements in Excel with Power Pivot - Self Service BI Academy for Excel and Power BI - Supercharge Power BI Online - Power Query Academy Intermediate to Advanced - Excel Fundamentals Academy -Power BI for the Business Analyst Online - Data Visualization: The Art and Science - Dimensional Modeling for the Excel and Power BI Pro - PL-300 Exam Preparation for Power BI - Demystifying DAX Intermediate to Advanced *Excludes 'Ask Ken Anything’ Valid February 14-28, 2026
Ready to revolutionize your #DataAnalytics game?
💜 Enroll in any of our #Excel and #PowerBI training by February 28, 2026, and use code LOVE20 to save 20%. Go to skw-t.com/lovedata
(Offer excludes subscriptions to the Ask Ken Anything sessions.)
#BusinessIntelligence #PowerQuery #DAX #DataModeling
🧭 Takeaway: The best solutions are often the simplest ones – if you know where to look.
Get practical solutions to real world challenges! Our #SelfServiceBI (skw-t.com/ssbi) and Excel Fundamentals (skw-t.com/excelfun) Academies give you session access, or purchase just the AKAs at skw-t.com/aka
It's that simple! While 0 is a number that #Excel counts, "null" is empty and gets ignored. This single replacement step fixed the entire counting problem instantly – no complex DAX needed. ⚡
Sometimes you don't need a complex solution; you just need someone who's seen the problem before.