How to Create and Modify Custom Functions in Power BI (or Excel)

Summary

This tutorial demonstrates how to create and use custom functions in Power Query to simplify and automate data transformations, especially when working with multiple files. The video walks through connecting to a folder of Excel files, applying basic transformations, and then creating a custom function that can be reused across all files. It covers how to define the function, apply it, and make further refinements using Power Query's Advanced Editor. The speaker emphasizes the value of custom functions in streamlining complex workflows and encourages users to explore the technique for more efficient data processing.

Takeaways

• 😀 Custom functions in Power Query simplify code and save time, especially when performing repetitive tasks across files.
• 😀 The native Power Query functionality allows creating functions from queries but is limited in selecting specific steps for function creation.
• 😀 Using custom functions is especially useful for transforming files, particularly when managing multiple similar files like Excel sheets.
• 😀 When combining files from a folder in Power Query, using the 'Combine and Transform' option is ideal for files with identical structures but can clutter the query window if there are discrepancies.
• 😀 A simpler approach is to use the folder connector and perform transformations manually to avoid unnecessary steps and clutter.
• 😀 The 'Remove Other Columns' step in Power Query places columns in the order they are clicked, which can be a helpful tip for efficient data preparation.
• 😀 Power Query allows you to drill down into the content of files (e.g., Excel) to extract and format data without overcomplicating the query structure.
• 😀 You can create a custom function by extracting steps from an existing query and wrapping them into a function, enabling easy reusability and transformation across multiple files.
• 😀 The process of turning a query into a function involves defining parameters and using the 'let' and 'in' clauses to set up the function logic.
• 😀 After creating a custom function, you can apply it to all files by using the 'Invoke Custom Function' step, streamlining transformations across multiple data sources.
• 😀 Custom functions allow for flexibility, letting you toggle back and forth between query steps and function transformations when making adjustments to the process.

Q & A

What is the main topic of the video?

The video focuses on creating and modifying custom functions in Power Query, a feature within Power BI, and provides a method to simplify repetitive tasks in data transformations across multiple files.

Why are custom functions helpful in Power Query?

Custom functions are helpful because they allow users to perform the same transformation repeatedly across multiple files or datasets, saving time and improving workflow efficiency. They simplify complex queries and reduce redundancy.

What is the issue with Power Query's native 'combine and transform' function?

The native 'combine and transform' function works well for files with identical structures, but it can cause errors and clutter when files have different sheet names, table names, or other structural variations.

How does the presenter recommend dealing with multiple files in Power Query?

The presenter recommends using a more flexible approach by manually transforming files in Power Query, keeping the query window clean, and then creating custom functions for repeated steps across multiple files.

What basic steps does the presenter demonstrate for working with files in Power Query?

The presenter demonstrates loading files from a folder, removing unnecessary columns, and extracting data from Excel files using functions like 'Excel.Workbook' to convert binary data into tables for further processing.

What is the purpose of the 'custom column' step in Power Query?

The 'custom column' step in Power Query is used to create new columns based on transformations applied to existing columns, such as extracting the content of an Excel file and converting it into a table for analysis.

How does the presenter show how to use the 'table.promote headers' function?

The presenter demonstrates using 'table.promote headers' to promote the first row of data in a table as column headers, which is often necessary when working with structured data extracted from Excel files.

What is the advantage of using a custom function over manually applying transformations?

Using a custom function makes it easier to apply the same set of transformations to multiple files without having to manually repeat the steps for each file. It also keeps the query window cleaner and more manageable.

What is the process for creating a custom function in Power Query?

To create a custom function, you duplicate an existing query, modify it in the Advanced Editor to accept parameters (such as file content), and apply the transformations to those parameters. The function is then invoked in the main query to transform multiple files.

How can you modify a custom function after it has been created?

You can modify a custom function by toggling between its function and query form. By commenting out certain steps in the Advanced Editor, you can adjust the function as needed without disrupting the overall query structure.