Ask Ken Anything Get more than just a course – get access to an expert who will help you build real world solutions. The Ask Ken Anything (AKA) sessions are live, interactive coaching calls that are held twice each…

Go to at skw-t.com/aka, or get access included with our #Excel Fundamentals (skw-t.com/excelfun) and #SelfServiceBI (skw-t.com/ssbi) Academies.

#DataPreparation #DataAnalysis #PowerBI

🧭Takeaway: Understanding why something is slow is the first step to making it fast.

The AKA calls are where you bring performance puzzles like this and leave with real answers. If you work with Power Query and want to build faster, smarter #BusinessIntelligence solutions, come join us.

The parameterized version is slow bc the function refers back to the original “fileslist” query. For every single table being processed, PQ re-reads the entire folder listing to find the file it needs. 🤯 The custom fx approach passes in each table in directly, evaluating the “fileslist” query once.

They built a version using a parameterized sample query (the same approach Excel generates when you hit "Combine Files") and a second version using a custom function passed directly to the data table. The custom function version was dramatically faster.⏳

@excelguru.ca explained the root cause...

(Image of a woman sitting at a long office desk looking intently at her computer monitors with her hand on the mouse.) (Skillwave Training logo) Why is my Power Query Solution So Slow??

A question came up in a recent Ask Ken Anything (AKA) session that many #PowerQuery users will immediately recognize: "I built two versions of a file-combining solution, and one is ridiculously slow - why?" 😭
The student had a folder full of #Excel files and needed to consolidate everything. 🧵

Vote Now! Card #1: 3rd Edition Global Excel Awards Finalist - Training Provider of the Year Ken Puls (Skillwave Training) Vote for Me Card #2: 3rd Edition Global Excel Awards Finalist - Best Microsoft Excel Add-in Monkey Tools Vote for Me Voting closes April 30, 2026

As you may have heard, both @excelguru.ca and his Monkey Tools add-in are finalists in the 2026 Global #Excel Awards! 🙌
If you're a fan of Ken's training and/or #MonkeyTools, cast a vote in the following categories:
✅Training Provider of the Year
✅Best Excel Add-in
Vote now at

Good to know!

Dynamic Expand Collapse Messages in Excel - Monkey Shorts Episode 020 In this episode of Monkey Shorts, Ken shows a quick reporting trick he likes to use in complex workbooks. It allows you to collapse a range, but provide a message which only shows up while the range…

In this video, @excelguru.ca shows a quick reporting trick he likes to use in complex #Excel workbooks. It allows you to collapse a range, but provide a message which only shows up while the range is collapsed, inviting the reader to open it and see the data. 🤓

Master Your Data in Excel & Power BI Pattern Difficulty Scale: No Code (Image of a cyborg monkey standing with its arms crossed) Pivoting Stacked Data Recipe Add a columns for transaction and line IDs Add Column -> Index Column -> From 0 Select [Index] -> Add Column-> Standard -> Integer-Divide* Select [Index] -> Transform -> Standard -> Modulo* Pivot the data Select [Index] -> Transform -> Pivot Column Set the Values field to your data column Open the Advanced option and “Don’t Aggregate” Right click the [Integer-Division] column -> Remove Rename the newly created columns Set your data types * Modulo and Integer Division always target the number representing the 1st row of the 2nd transaction (Images of a sample stacked TXT data and the pivoted output table) For more data patterns check out our courses at www.skillwave.training Recipe 20.120.2112

Say you have a table or TXT file where all the data is in a single column, but you need a table with each data point in a separate column on the SAME row. ✂️ Instead of manually cutting and pasting each data point into a new cell, #PowerQuery can do it for you!
Get the full set at skw-t.com/pqrecipes

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…

🧭Takeaway: If you're creating data, use Excel. If you're consuming data, use PBI. That loop where the 2 tools feed each other is where the real power lies.
The AKAs are where you get practical guidance, and come with #SelfServiceBI (skw-t.com/ssbi) & Excel Fundamentals (skw-t.com/excelfun) Academies

Ken recommended using the "Analyze in Excel" feature to pull historical actuals from the Power BI #DataModel into Excel, build granular forecasts by department or product line, and store the resulting budget files in a SharePoint folder. Power BI can then read directly from that folder on refresh.

Forecasting is not a Power BI job. It is an #Excel job.
👁️‍🗨️ Power BI is a consumption tool - great for visuals, row-level security, and sharing.
⚒ Excel is a creation tool - ideal for the flexible, line-by-line modeling that real budgeting and forecasting requires.

Budgeting/Forecasting with Excel and PBI Data Source(s) -> PBI Model with RLS-> Reports Connect to model via Analyze in Excel and build forecast via formula -> Budget Working Copy Workbooks shared with key parties for update/edit/review -> Working Copies Folder -> Approved Version(s) Approved Versions copied to restricted SharePoint folder -> Budgets Folder (SharePoint) Budgets re-ingested into PBI model -> PBI Model with RLS

An AKA participant wanted to know how to build a more accurate sales forecast in #PowerBI, grounded in historical data trends rather than arbitrary percentage increases decided by committee.
@excelguru.ca's answer reframed the question in a way that many data professionals will find liberating.🧵

Your #1 resource for online training on Power BI, Power Query, Power Pivot, and Excel (Skillwave Training logo) (Image of a woman sitting at a desk working on a laptop)

We know that there are other learning resources out there. 💻
Our promise is that the material at Skillwave will be the best learning resources created by the best instructors in the business. 🚀 Skyrocket your #BusinessIntelligence and #DataAnalytics skills - view courses at skillwave.training/learn

See what our students are saying! "I liked the in depth demonstrations on how the program is designed to work." T.R. "I loved the recipes developed for every day data problems in business." Elizabeth B. Power Query Academy - Intermediate to Advanced skillwave.training (Skillwave Training logo)

See why students love our flagship #PowerQuery course! 🥰
We'll help you unlock the true power of your data with Power Query in #Excel and #PowerBI! Say goodbye to hours of manual #DataManipulation and hello to quick #DataInsights and informed decisions. Join us today: skw-t.com/pqa

Using Excel while the Power Query editor is open - Monkey Shorts Episode 019 YouTube video by Skillwave Training

It's no secret that the #PowerQuery editor locks your #Excel instance while it's open. This means that you can't change or select a different worksheet while using PQ. In this video, @excelguru.ca will give you some alternate methods of interacting with Excel: www.youtube.com/watch?v=bKBe...

Show Blank Values in Excel PivotTables Have you ever filtered a PivotTable and noticed that dates with no sales suddenly disappear? One of Ken’s students recently asked this exact question during an Ask Ken Anything session in the Self…

Ever filtered an #Excel #PivotTable and noticed that dates with no sales suddenly disappear? 😟
A student asked @excelguru.ca: "Do I need to write a complicated #DAX measure to keep empty dates visible in my report?"
The good news: you don’t! 😀
Here's what you do:

News From the Data Monkey Brought to you by Excelguru and Skillwave Training (Image of a cyborg monkey in a superhero type crouch) March 24, 2026 In This Issue: • New Video to Show Blank Values in Excel PivotTables • From the AKA Sessions • What's New in Power BI • Latest Meet-up Recording and Next Event Information • Upcoming Courses and Conferences • Available Training Options The finalists for this year's Global Excel Awards have been announced and Ken's super proud to be included in 2 categories: o Ken Puls for Training Provider of the Year o Monkey Tools for Best Excel Add-in It's a real honour to be recognized along with these other great trainers and add-ins. A big thank you to Global Excel Summit for once again running these awards, and to everyone who submitted nominations! Public voting is now open until April 30, 2026 - learn more about all of the categories and finalists, and cast your vote here: https://globalexcelsummit.typeform.com/to/thGGWX91

Our latest newsletter is hitting inboxes right now! 📧
If you'd like to stay up-to-date on what's new in #PowerBI and #Excel, content from our experts, and information on courses, events, and promotions, sign up at skw-t.com/news. We'll also send you our free #PowerQuery eBook. 📘

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…

The AKA sessions are exactly the kind of space where you can bring a real-world challenge like this and get a step-by-step walkthrough. Access is included with our #SelfServiceBI (skw-t.com/ssbi) or Excel Fundamentals (skw-t.com/excelfun) Academies, or join the AKAs on their own at skw-t.com/aka.

Make your chart dynamic and always highlight the true min/max of what is visible by using iterator functions like MINX and MAXX plus ALL or ALLSELECTED to carefully control what context is being evaluated.

🧭Takeaway: The best charts do not just show data - they tell the story of where you stand.

The student had already built a clever Excel chart using three overlapping series at 100% to highlight high and low values in different colors. The challenge was writing the DAX measures to make that same chart work dynamically in Power Pivot, especially when filters were applied.

When Power Pivot Gets the Highlighter Treatment To keep your PivotChart dynamic and always highlight the true min and max of what is visible, use iterator functions like MINX and MAXX paired with ALL or ALLSELECTED to carefully control what context is being evaluated. (Image of a column chart with 4 columns. The min column is highlighted red, the max column is highlighted green, and the other columns are left as grey.) (Skillwave Training logo)

In our last Ask Ken Anything (AKA) session, one of our students asked a fantastic question: how do you replicate a dynamic chart highlight in #Excel - one that visually flags the minimum, maximum, and neutral data points - when working inside #PowerPivot with #DAX measures? 📊
🧵

At the core of every BI solution is the Data Model. But do you really know how to work with is properly? Dimensional Modeling for the Excel and Power BI Pro Start Learning (Image of a many setting in front of a laptop looking up and to the left thoughtfully with his chin in his hand) (Skillwave Training logo)

Unlock the power of #DimensionalModeling by learning the right way to build solid and scalable Data Models in #Excel and #PowerBI. 💪 This on-demand training brings together #PowerQuery and the #DataModel for powerful #BusinessIntelligence.
➡️Enroll today at skw-t.com/modeling

#DataModeling

Use Emojis in Power Query Step Names - Monkey Shorts Episode 018 Level up your Power Query documentation game by adding Emojis and info icons to your Power Query steps.

Level up your #PowerQuery documentation game by adding Emojis and info icons to your Power Query steps. 😎 @excelguru.ca and the Data Monkey will show you how in this latest episode of Monkey Shorts! www.youtube.com/watch?v=PazQ...

#Excel #PowerBI #DataAnalyticsTools

An Introduction to DAX as a Query Language Level: Intermediate I have written quite a few articles that contain embedded references to DAX Queries and DAX Table functions in the past.  Today I am writing an article that introduces the DAX…

Do you know what a query language is? 🤔 It's a programming language that allows you to send a request to a database and then receive data back in the form of a table. This article by Matt Allington introduces the #DAX query language in #PowerBI and #Excel:

We couldn't agree more, Power Query is an absolute gamechanger when working with data!

What is Business Intelligence in the 21st century?

Modern #BusinessIntelligence is all about empowering users to access, visualize, and explore data intuitively and collaboratively. Our trainers draw on their real-world experience to help you get the most from your data, share insights, and make better decisions: skw-t.com/courses

Testimonial Having three separate ways to create charts, from a static sheet, to a data model in Excel or Power BI, was what I liked most about the course. Andrew K. (5 stars) The Art and Science of Data Visualization Meet the Trainers (headshot of each below): - Data Viz Expert, Jon Schwabish - Excel Expert, Ken Puls - Power BI Expert, Matt Allington (Skillwave Training logo)

We LOVE hearing from happy students! 😁
Our #DataVisualization course will help you present data in a clear, engaging, and meaningful way. Master the art of #DataStorytelling and gain the skills to create better, more effective graphs, charts, and diagrams in #Excel and #PowerBI - skw-t.com/dataviz

Power Query Filtering Tips - Monkey Shorts Episode 017 It is pretty common to use the drop down arrows when filtering content in Power Query. But do you pay attention to what Power Query actually does when you do this? In this video Ken shares how you…

It is pretty common to use the drop down arrows 🔽 when filtering content in #PowerQuery. But do you pay attention to what PQ actually does when you do this? 🫣
@excelguru.ca shares how you can get burned, as well as how you can take granular control to set up filters: