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
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
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
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
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
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
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
excelguru.ca
What would you do when faced with the following scenario? 🤔
“I have multiple Excel workbooks which include two worksheets (A and B) which are formatted differently. I need to import and combine these but am not sure how.”

Our student wasn’t sure, so they brought this to an Ask Ken Anything session.
Building a Custom Function In an “Ask Ken Anything” session, Ken Puls helped a student manually build a custom function for importing and combing two worksheets that were formatted differently. It even included a sample transform to easily update the query in the future! (Screenshot of Power Query showing the 2 custom functions highlighted in the Query listing, and the following function in the formula bar: =Table.AddColumn(#"Changed Type", "Details", each if [Worksheet] = "1" ) (Skillwave Training logo)
October 21, 2025 at 4:49 PM
excelguru.ca
Our office mascot wishes a Happy #SpreadsheetDay to all of our fellow #Excel enthusiasts! This year feels extra special because of Excel's 40th birthday. 🎉

Spreadsheet Day was started in 2010 by Debra Dalgleish to commemorate the day VisiCalc was released in 1979. Learn more at buff.ly/8NrZFjZ
October 17, 2025 at 4:23 PM
excelguru.ca
I love hearing from happy students! 😁
In my #Excel Fundamentals Academy at @skillwave.training, you’ll learn all the core skills needed by a #DataAnalyst, including #ExcelFormulasAndFunctions, #PivotTables, #DataVisualization, and #PowerQuery. Level up your Excel skills, go to skw-t.com/excelfun
Testimonial "Great content overall, topped with some solid gold nuggets! I also love Ken's teaching style and rather dry sense of humour. Last but not least, I posted a question which was responded to in no time. Not only that, Ken went above and beyond to have it resolved. I was absolutely blown away by the level of service!" Mark S. (5 stars) Excel Fundamentals Academy (Caricature of Ken Puls) Developed and delivered by Microsoft MVP and Chartered Professional Accountant, Ken Puls (Skillwave Training logo)
October 16, 2025 at 7:27 PM
excelguru.ca
🔎QuerySleuth is a must-have for every #PowerQuery user. Developed based on a real business need, we believe this form will add magic to your ability to trace query dependencies and precedents. And it does it all without locking your #Excel UI! Learn more: mkyt.ca/querysleuth

#DataAnalyticsTools
Monkey Tools Build better data models, faster Developed by Excelguru (Image of a cyborg monkey with its fist up for a fist bump) QuerySleuth TreeView allows you to easily browse your queries. And you can quickly flip between showing precedents (the default view), dependents, or turn the tracking off entirely if needed. Plus, when looking at the M code of a query, any precedent queries are highlighted right in the code. (Screenshot of the TreeView in QuerySleuth with the various queries highlighted by colour code) monkektools.ca
October 15, 2025 at 4:15 PM
excelguru.ca
Hey newsletter subscribers, time to check your inbox as the latest edition has been sent!📬
Not subscribed? Sign up at xlguru.ca/newsletter for what's new in #Excel and/or #PowerBI, content from our experts, and info on courses, events, and specials. Plus, you'll get our free 'DIY BI' eBook series!📚
"News from the Data Monkey" Brought to you by Excelguru and Skillwave (Image of a cyborg monkey in a crouched superhero pose) October 14, 2025 In this issue: • From the AKA Sessions • What's New in Excel • Recent Meet-up Recording • Upcoming Courses and Conferences • Available Training Options Today is the first day of Excel Virtually Global, and there is a great line-up of speakers and topics over the next 3 days. Ken Puls is part of a panel discussion with good friend, Bill Jelen starting at 1800 UTC / 1100 PDT. There's still time to register, and most sessions will be recorded. Go to https://excelvirtuallyglobal.com for details, and then make a donation to the charity of your choice.
October 14, 2025 at 9:17 PM
excelguru.ca
In a recent AKA session, a student asked the following:
“I have to prepare a report quarterly, and every quarter the data I am receiving is sent to me in a different format. It takes me forever to clean it up and get it into the format for analysis… What can I do to make this easier?”
Struggling to learn on your own? (Image of a woman sitting in front of a laptop with her head held in her hands and a desperate look on her face) (Skillwave Training logo)
October 13, 2025 at 4:47 PM
excelguru.ca
With just a click, the Parameter Table & Function feature of our Monkey Tools add-in will insert the Parameter table and fnGetParameter function into your workbook. It even places the #Excel file path formula in the cell! 🙌 Learn more about it here: mkyt.ca/param-tbl

#DataModeling #PowerQuery
Monkey Tools Build better data models, faster Developed by Excelguru (Image of a cyborg monkey with its fist held up for a fist bump) Parameter Table and Functions (Screenshot of Power Query showing the inserted Parameter table and fnGetParameter function) If you're a fan of the fnGetParameter function (as per Chapter 17 of Ken's 'Master Your Data' book), you'll love this Monkey! monkeytools.ca
October 8, 2025 at 5:05 PM
excelguru.ca
One week to go until #Excel Virtually Global 2025! 🌏💻 It's the 10th year for this virtual conference, which runs Oct 14-16. I'm looking forward to being part of a panel discussion with my good friend, Bill Jelen (MrExcel.com) on that first day. Get the full details at excelvirtuallyglobal.com
October 7, 2025 at 4:38 PM
excelguru.ca
Have you ever worked on something in a program, confident in your path, only to be greeted by a weird error message? as they were trying to combine a folder of JSON files using #PowerQuery in #Excel. 🤪 Doing everything exactly as they should resulted in the error message shown in the image here.
Why am I getting this error? student was trying to combine a folder of JSON files using Power Query in Excel. They were doing everything exactly as they should but got the error message shown here: (Screenshot of the Power Query formula bar containing the following formula: =Record.ToTable(Source) Error message below reads, "We cannot convert a value of type List to type Record.") They were able to solve it, but Ken Puls provided a sanity check at our last ‘Ask Ken Anything’ call. He confirmed that the student wasn’t doing anything wrong… Power Query was! PS: For the record, the formula that Power Query should have provided was: =Table.FromRecords(Source) (Skillwave Training logo)
October 6, 2025 at 4:12 PM
excelguru.ca
Less than a month until Bulgaria Excel Days 2025! 📆 I can't wait to be back in Sofia for this amazing event, featuring inspirational speakers, advanced masterclasses, and opportunities to gather new knowledge from leading #Excel experts. You can still register at: exceldays.itraining.bg/en/home-en/
Ken Puls onstage speaking at Bulgaria Excel Days 2024
October 1, 2025 at 7:10 PM
excelguru.ca
The Monkey Tools ribbon was designed with one goal in mind: to make you more efficient when building queries and/or data models. Learn how to customize or personalize the Monkey Tools tab at mkyt.ca/customtab

#Excel #PowerQuery #DataModeling #BusinessIntelligence #DataAnalyticsTools #DataAnalysis
Monkey Tools Build better data models, faster Developed by Excelguru (Image of a cyborg monkey with it's fist up for a fist bump) Customize the Monkey Tools Tab To add a custom group to our ribbon tab, right click any ribbon tab and choose Customize the Ribbon. This will launch an Excel Options screen, where you will need to: 1. Select the Monkey Tools tab on the right 2. Click New Group 3. Select the group 4. Click Rename... 5. Enter the name 6. Click OK Then you can add as many commands to the group as you like! (Screenshot of the Excel Options screen with the above-numbered items highlighted) monkeytools.ca
October 1, 2025 at 4:43 PM
excelguru.ca
The inaugural edition of our new combined @Skillwave.Training and Excelguru newsletter, "News from the Data Monkey", went out earlier! 🐵📧
Learn more about our products, courses, and events, what's new in #PowerBI, recent meet-up recordings, and more. Not subscribed? Sign up at xlguru.ca/newsletter
Screenshot of the "News from the Data Monkey" newsletter, September 2025 edition.
September 30, 2025 at 7:46 PM