Ken Puls, FCPA, MS MVP

2 Mar

Delivering expert training BY humans, FOR humans, Excelguru is dedicated to unlocking your team’s potential through: 🔹On-site workshops 🔹Online coached courses 🔹On-demand video courses Contact us today for training options at excelguru.ca #Excel #PowerBI #DataModeling #PowerQuery #DataAnalysis #DAX

15h

I'm back home after an amazing trip to Sofia for Bulgaria Excel Days, and wanted to share a few pictures. This is always my favourite event of the year, and I absolutely love teaching in person connecting with fellow #Excel enthusiasts!

One of our AKA participants wanted to know how to handle Not A Number (NaN) values in an exported CSV. Replacing them seemed to work, yet the query still reported errors that came up blank when clicked. 😠 I tested it live in #PowerQuery and found something interesting. 🧵

Right after promoting headers but before setting data types, replace "NaN" as text. If it has already been converted, change the column to text, replace the values, then convert it back. 🧭Takeaway: Clean up problem values before you set data types, not after.

skw-t.comAsk Ken AnythingGet 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 month. They’re designed to give…

This is the kind of hands-on troubleshooting AKA members get every session, using the messy data they actually work with. Subscribe today at skw-t.com/aka, or get access included with my #SelfServiceBI (skw-t.com/ssbi) or #Excel Fundamentals (skw-t.com/excelfun) Academies through @skillwave.training

Set the column to Decimal Number and NaN becomes a true "not a number" value; set it to Whole Number and it becomes an error. Replace Errors catches the error but not the NaN, and there's no built-in function to replace NaN. 😮 My fix – for any type of replacements – is to deal with it early.

youtube.comCustomize the Power Query QAT - Monkey Shorts Episode 049Have 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…

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! In this video, I'll show you how I added the "Detect Data Types" button to the Power Query QAT. 🛠 www.youtube.com/watch?v=fcdC...

meetup.comUser-Friendly Excel Reports That People Actually Want to Use | Laura Szepesi, Thu, Nov 26, 2026, 9:00 AM | Meetup**SESSION TRACK:** Excel **SESSION OUTLINE:** Joseph Yeates will start us off with some of the latest updates to Power BI. Then, we welcome our featured presenter, Microso

If you work with #Excel, chances are you deliver your files to someone else. On Nov 26, Microsoft MVP Laura Szepesi will present on design principles and clever workarounds you can use immediately for creating #ExcelReports that your users don’t just like, but love! 😍 www.meetup.com/vancouver-po...

Are you based in BC and looking to expand your #Excel and #PowerBI knowledge? Your organization could qualify for funding to help cover either in-person or on-demand training through our @skillwave.training learning platform! #BusinessIntelligence #DataAnalytics #DataModeling #DataVisualization

Skillwave Training@skillwave.training

We recently had a corporate client apply for the BC Employer Training Grant through workbc.ca, to help cover the cost of team training. 💵 It's available to organizations of all sizes and you could get up to $300,000! If you're based in British Columbia, check out the program: skw-t.com/bctraining

I've been having amazing time in Bulgaria!🤩 Today was the final session of my live #SelfServiceBI Boot Camp. The first part of the day was focused on #DAX and Calendar Intelligence. 📅 We finished off with building and sharing dashboards in #PowerBI, and how to choose when to use Power BI or #Excel.

A huge thank you to Boriana Petrova and the ITraining crew for organizing another great event! 🙏

Day 2 of my live #SelfServiceBI Boot Camp in Bulgaria focused on #PowerPivot and #DataModeling. One of the key takeaways is that Facts and Dimensions should always live in separate tables. Fact = something that can be aggregated! 🧮 Dimension = how we slice our facts! 🔪 #BusinessIntelligence #Excel

The QuerySleuth feature of our Monkey Tools #Excel Add-in is a must-have for every #PowerQuery user. It automatically indents and wraps your M code, and highlights the syntax by colour. 🤓 (However, you can adjust these options as you prefer.) Learn more at mkyt.ca/querysleuth #DataAnalyticsTools

youtu.beCalculate number of workdays between two datesYouTube video by Curt Frye

I love teaching in person! 💕 This week I'm leading my 3-day #SelfServiceBI Boot Camp as part of Bulgaria Excel Days. One of the first things covered today was the #BusinessIntelligence ecosystem and how we can use #Excel, #PowerQuery, #DAX, and #PowerBI together for robust reporting solutions.

Ken Puls, FCPA, MS MVP reposted

Curtis Frye

@curtisfrye.com

How much time do you actually have to finish a task at work? In this week's Techsoc movie, I'll show you how to calculate the number of workdays (excluding weekends and holidays) between two dates. youtu.be/9izTAE9HhDc

youtube.comExcel Like a Consultant (Pro Techniques that WOW Clients) | Wyn HopkinsIn this Vancouver Power BI & Modern Excel User Group meetup, Microsoft MVP Wyn Hopkins presents Excel Like a Consultant: Pro Techniques That Wow Clients, sharing practical tips and techniques for…

It was awesome having @wynhopkins.bsky.social present at last week's Vancouver #PowerBI and Modern Excel meet-up! He showed us how small improvements in workbook design, navigation, formatting, and automation can make a big difference to our #ExcelReports. 🤩 www.youtube.com/watch?v=9riY...

Happy #SpreadsheetDay day to our fellow #Excel and spreadsheet lovers! 💚 This comic from the always funny xkcd.com seemed fitting. (www.xkcd.com/1906) Spreadsheet Day was started by Debra Dalgleish in 2010 to commemorate the day the 1st computer spreadsheet program, VisiCalc, was released in 1979. 🤓

youtube.comCompare vs Prior Row in Power Query - Monkey Shorts Episode 048Power 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…

#PowerQuery 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, I demo a super-cool black magic technique that allows you to do exactly that. 😈 www.youtube.com/watch?v=zdNY... #Excel #PowerBI #DataAnalytics

meetup.comExcel Like a Consultant (Pro Techniques that WOW Clients) | Wyn Hopkins, Thu, Oct 15, 2026, 5:00 PM | Meetup**SESSION TRACK:** Excel & Power BI **SESSION OUTLINE:** First, our resident Power BI expert Joseph Yeates will show us what's new in Power BI. Then, Microsoft MVP Wyn Hop

Today's Vancouver #PowerBI and Modern #Excel meet-up gets underway at 5pm PT. @wynhopkins.bsky.social will lead a fast-paced, practical session demo'ing high-impact Excel tips that will help you take your game to the next level. ✨ Sign up here to join us: www.meetup.com/vancouver-po...

Ken Puls, FCPA, MS MVP reposted

matty

@mattio.bsky.social

This is wild - somebody did a deep dive into the real stories behind this famous XKCD: sheets.works/data-viz/hol...

Unsure what Monkey Tools can actually do for you? 🐵⚒️ I have a FREE Using Monkey Tools course available through our @Skillwave.Training site, where I explain all the different tools and features that will help with your #DataModeling. Enroll today at mkyt.ca/course #ExcelTools #DataAnalyticsTools

Yesterday was Thanksgiving here in Canada, and we've recovered from our turkey coma enough to send out the latest newsletter. 🦃 If you're not subscribed, sign up at xlguru.ca/newsletter for info on what's new in #Excel and #PowerBI, courses, events, specials, our free 'DIY BI' eBooks, and more!

youtube.comLoading Files from Web into ExcelLearn how to load CSV files from the web into Excel using Power Query. In this video, Ken shows two ways to connect to a file hosted online: using the specific From Text/CSV connector, or using the…

Learn how to load CSV files from the web into #Excel using #PowerQuery. 🌐 In this new video, I show 2 ways to connect to a file hosted online: 🟩 Using the specific From Text/CSV connector, or 🟩 Using the more general From Web connector. Check it out here: www.youtube.com/watch?v=ISWz...

This is technique useful when you want to connect directly to web-hosted CSV files instead of downloading them manually every time the data changes. Instead, it can be updated with a simple click on Refresh button! 💥

A question sent in for our recent 'Ask Ken Anything' (AKA) session tackled a common reporting headache: how do you compare hours logged in a project management system against data from time-tracking software? 🕒 I walked through a solution I built using #PowerQuery and #PowerPivot. 🧵

The time tracker stored durations, which turned into tiny decimals next to the project hours. Using Transform > Duration > Total Hours converted 5h 59m 50s into 5.997h, making the systems directly comparable. 🧭Takeaway: When time data won't compare cleanly, make sure you’re using a common data type!

skw-t.comSelf Service BI AcademyPrepare 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…

Bring your trickiest data puzzles to the AKA and see them solved step by step. Enroll in my #SelfServiceBI (skw-t.com/ssbi) or #Excel Fundamentals (skw-t.com/excelfun) Academies at @skillwave.training to gain access, or subscribe to just the AKAs at at skw-t.com/aka

1️⃣ Clean both sources. 2️⃣ Replace "0:00:00" entries with nulls so empty records wouldn't clutter the #PivotTable. 3️⃣ Build shared employee and calendar tables 4️⃣ Write measures to calculate the variance. The breakthrough was one small step...

youtube.comHow to unfill in Power Query - Monkey Shorts Episode 047Power 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…

#PowerQuery's fill down ⬇️ /fill up ⬆️ feature is amazing, but what if you need to "un-fill" a selection for some reason? There may be an un-fill command coming to the UI in #PowerBI, but it's going to be some time before we see it in #Excel. Here's my trick to do it: www.youtube.com/watch?v=yHwt...

meetup.comExcel as A Database and AI Agent Fuel | Richard Nero, Thu, Nov 5, 2026, 9:00 AM | Meetup**SESSION TRACK:** Excel **SESSION OUTLINE:** Many will argue Excel is not a database. Well, Microsoft MVP Richard Nero will show how in a few clicks you can turn Excel wo

Many will argue #Excel is NOT a database.🥊 But, at our Nov 5 Vancouver #PowerBI and Modern Excel meet-up, Richard Nero will show how in a few clicks you can turn Excel workbooks into cloud based #SQL databases that fuel #AIAgents. RSVP here: www.meetup.com/vancouver-po... (Session will be recorded.)

More and more, we’ve been taking advantage of the LET() function when writing our #ExcelFormulas. The Letify Monkey will convert an existing formula with just a few clicks, though you can also define the auto-generated variable names and configure various options. 🐒Learn more: mkyt.ca/letify

During our last AKA session, an attendee described a puzzling issue: queries driven by dates in worksheet cells kept showing old values after a refresh. Only clicking Refresh Preview, or saving, closing and reopening the file, seemed to fix it.🤔 My diagnosis? The data probably wasn't wrong at all. 🧵

💡My habit is simple: the first step in any debugging session is clicking Refresh Preview, then checking the loaded output before deciding something is broken. 🧭Takeaway: Refresh the preview before you trust what the Query Editor is showing you. #DataPreparation #DataAnalytics #BusinessIntelligence

A quirk like this can eat hours when you're on your own. In the AKAs, you get the reason behind it, so you can fix it with confidence and move on. Sign up at skw-t.com/aka, or get access for free with my #Excel Fundamentals (skw-t.com/excelfun) and #SelfServiceBI (skw-t.com/ssbi) Academies.

#PowerQuery caches preview values inside the file, so the Query Editor and hover cards can show stale results even after Refresh All has loaded fresh data to the worksheet. 🔄 It's even more likely if background data previews have been turned off to speed up large workbooks.

youtu.beExcel: Combine Multiple Rows into Separate Columns with Power QueryYouTube video by Mike Thomas

♥️#PowerQuery

Mike Thomas@thomasmike.bsky.social

New Excel Video...combining multiple rows for the same item into a single row with the related values placed into separate columns. The example I use is multiple customer comments for a set of products. For this video I used Power Query. youtu.be/5BKsdHsNtuo

Excel can be used for creating polished reports, dashboards, catalogs, heat maps, and more. 📉 At last week's Vancouver #PowerBI and Modern #Excel meet-up, Jakob Nielsen showed how his Flow add-in helps reduce repetitive layout, formatting, and report-building work. 🎥Watch here: youtu.be/nAl2ZyNz-9k

youtube.comNumber Grouped Rows in Power Query - Monkey Shorts Episode 046Also known as "number rows by partition" this is a super useful recipe to have in your Power Query arsenal. The basic goal is to provide each item in a group with a row number, restarting the…

AKA "number rows by partition" this is a useful recipe to have in your #PowerQuery arsenal. The basic goal is to provide each item in a group with a row number, restarting the numbering at row 1 when we move to the next group. 🔢 With the right recipe, it's very easy: www.youtube.com/watch?v=9ckP...

meetup.comStunning Reports. Smarter Workflows. | Jakob Nielsen, Thu, Oct 1, 2026, 9:00 AM | Meetup**SESSION TRACK:** Excel **SESSION OUTLINE:** We're excited to have Jakob Nielsen (a member of Microsoft's Excel design team) presenting the second in his series of 3 sess

You can still RSVP for today's Vancouver #PowerBI and Modern #Excel meet-up. Jakob Nielsen will be taking us for a deep dive into Flow, his Excel add-in that helps automate report layout and styling formats, keeping things consistent and polished. 🤩 Sign up: www.meetup.com/vancouver-po... #DataViz

We heard from European friends that they aren't fans of the N American week numbering systems. 📆 #PowerQuery has a “week number” function but no function to get ISO weeks. Well, our Monkey Tools #Excel add-in will inject a GetISOWeek function.✋ Learn how it works here: mkyt.ca/get-iso-week

techcommunity.microsoft.comWhat's New in Excel (September 2026) | Microsoft Community HubWelcome to the September 2026 edition of What's New in Excel!

Here's a recap of what's new in #Excel, including the ability to: 🟩 Store lists and arrays in a single cell 🟩 Name a table at the time of creation 🟩 Centre across selection from the new button in the Merge & Centre menu Read the full post: techcommunity.microsoft.com/blog/excelbl...

youtu.beWhy PivotTables Show Blanks — and How to Fix ThemIn this video, Ken explains the different types of blanks that can show up in PivotTables built from the Data Model. You’ll see why measure calculations can return blanks when no rows remain after…

In this new video, I show the different types of blanks that can show up in #PivotTables built from the #DataModel. 🙃 If you work with #Excel PivotTables, #PowerPivot, #DAX measures, or #PowerQuery, this will help you understand where blanks come from and how to deal with them: youtu.be/fnVUJmHUTwc

In the most recent AKA session, a student asked about a frustrating #DataVisualization problem: a carefully formatted combo #PivotChart looked perfect until new forecast data was added. One refresh later, the formatting was gone. 😠 I reproduced the issue and was upfront that there's no clean fix. 🧵

🧭Takeaway: If a PivotChart keeps breaking on refresh, change the foundation instead of fighting the formatting. That's what the AKA is for: real problems from your own files, worked through live with someone who will tell you honestly what works and what doesn't.

skw-t.comExcel Fundamentals AcademyIn the Excel Fundamentals Academy, you’ll learn all the core skills for the Excel analyst, beginning with a refresher on Excel formulas and functions (including the daunting VLOOKUP), and then moving…

My #Excel Fundamentals (skw-t.com/excelfun) and #SelfServiceBI (skw-t.com/ssbi) Academies at @skillwave.training include access to our semi-monthly AKA sessions, or join just the AKAs at skw-t.com/aka

PivotCharts built on the #DataModel are notoriously fragile when a refresh adds new series, because the chart loses track of what it's supposed to format. I offered practical workarounds: 🟢 Layer separate combo and line charts, OR 🟢 Try driving the chart from #DynamicArrays so it works off formulas.

youtube.comShift non-aligned Columns in Power Query - Monkey Shorts Episode 045In this week's episode of Monkey Shorts, Ken explores a recipe to fix non-aligned data issues. It all started with a PDF import where data shifted into different columns on different rows. It all…

In this Monkey Shorts, I explore a #PowerQuery trick to fix non-aligned data. It all started with a PDF import where data shifted into different columns on different rows.👎 It all ended with success, and a recipe to solve this challenge for others!👍https://www.youtube.com/watch?v=TC_gRjjCnm0 #Excel

Ken Puls, FCPA, MS MVP reposted

Ilgar Zarbaliyev

@ilgarz.bsky.social

Join us tonight for our Azerbaijan Data & AI Community meetup! 📅 25 September 2026 🕗 20:00 Baku time (GMT+4) Watch live on YouTube: youtube.com/live/hjRP2FQ... #PowerBI #MicrosoftFabric #ArtificialIntelligence #MCP #DataAnalytics #PowerBICommunity #AzerbaijanDataAI

youtube.comPower B(A)I – AI-driven Reporting with MCP, Custom AI Skills & Power BI BridgeYouTube video by Ilgar Zarbaliyev

What a great way to wrap up the week! Today, I'm leading some training for a private client, exploring the awesomeness of #PowerQuery. 😎 If you spend time manually gathering, cleaning, and transforming data, you know how tedious it can be. PQ will change your work forever in #Excel or #PowerBI!

techcommunity.microsoft.comPut multiple values in one cell with lists and arrays in Excel | Microsoft Community HubThroughout Excel's 40-year history, you've only been able to put one value per cell. In this announcement, we're excited to share how that's changing with...

Some big #Excel news today! 🥳 We can now insert lists into a single cell, where each item is separated with a comma (or semicolon). Even more, we can contain our #DynamicArray outputs in a single cell, instead of spilling them to the grid. Read more here: techcommunity.microsoft.com/blog/microso...