Mastering Excel Power Query: Function.From Deep Dive

Excel's Power Query is an incredibly powerful tool for data manipulation and transformation. Often overlooked is the Function.From formula, a key element in extending Power Query's capabilities. This guide provides a comprehensive deep dive into Function.From, demystifying its syntax and illustrating its practical applications within Excel. Learn how to use this function to create custom, reusable tools that automate complex data tasks and dramatically improve your workflow efficiency.

What is Power Query in Excel?

Power Query, also known as 'Get & Transform Data', is a data transformation and data preparation engine available in Excel. It allows users to extract data from various sources, clean, transform, and load it into Excel. This tool provides a graphical interface for querying data from a variety of sources, then applying transformations to the data without writing code. While the interface is user friendly, Power Query is backed by a powerful scripting language called 'M' which enables advanced users to create more complex transformations. The function from is part of the M language.

Power Query empowers users to:

• Connect to diverse data sources: Import data from databases, web pages, text files, and more.
• Clean and transform data: Filter rows, remove columns, replace values, and perform various data cleansing operations.
• Shape data: Pivot, unpivot, group, and aggregate data to create the desired structure.
• Load data: Load transformed data into Excel worksheets or the Power Pivot data model.
• Automate data refresh: Define the steps once, then refresh the data with a single click to get the latest updates.

  This saves time and reduces the risk of errors when compared to performing these tasks manually.

Introduction to Function.From

The Function.From formula is a Power Query M language function that allows you to create new functions programmatically.

It effectively transforms a function that operates on a single value into a function capable of processing an entire list or table of values. This is particularly useful when you need to apply a custom transformation to multiple rows of data but the existing Power Query functions don't quite fit your needs.

Essentially, Function.From provides a mechanism to extend the functionality of Power Query by enabling you to define custom functions tailored to your specific data manipulation requirements.

By mastering this formula, you are better equipped to:

• Automate complex data transformations: Create custom functions to handle unique data processing tasks.
• Increase efficiency: Apply transformations to entire datasets with a single function call.
• Promote code reusability: Save and reuse custom functions across multiple queries and workbooks.
• Enhance data consistency: Ensure that transformations are applied consistently across all data.

Combining Data from Multiple Sources

One of the most powerful applications of Function.From is combining data from multiple sources. Imagine you have data spread across multiple tables and you need to combine it in a very specific way . Function.From lets you create a custom function that:

1. Pulls data from different sources.
2. Applies the specific logic you need.
3. Gives you the result you need.

For example, you might have a table of customer information and a table of order information. You can create a custom function that retrieves the customer's name and the total amount of their orders:

CombineData = Function.From(
 type function (CustomerID as number) as record,
 each let
 Customer = Table.SelectRows(Customers, each [CustomerID] = CustomerID)[First Name]{0},
 Orders = Table.SelectRows(Orders, each [CustomerID] = CustomerID),
 TotalAmount = List.Sum(Orders[Amount])
 in
 [CustomerName = Customer, TotalOrderAmount = TotalAmount]
)

Then, you can apply this CombineData function to a list of CustomerIDs:

CustomerIDs = {1, 2, 3, 4, 5};
CombinedDataList = List.Transform(CustomerIDs, CombineData)

The CombinedDataList will contain a list of records, each containing the customer's name and the total amount of their orders.

This approach provides flexibility and allows you to create highly customized data integration solutions.

Calculating Sales Tax

Let's say you have a list of prices and you need to calculate the sales tax for each price. The sales tax rate is 8%

. You can create a custom function to perform this calculation using Function.From:

SalesTax = Function.From(type function (number) as number, each _ * 0.08)

Then, you can apply this SalesTax function to a list of prices:

Prices = {10, 20, 30, 40, 50};
SalesTaxList = List.Transform(Prices, SalesTax)

The SalesTaxList will contain the sales tax amount for each price in the Prices list. Sales tax is 8 percent . You can then define the function type as a math function . The function as function would be multiplying by point zero eight to get that eight percent .

Converting Text to Uppercase

Suppose you have a column of text values that you need to convert to uppercase. You can create a custom function using Function.From:

ToUppercase = Function.From(type function (text) as text, Text.Upper)

Then, you can apply this ToUppercase function to a column of text values:

Table.TransformColumns(Source, {
{"Column1", ToUppercase}}
)

This will convert all text values in "Column1" to uppercase.

Performing Conditional Calculations

Consider a Scenario where you need to apply different calculations based on certain conditions. For example, if a value is greater than 100, you multiply it by 0.9; otherwise, you multiply it by 1.1. You can create a custom function using Function.From:

ConditionalCalculation = Function.From(type function (number) as number, each if _ > 100 then _ * 0.9 else _ * 1.1)

Then, you can apply this ConditionalCalculation function to a list of numbers:

Numbers = {50, 150, 75, 200};
CalculatedList = List.Transform(Numbers, ConditionalCalculation)

The CalculatedList will contain the results of the conditional calculation for each number in the Numbers list.