Rick de Groot

Power Query has an ellipsis operator, and it can save your code from disaster. I promise, you'll learn something new in under 2 minutes. 📽️ : youtu.be/i842G9m6YOg?si… Enjoy!

👉 Watch the video here: youtu.be/L1zjwGe8Rrw?si… Sounds interesting? Grab your popcorn, hit play and let’s dive in! 🎬

You’ll also see what happens to metadata when values combine (spoiler: metadata doesn’t always play nice). And for a fun twist, we’ll wrap up with a cool use case—building a calendar table that relies heavily on metadata for important information.

Ever wondered what’s hiding behind your data? That’s metadata—tiny but powerful details that can change the way you work with queries. In today's video, I’ll show you how to set, remove, and actually use metadata to your advantage.

@DMaslyuk Awesome - instantly ordered. Thanks for the shoutout daniil!

If this were LinkedIn, I'd say I'm thrilled, excited, and delighted, because the Second Edition of Collect, Combine, and Transform Data Using Power Query in Power BI and Excel is available now! Read on for the backstory! Back in September 2019, Apress reached out to me and asked if I'd be interested in updating @cwebb_bi's book, Power Query for Power BI and Excel. From what I understood, Chris was too busy to revise it. I was definitely interested, in part because Chris would be the Technical Editor of the book. I submitted my proposal in October 2019, and I didn't hear back from Apress for a while. To Apress's credit, they didn't ghost me and instead told me in December 2019 that they had "decided to go with a different author for the revision" because "author came back with a more detailed proposal, especially around the specific changes that he planned to make." 2025 is around the corner, and I still don't see a second edition of Chris's book, unfortunately. Apparently, the contract was cancelled, because the chosen author never delivered. It's a bit of a shame that I didn't get to revise Chris's book because I would love to work with Chris, who's definitely a legend in my books. I reached out to Microsoft Press/Pearson in August 2022 and asked if they wanted to revise @gilra's book, which was already four years old by then. They said they would like the book to be revised and Gil was unavailable because he was working for Amazon at the time. However, Gil already had preferred authors for the Second Edition – again I wasn't chosen. In May 2023, I reached out to Microsoft Press again. I asked for an update, and I was told that the authors originally selected for the revision hadn't delivered, so the search for a new author continued. I was still interested in the project. This time I was selected, we signed the contract, and, unlike others before me, I actually delivered 😊 The result is the Second Edition that you see in the photo! There are three brand new chapters in the new edition, and most of the original content got a facelift. You can buy it from Microsoft Press Store, Amazon, and all other good book shops!

Either way, it's helpful to know how you can do this. In my latest article, you learn two simple methods to convert data types to strings. 📖 Article: gorilla.bi/power-query/co… 💡 What other challenges have you faced with data types in Power Query?

So, why would you need to return a data type as text? Here are two common scenarios: 👉 You want to create custom error messages that explain which type of value caused an issue. 👉 You’re documenting data structures and need an overview of the data types in a table.

Have you ever wondered how to convert a 'data type' into a text value? Power Query doesn't have a built-in function to make this simple.... But there’s a way around it. 🎯

What do you do to lessen your frustration when working with large tables in Power Query?

Boom—everything deselected, and you’re back to square one. You gotta select everything all over again. But what if there was an easier way? Good news, there is. Learn how to make column selection easy and quick: youtube.com/watch?v=prhcWT…

Struggling to select columns in a table with 90+ columns? Here's a method that will change your workflow. When working with massive tables in Power Query, picking the right columns can feel like a nightmare. For example, imagine a date table with over 90 columns.

@DMaslyuk Interesting read Daniil, thanks for adding your perspective to it. I'm not sure I fully comprehend what the situation for the local time is. My experience with the Ireland refresh was slightly different, which is what I shared before. I may have to look at this again later.. ;-)

My first blog post in almost two years (and probably the longest ever): What is #PowerQuery's time zone? The answer isn't as simple as you may think, I assure you 😊 Read more: xxlbi.com/blog/power-que…

@ilya_sb1 Didn’t know that, I may have to look into this!

@Rickmaurinus Old Bulgarian calendar is perfect. Was around longer than this civilization. It has always perfectly symmetrical months, weeks. Ex. 1st of any month is a Monday, etc.

What if I told you there's a calendar that makes financial reporting more comparable? Say hello to the 445 calendar. Unlike the traditional calendar with months that vary from 28 to 31 days, the 445 calendar splits the year into 52 weeks, organized into four quarters.

In my latest post, I walk you through the essential column calculations for the 445 calendar. Plus, I’ll show you how the same query can be adapted for the 445, 454, and 544 calendars.

Each quarter is broken down into three periods: the first two periods have 4 weeks each, and the third has 5 weeks. Curious about how to create and implement this in Power Query? Article: gorilla.bi/power-query/44…

When you group your data in Power Query, it hardcodes columns and data types. This can lead to errors if your data changes. Here's how you can make your queries dynamic youtu.be/ijpS0JQx0ko?si… I wish I knew this technique years ago! 😋

@TalebKabbara Why do these spammy sites link to you. What is their incentive? Are these competitors? I find it hard to believe the competition is that desperate. So it makes me wonder what the intention is with these spam sites

This is the same site that I disavowed 3,226 links 4 days ago.

Dynamic Group By Operations in Power Query M from @Rickmaurinus buff.ly/3xngLiB #PowerQuery