Ken Puls’ Post

If you work with large data sets in #PowerQuery in either #Excel or #PowerBI, you'll know the pain that comes with waiting on query previews to refresh. 😫 In this Monkey Shorts video, I share my strategy to quickly flip between a smaller and larger data set so that you can develop with speed, and then flip the switch to apply your logic to your entire data set. 🧐 Check it out here: https://lnkd.in/gy_vY9J5

This is Part 3 of my series on using #PowerQuery to get data from OneDrive for Business, and then create a #PowerBI dashboard that reads from an #Excel file hosted in the cloud. In this installment, I look specifically at creating Power BI Card Visuals to highlight some key indicators that filter as we select other visuals on the dashboard: https://lnkd.in/gmUkc5vP #DataVisualization #DataAnalytics #BusinessIntelligence

An hour to go until our Vancouver #PowerBI and Modern #Excel meet-up gets underway! We're excited to have Rick de Groot presenting on custom comparer functions in #PowerQuery. While there are standard functions, such as Comparer.Ordinal and Comparer.OrdinalIgnoreCase, you can gain more control by creating your own comparison logic. 👍There's still time to RSVP and join us, go to: https://lnkd.in/gZQbkX3z (The session will also be recorded.)

Super pleased to receive an email from Microsoft today with my 18th consecutive MVP award. Amazingly, this year I've been awarded in 2 categories for the first time: M65 (Excel) and Data Platform (Power BI)! #MVPBuzz

We've just released a handy new resource for learning how to use our Monkey Tools #Excel add-in! All of my Using Monkey Tools videos on YouTube have been compiled into an on-demand course. It's available for FREE on our Skillwave Training learning platform. ▶️ 🎦 Along with the existing videos, you also get access to any new videos and the example files that I use in the demo's. Sign up today at https://mkyt.ca/course

Unfortunately, #PowerPivot doesn't have an "export to CSV" function. So what do you do if you have over 1M rows of data and you need to create a CSV for upload into another system? Or maybe you simply want to export the data on a regular basis to append during an import, thereby offloading the transformation overhead of non-transformed files? ⁉️ The good news is that Monkey Tools has a monkey for that. Choose the columns you want, right click to rename them, click Export and (optionally) go get a coffee while our monkey does the job for you. It really is just that easy! 💥 With added bonuses for pro users such as changing the delimiter, file output type and even pre/post pending the file names, you'll find this monkey super useful! Learn more about it here: https://lnkd.in/geS5PJMU

Today, I'm leading a course on #DimensionalModeling in #PowerPivot for Chartered Professional Accountants of British Columbia. One of the key concepts for #DataModeling is how your data tables relate to one another. ⭐️❄️💫 #Excel #BusinessIntelligence

Did you know that you can get the full #PowerQuery documentation for a function by just pressing the Enter key? You can - if you do it in the right place... In this episode of Monkey Shorts, I show you exactly where that "right place" is, to get you the fastest access to the documentation you're after: https://lnkd.in/gxubBg8f #BusinessIntelligence #DataAnalysis #Excel #PowerBI

Have you heard about the new "Semi Joins" available in #PowerQuery? They are similar to the Anti Joins in that they deliver what records match between 2 tables, but they don't bring over the values. To help explain the new Left and Right Semi Joins, I added 2 new recipe cards to my Power Query Recipes collection. 👩🍳 You can purchase your copy of the full collection on our Skillwave Training site: https://lnkd.in/gZMvqdKc 🎥 And if you're enrolled in Power Query Academy, #SelfServiceBI Academy, or #Excel Fundamentals Academy, you can see them in action with a new lesson that I added to each course!

Okay, let's be honest: SQL Dynamic Management Views (DMVs) aren't for everyone. But if you are in an area where you need to use them, you'll know that setting them up in #Excel to query #PowerPivot involves writing not only the #SQL code, but also a #VBA back-end to apply them. At least, it used to... 🐵With this monkey at your side, you'll find data extraction from the #DataModel via SQL DMVs is easy. Our DMV Sleuth builds the connection, and even provides you with a massive list of DMVs to query. All you need to do is customize the DMV to get the exact output you need! Learn more here: https://lnkd.in/gDRJ7wpP