XelPlus is a leading online education company, providing training courses for Excel, Power BI, Finance, and Google Sheets. XelPlus’ bestselling courses are popular among financial analysts, CFO’s, and business owners. Technology is changing fast. We help our members turn confusion into confidence with every skill learnt.
Power Query gave me the wrong number
|
Power Query lies to you. Not always. Sometimes. And it means well. It wants to get you results faster. But sometimes speed gets in the way of accuracy. I ran a query this week to get the latest stock count per product. Power Query gave me 49. The actual number was 8. No error message. No warning. Just the wrong answer. And this isn't just a stock count thing. It can happen any time you're trying to keep the latest record per group - latest order status, latest price update, latest anything. If you use Power Query for this, you should see this. Power Query Challenge Pack - available nowThis is actually one of the challenges in our just-released Power Query Challenge Pack. (There's a whole other way to solve it, so you can still challenge yourself.) 15 real-world problems. Messy data. No hand-holding. You work through it, get stuck, figure it out. Only then check your approach against a video solution. Every challenge came from real feedback. What professionals actually find hard at work. Duplicate handling, broken queries, PDFs, files that were built for presentation and not for analysis.
🤓 Geeky News🤖 From Copilot to Autopilot: the AI Agent that runs itselfMicrosoft just introduced something called Autopilots. Always-on agents that work in the background, take action on your behalf, and don't wait to be asked. Their first one is called Scout. It connects to Teams, Outlook, OneDrive, SharePoint, your calendar, your chats. It schedules meetings, blocks focus time, flags stalled decisions, preps your briefing materials. All without you prompting it. The pitch is that it learns how you work over time and gets more useful as it goes. Currently in private preview for a select group, with Frontier program access for early adopters. Whether you want an AI that knows your schedule, your priorities, and your contacts running quietly in the background all day is, of course, a personal decision. 👀 📧 Latest improvements in the New OutlookMicrosoft posted a roundup of 15 productivity features that have landed in the new Outlook for Windows over the past year. Most of them are small. A few are genuinely useful. The ones worth knowing:
If you've been putting off switching to the new Outlook, the gap is narrowing. I'll hold off for a while longer. 💻 Office 2019 for Mac stops working July 13thIf you're still on Office 2019 for Mac, the apps will switch to read-only mode on July 13th. You'll be able to open and print files, but not edit them or create new ones. Office 2019 for Windows is not affected. Options if you are affected: upgrade to a Microsoft 365 subscription, buy Office 2024 as a one-time purchase, or use the free Office web apps at office.com. 🤔 Did You Know?A new Copilot in Excel feature is looking promising. You can now personalize the default behavior and save those settings across workbooks. It's like custom instructions in ChatGPT, but for Excel. List your preferred functions and formatting styles. Copilot will remember, so you don't have to mention them in every prompt. 👏 From certified to actually using itNikoleta works in financial analysis. The kind of role where data doesn't arrive clean, reporting deadlines don't move, and "I'll figure it out later" isn't really an option. She just finished Master Excel Power Query: Beginner to Pro and posted this on LinkedIn: Congrats, Nikoleta. Certificate earned, and already being used at work. That's the order you want it in. See you next week, Leila When you're ready, here are some ways we can help: 🎓 Join 400,000+ members in our courses 📺 Get free tutorials on YouTube 👥 Train your whole team with our courses. Team pricing and progress tracking - reply for details. This newsletter contains affiliate links, which give us a small commission at no cost to you. Thank you for your support! |
Leila Gharani - XelPlus
XelPlus is a leading online education company, providing training courses for Excel, Power BI, Finance, and Google Sheets. XelPlus’ bestselling courses are popular among financial analysts, CFO’s, and business owners. Technology is changing fast. We help our members turn confusion into confidence with every skill learnt.
When I worked in finance, the reports I had to build almost always came from more than one sheet. A lot of copy-pasting. These days one formula can do that for you. It's called VSTACK. It stacks your sheets into one list. It stays live, works in Excel Online, and anyone who opens the file knows exactly what's happening. Most people who use VSTACK, use it to glue two ranges together, and think that's it. But that's barely scratching the surface. I put together my top 6 VSTACK tips in this new...
The most common way people mess up their own spreadsheets is a button sitting right there on the Home tab. Merge & Center. It's one of the first buttons anyone learns in Excel, and it does exactly what it says: centers a title, makes a header look tidy. The trouble starts the moment someone uses it inside the data instead of just the header row. Try to sort... yep, it doesn't work. Try to sum a column with a merged cell sitting in the middle of it. Column E, for example: Exactly. You'd have...
What do you do when your data goes wide? Picture a sales table with one row per rep, but then Sales_Jan, Units_Jan, Sales_Feb, Units_Feb, and so on, all the way to December. It just keeps growing wider with every month you add. Try to filter by month, or build a pivot table around it, and you're stuck. Month isn't a value in this table. It's baked into the column headers. You need a way to turn the wide into long so that it's ready for analysis. And there's a Python function that does exactly...