Lambda functions in Power Query are a powerful feature introduced to make it easier to perform complex operations on data without needing to create multiple helper columns or complex nested functions. Let’s start with some basic concepts and then move on to a practical example. What is a Lambda Function? A Lambda function is essentially…
The Text.StartsWith() function in Power Query (M Language) is used to check if a text string starts with a specific substring. However, when you want to check if a text starts with any one of a range of values, you’ll need to slightly modify your approach because Text.StartsWith() natively handles only a single text value…
List.Transform function in Power Query (M language) is used to apply a specified transformation to each item in a list and produce a new list containing the results. It’s very similar to the concept of “map” in many programming languages, where you apply a function to each item in a collection. Basic Example of List.Tranform…
Welcome to the future of spreadsheet management! Whether you’re a seasoned Excel user or just starting to explore its vast capabilities, Microsoft’s Copilot is set to revolutionize the way you work. In this post, we’ll dive into how Copilot can significantly boost your productivity, allowing you to focus more on strategic business decisions rather than…
In many business scenarios, users need the ability to dynamically filter data based on their specific requirements. For instance, when working with sales data pulled from a SQL Server database into Excel, it can be extremely useful to let users define date ranges to view relevant data. Today, we’ll explore how you can enable this…
Character.FromNumber is a useful function in Power Query that allows you to convert a number to its corresponding character in the Unicode character set. Character.FromNumber Syntax: Character.FromNumber(number as number) as text Arguments: Return value: Example Character.FromNumber: To convert the number 65 to its corresponding character, which is ‘A’, you would use the following formula in…
Text.SplitAny is a Power Query function that allows you to split a text string into multiple parts based on one or more delimiters. This is useful when you have a text string that contains multiple values, such as a comma-separated list, and you need to extract each value individually. Syntax The Text.SplitAny function takes three…
Text.Trim power query function helps us to remove spaces from the beginning and end of the given string. It also helps to remove a particular character from both ends of the string. Syntax It takes two parameters. Text.Trim Power Query Example 1 Let’s take the following example: we have a couple of account numbers with…
Text.TrimEnd power query function helps us to remove spaces or any character from the end of the given string. It comes under the category of power query Text functions. It is a very helpful function for data cleaning. Syntax It takes two parameters. Text.TrimEnd Power Query Example We have some account numbers with additional spaces…
Text.TrimStart power query function helps us to remove the leading zeros from the given string. It comes under the category of Power query text functions. Syntax It takes two parameters. Text.TrimStart power query function Example Let’s take the following example in which we have some account numbers with leading zeros, and we want to remove…