Microsoft MVP | Blog https://xitter.gq/t.co/qwx9m8DZhg | Become a Power Query Pro - Join 1000+ subscribers and transform your job: https://xitter.gq/t.co/DZi4ztUjD3
Utrecht, The Netherlands
Joined March 2018
- Tweets716
- Following195
- Followers1K
- Likes408
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=pmJH…
Enjoy!
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.
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.
👉 Watch the video here: youtu.be/L1zjwGe8Rrw?si=AiwT… Sounds interesting? Grab your popcorn, hit play and let’s dive in! 🎬
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. 🎯
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.
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/conve…
💡 What other challenges have you faced with data types in Power Query?
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.
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=prhcWT4O…
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.
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/445-c…
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=rlBj…
I wish I knew this technique years ago! 😋
Rick de Groot retweeted
Our next Vancouver #PowerBI and Modern #Excel meet-up is July 11 at 10am PDT. We'll be exploring custom comparer functions in #PowerQuery for things like grouping and sorting with Microsoft MVP @Rickmaurinus. 🤓
Sign up here: meetup.com/vancouver-power-b…
(Session will be recorded.)
Example:
Start Time: DateTime.FixedLocalNow
Perform Actions
End Time: DateTime.LocalNow
Calculate Duration: End Time - Start Time
To try the examples, check out:
powerquery.how/datetime-fixe…
This nuance might help you put datetime values to better use in your queries.
🧵 Power Query can capture the current datetime value, but it offers two different functions: DateTime.LocalNow and DateTime.FixedLocalNow.
What's the difference? ⬇️