Ken Puls, FCPA, MS MVP
LightNews LightNews
banner
excelguru.ca
Ken Puls, FCPA, MS MVP
@excelguru.ca
340 followers 64 following 580 posts
I teach people how to use Excel (https://skillwave.training), host an Excel website (https://excelguru.ca), build software (https://monkeytools.ca) and enjoy watching football (aka soccer).
Posts Replies Media Videos
excelguru.ca
A big thank you to Alison Pollecutt for presenting on the "Go To Special" tool in #Excel at last week's meet-up! 🙏 We learned how it can be used to select things in your workbook based on special criteria and help speed up things that you've been doing manually. Here's the recording:
The Simple Magic of "Go To Special" | Alison Pollecutt
“Go To Special” is a tool that you might easily miss amongst the many other options on the Excel ribbon. Let’s face it, what does “Go To Special” even mean? Well, spreadsheet enthusiast Alison…
www.youtube.com
November 10, 2025 at 7:06 PM
excelguru.ca
Ever wondered how to make a #PowerPivot-backed #PivotTable suppress rows full of zeros? A student asked that exact question in an AKA session, and turned it into a great learning moment for everyone! I showed a simple #DAX pattern to suppress zeros dynamically so only meaningful values remain. 🧵 1/3
Zero Means Nothing... Literally! Ever wondered how to make a PowerPivot-backed PivotTable suppress rows full of zeros? In our last Ask Ken Anything session, Ken Puls wowed the group with a simple DAX pattern to suppress zeros dynamically so only meaningful values remain. Screenshot of DAX formula: =VAR vAmount = SUM( Sales[Amount] ) VAR vResult = IF( vAmount = 0, BLAND(), vAmount) RETURN vResult (Skillwave Training logo)
November 10, 2025 at 5:22 PM
excelguru.ca
I wrapped up the week with a great day of #PowerQuery training for CPABC.
One of the best things about PQ is that if you have a report that you update regularly, you can set things up so that you just save the new data, refresh your query with a SINGLE CLICK, and your chart or visual is updated! 🔄📊
Power Query Update Options Individual Files Path 1: Save the new data file with a new name -> Open the Workbook -> Edit the Query -> Update the Source path to the new file -> Close the Power Query Editor -> Click Refresh -> Update Visuals (icons for PivotTable, bar chart, line chart, and pie chart) Path 2: Save the new data file over the original -> Open the Workbook -> Click Refresh (refresh icon) -> Update Visuals (icons for PivotTable, bar chart, line chart, and pie chart) The Benefits: Re-use your existing business intelligence reports and logic
November 7, 2025 at 11:44 PM
excelguru.ca
Have you ever wanted to add your favourite icons to the QAT (Quick Access Toolbar) in the #PowerQuery editor in #Excel or #PowerBI? 🤔 Wonder no more, as I'll show you how! Check out how I added the "Detect Data Types" button to the Power Query QAT:
Customize the Power Query QAT - Monkey Shorts Episode 049
Have you ever wanted to add your favourite icons to the QAT (Quick Access Toolbar) in the Power Query editor in Excel or Power BI? Wonder no more as Ken shows you how to do so! In this video, Ken…
www.youtube.com
November 7, 2025 at 5:11 PM
excelguru.ca
This is good news!
wynhopkins.bsky.social Wyn - Power BI & Excel @wynhopkins.bsky.social · 5d
Stuff all the AI announcements. This is the best news so far...

From SharePoint Folder is now available regardless of which subscription plan. Business Premium users rejoice!

Looks like a few other connectors have lit up too!

If you're on an M365 plan then check it out now

#Excel #PowerQuery
November 6, 2025 at 5:15 PM
excelguru.ca
Just an hour to go until today's Vancouver #PowerBI and Modern #Excel meet-up gets underway! Spreadsheet enthusiast Alison Pollecutt is willing to bet that there are things you're doing manually that "Go To Special" could help you do a lot quicker. 😲
Sign up here:
The Simple Magic of "Go To Special" | Alison Pollecutt, Thu, Nov 6, 2025, 9:00 AM | Meetup
**SESSION TRACK:** Excel **SESSION OUTLINE:** “Go To Special” is a tool that you might easily miss amongst the many other options on the Excel ribbon. Let’s face it, what
www.meetup.com
November 6, 2025 at 4:03 PM
excelguru.ca
When you first open Query Sleuth in our Monkey Tools #Excel Add-in, you’ll notice that your queries are colour coded with up to four different colours: 🟠🟡🔵🟢 This adds a bit of extra insight around the query load destinations. Learn more at mkyt.ca/querysleuth

#PowerQuery #DataAnalyticsTools
Monkey Tools Build better data models, faster Developed by Excelguru (Image of a cyborg monkey with its fist up for a fist bump) Query Sleuth Queries are colour coded to add a bit of extra insight. By default: - Yellow = Query is loaded as a "Connection Only" - Green = Query is loaded to the data model - Blue = Query is loaded to an Excel table - Orange = Query is loaded to both the data model and an Excel table (Screenshot of the Query Sleuth dialog with a listing of colour coded queries) monkeytools.ca
November 5, 2025 at 5:16 PM
excelguru.ca
A new student joined my last Ask Ken Anything session with a familiar #Excel headache: “Every month I download new CSVs from our billing system – and every month I spend hours cleaning them up.” 😭

I walked her through how #PowerQuery can automate every step in a single refreshable query.
Goodbye Manual Cleanup, Hello Power Query! (Image of a woman sitting at a desk in front of a laptop with a big grin on her face) (Skillwave Training logo)
November 4, 2025 at 5:06 PM
excelguru.ca
For local folks in Nanaimo, BC: I'm leading a FREE hands-on workshop at VI University on Dec 4. It's is designed for accounting and finance students, helping them build essential #Excel skills for real-world #FinancialAnalysis and #FinancialReporting. 📉 Sign up here: xlguru.ca/viu-excel-wk...
Microsoft Excel Interactive Workshop
Learn Excel with expert Ken Puls! Hands-on workshop for accounting students, build real financial statements & skills employers love.
www.eventbrite.ca
November 3, 2025 at 7:56 PM
excelguru.ca
#PowerQuery doesn't have a great concept of row context, making it very difficult to compare values across rows. In this episode of Monkey Shorts, I'll demonstrate a super-cool black magic technique that allows you to do exactly that.🧙
www.youtube.com/watch?v=zdNY...

#Excel #PowerBI #DataAnalysis
Compare vs Prior Row in Power Query - Monkey Shorts Episode 048
Power Query doesn't have a great concept of row context, making it very difficult to compare values across rows. In this week's episode of Monkey Shorts, Ken demonstrates a super-cool black magic…
www.youtube.com
October 31, 2025 at 4:13 PM
excelguru.ca
Check out what's new this month in #Excel, including these 2 features in the Windows version:
🟢 #PivotTables #SPILL! (similar to the experience in Dynamic Arrays)
🟢 Formula by Example (previously only in the Web version)
What's New in Excel (October 2025) | Microsoft Community Hub
This month, look for Agent Mode in Excel (Frontier) in the Tools menu of Copilot for Excel. Additionally, PivotTable #SPILL and accessibility assistant...
techcommunity.microsoft.com
October 30, 2025 at 6:46 PM
excelguru.ca
Join us on Nov 20 to learn how to manage the everyday chaos of supply chain spreadsheets with simple yet powerful #Excel tricks! Faraz Shaikh will share real-world tips used by planners and logistics pros to
🧹clean data,
🔎track stock, and
📊visualize key insights
Sign up here:
From Chaos to Control: Excel Tips for Every Supply Chain Ninja | Faraz Shaikh, Thu, Nov 20, 2025, 10:00 AM | Meetup
**SESSION TRACK:** Excel **SESSION OUTLINE:** Manage the everyday chaos of supply chain spreadsheets with simple yet powerful Excel tricks! In this fast-paced, practical s
www.meetup.com
October 30, 2025 at 4:31 PM
excelguru.ca
I LOVE hearing from happy students! ❤️
skillwave.training Skillwave Training @skillwave.training · 12d
Another happy student! ⭐️⭐️⭐️⭐️⭐️
Elevate your #FinancialReporting game with our advanced #Excel #PowerPivot course by CPA and FCMA, @excelguru.ca. Learn how to correctly lay out chart of accounts, build P&L measures, and generate Balance Sheet measures using #DAX. Enroll today at skw-t.com/buildfs
Testimonial "The course was clear, complete, and very useful." Pascal I. (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 Training logo) https://skillwave.training
October 29, 2025 at 10:51 PM
excelguru.ca
It was the final day of my in-person #SelfServiceBI Boot Camp in Munich. The first part of the day was focused on #DAX and Calendar Intelligence. 📅 We then finished off the training with building and sharing dashboards with #PowerBI, and how to choose when to use Power BI or #Excel. 💪
The Importance of a Calendar Dimension (Screenshot of the Data Model structure, highlighting the Transactions and Budgets tables and cross-referencing them to a monthly chart) Transactions[End of Month] cannot cross filter Budgets Filters cannot go against arrow direction Solution: Separate table to filter both Budgets & Transactions Which tool do you use and when? Use Case / Feature Set - Prototyping: Excel = Yes, Power BI = Yes - Structured (tabular) analysis: Excel = Yes, Power BI = Yes - Unstructured/ad-hoc analysis: Excel = Easy, Power BI = Difficult - Deep data analysis: Excel = Yes, Power BI = Difficult - Variety of visuals (tables, matrix, charts, etc..): Excel = Limited, Power BI = Excellent - Cross filtering of visuals: Excel = Limited, Power BI = Excellent - Drill down capability: Excel = Limited, Power BI = Excellent - Suitability for multi-page (single sheet) reports: Excel = Excellent, Power BI = Poor - Sharing control: Excel = Poor, Power BI = Excellent - Security: Excel = Mediocre, Power BI = Excellent
October 29, 2025 at 7:14 PM
excelguru.ca
The Basic Explicit Measure Monkey provides a simple interface to build basic #DAX measures without writing any code yourself. 😎 It also gives aggregation choices based on your data types, shows the DAX written, and lets you set default measure formats. Learn more: mkyt.ca/bem

#Excel #PowerQuery
Monkey Tools Build better data models, faster Developed by Excelguru (Image of a cyborg monkey with its fist up for a fist bump) Basic Explicit Measure Monkey This monkey gives you a wizard-like interface to build your DAX measures without code, providing you with aggregations that are valid for your column's data types. It is targeted at those who are new to DAX and need help with getting the syntax right. (Screenshot of the Create Basic Measures interface) monkeytools.ca
October 29, 2025 at 4:11 PM
excelguru.ca
The latest edition of our newsletter is out! 📧
Stay up-to-date on what's new in #PowerBI and #Excel, updates to Ken's Monkey Tools add-in, content from our experts, and info on specials, events, and courses - subscribe at xlguru.ca/newsletter
We'll also send you our free 'DIY BI' eBooks! 📘📗📒📙
News from the Data Monkey Brought to you by Excelguru and Skillwave (Image of a cyborg monkey in a superhero-style crouch) October 28, 2025 In this issue: • From the AKA Sessions • Latest Monkey Tools Updates • What's New in Power BI • Recent Meet-up Recording • Upcoming Courses and Conferences • Available Training Options
October 28, 2025 at 8:11 PM
excelguru.ca
Today is Day 2 of my live Munich #SelfServiceBI Boot Camp, where we are focusing on #PowerPivot and #DataModeling. One of the key takeaways is that Facts and Dimensions should always live in separate tables.
A Fact is something that can be aggregated! 🧮
A Dimension is how we slice our facts! 🔪
What is a Fact? Something that is aggregated - Could be values or text! Examples: - Sum of Amount - Count of Customers Easy to identify: - Live in the bottom right corner of the Pivot! (Screenshot of the Fields area of a PivotTable, with the bottom right quadrant labeled Values and highlighted with "It's a Fact!") What is a Dimension? How we slice our Facts Examples: - Account Group - Customer Class - Year, Month, Day Easy to identify - Row, Column or Filter Fields (not Values) - Slicers or Timelines (Screenshots of a slicer, a timeline, and the Filters, Columns, and Rows fields in a PivotTable highlighted with "Dimension". The Values fields in the PivotTable is highlighted with "It's a Fact!") NOTE: If it won’t be aggregated, then it’s a Dimension
October 28, 2025 at 6:49 PM
excelguru.ca
In an AKA session, I had a spontaneous question about budgeting and #FinancialForecasting techniques. That led us through several #Excel features including Goal Seek, Data tables and Excel’s built-in Forecast Sheet function, which resulted in a nice forecast sheet built against a #DataModel.
Dynamic Financial Forecasts In an ‘Ask Ken Anything’ session, Ken Puls and the attendees worked together to build a forecast sheet that is fully dynamic so that is would update upon a data model refresh, forecasting the next two years of sales! (Screenshot of chart showing sales figures from 2020-01-31 to 2021-12-31 and forecasted sales from 2022-01-31 to 2024-12-31) (Skillwave Training Logo)
October 28, 2025 at 4:08 PM
Reposted by Ken Puls, FCPA, MS MVP
curtisfrye.com
How many different customers, states, or countries have you received orders from in the past month? In this movie I'll show you how to combine Excel functions to get the answer. The technique is slightly different for numbers and text, so I'll show you both methods. youtu.be/nPx3NFGwfaw
Count unique values in a data set
YouTube video by Curt Frye
youtu.be
October 27, 2025 at 5:19 PM
excelguru.ca
Today was the Day 1 of my in-person #SelfServiceBI Boot Camp in Munich. 👨‍🏫 We began with an overview of the #BusinessIntelligence ecosystem, then reviewed #Excel #PivotTables, and wrapped up the day by beginning the dive into #PowerQuery.
Photo of a meeting room set up awaiting the attendees for Day 1 of Ken Puls' Self Service BI Boot Camp in Munich, Germany. There is a long table with chairs around, and a flip chart and screen at the far end of the room. Which Technology Gets Used Where? Image of 5 bubbles indicating the various stages of the business intelligence ecosystem. The bubbles arranged in a circle with arrows pointing from one bubble to the next and labels of which technology is used at each stage: - Get & Transform Data (Power Query) - Relate Tables into a “Model” (Power Pivot Data Model) - Generate Measures & Analytics (PivotTables and Formulas) - Create Visuals (Tables, Pivots, Charts & More...) - Share & Explore (Email, SharePoint & Power BI)
October 27, 2025 at 7:54 PM
excelguru.ca
Following up on the video I shared last week about how to "un-fill" in #PowerQuery, here is my original article about how to keep only the first instance and replace all subsequent repeating values with the "null" keyword: excelguru.ca/unfill-in-po...

#Excel #PowerBI #DataPreparation
Unfill in Power Query
Recently I received a question on how to Unfill in Power Query - remove all duplicate instances of a value, replacing them with null
excelguru.ca
October 27, 2025 at 4:32 PM
excelguru.ca
I had a great time today leading a masterclass as part of #BulgariaExcelDays on the awesomeness of #PowerQuery! If you spend time manually gathering, cleaning, and transforming data in #Excel, you know how tedious it can be. PQ will change your work forever in, saving you hours of time and effort. 😎
Photo taken from the back of the classroom for Ken's Power Query Masterclass for Bulgaria Excel Days 2025. Students are setting up their laptops on the tables and getting water bottles up front, where there is a screen with a slide saying "Master Your Data: Live Edition. A Skillwave Product with content from Excelguru". There is a man peaking into the photo frame in the lower left corner with fist pumping in the air and he's cheering. The Classic Excel Answer 5 Step Process: Import data > Make contiguous range > Sort > Filter and cull > Cleanup Drawbacks: - Tedious - Complicated - Have to re-live it every time Dealing with Data in Modern Excel (Icons grouped on the left representing various data types, including databases, websites, documents, file folders, and emails) --> Rich array of transformation tools in one place (Icons of various tools) Worksheet + One-click Refresh = Smiley Face
October 24, 2025 at 6:42 PM
excelguru.ca
#PowerQuery's fill ⬇️/fill ⬆️ feature is amazing but what if you need to "un-fill" a selection for some reason? There may not be an un-fill command in the UI, but that doesn't prevent us from doing it! In this episode of Monkey Shorts, I'll show you how: www.youtube.com/watch?v=yHwt...
#Excel #PowerBI
How to unfill in Power Query - Monkey Shorts Episode 047
Power Query's fill down/fill up feature is amazing, but what if you need to "un-fill" a selection for some reason? There may not be an un-fill command in the Power Query user interface, but that…
www.youtube.com
October 24, 2025 at 4:11 PM
excelguru.ca
I've been having an awesome time in Sofia this week for #BulgariaExcelDays! There's nothing better than connecting with friends in person, like Gasper Kamensek, Chandeep Chhabra and @exceloffthegrid.bsky.social. ☺️
Thank you to Boriana Petrova and Boris Loukanov for always putting on a great event. 🙏
Ken Puls and Gasper Kamensek at Bulgaria Excel Days 2025. Ken Puls and Chandeep Chhabra at Bulgaria Excel Days 2025. Ken Puls and Mark Proctor at Bulgaria Excel Days 2025.
October 23, 2025 at 6:00 PM
excelguru.ca
We're very proud of our Monkey Tools add-in, and love hearing from happy users! ❤️
Monkey Tools contains a set of tools to help you build better data models more quickly and follow #DataModeling best practices. Learn more about it at monkeytools.ca

#PowerQuery #DAX #DataAnalyticsTools #DataAnalysis
Monkey Tools Build better data models, faster Developed by Excelguru (Image of a cyborg monkey with its fist up for a fist bump) What do people think of Monkey Tools? "Monkey Tools is by far the best Excel Add-in available. Ken Puls has designed miracles for Power Query and Power BI. If you thought those 2 tools were great, just wait until you try them WITH Monkey Tools. You will never go back!" Sherry Barnes-Fox Try it for FREE with our 2-week Pro Trial! Go to: monkeytools.ca
October 22, 2025 at 4:24 PM