Author | Lightnews

Ken Puls, FCPA, MS MVP @excelguru.ca · 21h

#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

2

Ken Puls, FCPA, MS MVP @excelguru.ca · 1d

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

1 1 2

Ken Puls, FCPA, MS MVP @excelguru.ca · 1d

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

Ken Puls, FCPA, MS MVP @excelguru.ca · 2d

I LOVE hearing from happy students! ❤️

Skillwave Training @skillwave.training · 2d

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

1

Ken Puls, FCPA, MS MVP @excelguru.ca · 2d

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

1

Ken Puls, FCPA, MS MVP @excelguru.ca · 2d

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

1

Ken Puls, FCPA, MS MVP @excelguru.ca · 3d

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

Ken Puls, FCPA, MS MVP @excelguru.ca · 3d

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

Ken Puls, FCPA, MS MVP @excelguru.ca · 3d

Multiple members added their own strategies and provided demos, truly showcasing the power and passion of the AKA community. 🙌

Courses are good. Personalized help is better. Our #SelfServiceBI (skw-t.com/ssbi) and Excel Fundamentals (skw-t.com/excelfun) Academy students get help with real projects.

Self Service BI Academy

Prepare to change your company's reporting game forever! In the Self Service BI Academy, Ken Puls will teach you how to use Excel, Power Query, and Power BI together to build robust Self Service BI…

skw-t.com

Ken Puls, FCPA, MS MVP @excelguru.ca · 3d

Of course Excel’s Forecast Sheet function builds static on-time-use forecasts, but that wasn’t good enough for us… With a few modifications to the underlying formulas, we made it fully dynamic so that it would update upon a data model refresh, dynamically forecasting the next two years of sales! 🔥

1

Ken Puls, FCPA, MS MVP @excelguru.ca · 3d

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.

1

Reposted by Ken Puls, FCPA, MS MVP

Curtis Frye @curtisfrye.com · 4d

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

1 2

Ken Puls, FCPA, MS MVP @excelguru.ca · 4d

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)

1

Ken Puls, FCPA, MS MVP @excelguru.ca · 4d

Oh no, I didn't mean to make you feel left out. It's always great to see you!

1

Ken Puls, FCPA, MS MVP @excelguru.ca · 4d

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

Ken Puls, FCPA, MS MVP @excelguru.ca · 7d

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

2

Ken Puls, FCPA, MS MVP @excelguru.ca · 7d

#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

2

Ken Puls, FCPA, MS MVP @excelguru.ca · 8d

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.

1

Ken Puls, FCPA, MS MVP @excelguru.ca · 9d

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

1

Reposted by Ken Puls, FCPA, MS MVP

Curtis Frye @curtisfrye.com · 11d

For the UK and Europe crowd: This week's YouTube video shows you how to generate a list of unique random numbers from a range of values. I use a bit of a hack by generating more numbers than you need and taking the first n unique ones, but it runs quickly and is easy to understand.

Generate unique random numbers

Selecting a subset of values from a larger set, or generating a random order of elements in a set, used to be difficult in Excel. In this movie, I’ll show you how to use new functions such as…

youtu.be

1 1

Ken Puls, FCPA, MS MVP @excelguru.ca · 10d

Thanks for the shout out, Patrick!

1

Ken Puls, FCPA, MS MVP @excelguru.ca · 10d

Don’t know how to approach a challenge? That’s what our AKA sessions are all about! Our #SelfServiceBI (skw-t.com/ssbi) and #Excel Fundamentals (skw-t.com/excelfun) Academies give you access to the sessions, or purchase just the AKA access at skw-t.com/aka

Self Service BI Academy

Prepare to change your company's reporting game forever! In the Self Service BI Academy, Ken Puls will teach you how to use Excel, Power Query, and Power BI together to build robust Self Service BI…

skw-t.com

Ken Puls, FCPA, MS MVP @excelguru.ca · 10d

I showed how we can manually build a custom function in #PowerQuery – complete with a sample transform to update the query in future – for each individual sheet type. From there a little bit of conditional logic, and we’re off to the races! 🐎

#DataTransformation #PowerBI Excel

1 1

Ken Puls, FCPA, MS MVP @excelguru.ca · 10d

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.

1 1

Ken Puls, FCPA, MS MVP @excelguru.ca · 11d

🎥 The recording from last week's Vancouver #PowerBI and Modern #Excel meet-up is now available: youtu.be/JroBScMe7qw
Thanks to everyone who attended live, and to Joseph Yeates for starting things off with a look at what's new in Power BI. And I always enjoy talking about Monkey Tools. 😉

Exploring Monkey Tools | Ken Puls

SESSION OUTLINE: Joseph Yeates will start things off by showcasing some of the latest features in Power BI. Then, we'll explore Ken Puls' Monkey Tools Excel add-in (available at…

youtu.be

1