PL-300: how to use parameters and functions in Power Query to prepare data

In this lesson I choose the skill: use parameters and functions in Power Query (Power BI Editor) to prepare data. This ability is relevant to the PL-300 exam because it shows how to make transformations reusable, reduce repetition, and manage loads with flexibility — all very useful in real scenarios where the source, format, or period of the data often change.

What you need to know

Parameters and functions in Power Query (M) allow you to make transformation processes dynamic and reusable. A parameter is a named value you can change without editing individual steps — for example: DataStart, ServerName, FilePath, CSVDelimiter or KeyColumn. A function is a parametrically defined sequence of steps that accepts arguments and returns a result, allowing you to apply the same logic to multiple sources or data partitions without duplicating code.

Conceptual example: imagine 12 CSV files, one per month, each with about 100k rows. Instead of creating 12 queries with the same steps, you create a parameter for the directory and another for the file name pattern (for example "Vendas_YYYYMM.csv"). Then you create a function that, given a path, imports, promotes headers, adjusts column types and applies a date filter. Applying that function to a list of 12 files yields a unified table with much less maintenance.

How it works in practice

Basic step-by-step to create a parameter and a simple function in Power Query:

1. Create a parameter

   In Power BI Desktop: Home > Manage Parameters > New Parameter. Define Name, Type (Text, Number, Date) and Default value. For example: CaminhoBase = "C:\\Dados\\Vendas\\". You can also set Suggested Values (list of options) to force selection or allow the parameter to be required when publishing to the service.

2. Build a query that uses the parameter

   Import a sample file and replace the fixed path with the parameter. For example change File.Contents("C:\\Dados\\Vendas\\Jan.csv") to File.Contents(CaminhoBase & NomeFicheiro). Test the change to verify that you only need to change the parameter to point to another directory or environment (development vs production).

3. Turn the query into a function

   In the Power Query Editor, convert the query to a function: Open Advanced Editor and change the definition to something like (FilePath as text, Delimiter as text) => let ... in .... The function can accept 1–3 parameters (e.g.: FilePath, Delimiter, Encoding) and return a table already cleaned and typed.

4. Apply the function to a list

   Create a query that lists files in a directory (Folder connector). Filter by extension and by name pattern, then use Add Column > Invoke Custom Function to apply the function to each row. Finally Expand the resulting column to combine all results into a single table. For 12 files this is instantaneous; for hundreds, consider filtering or processing in batches.

// Exemplo de função em M com dois parâmetros
(FilePath as text, Delimiter as text) =>
let
    Source = Csv.Document(File.Contents(FilePath), [Delimiter=Delimiter, Columns=8, Encoding=1252]),
    Promoted = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    ChangedTypes = Table.TransformColumnTypes(Promoted, {{"Data", type date}, {"Produto", type text}, {"Quantidade", Int64.Type}, {"Valor", type number}}),
    Filtrar = Table.SelectRows(ChangedTypes, each [Quantidade] > 0)
in
    Filtrar

Then, if you have a FileList table with 12 rows (one per month), add a custom column that invokes the function for each FilePath and, finally, Expand to combine all results into a single stable table for modeling.

Common mistakes

• Using parameters only for static values — often parameters are used only for paths. Create parameters for delimiters, encodings, date filters (DataStart/DataEnd) and to control conditional logic (include/exclude columns).
• Functions that are too complex — if the function tries to do many different tasks it becomes hard to maintain; break it into small functions (for example: ImportCSV, NormalizeColumns, MapDimensions) and compose them. This makes testing and debugging simpler.
• Not considering performance — invoking functions row by row on large lists can be slow. Whenever possible use native table operations (Table.Combine, Folder.Contents) and minimize calls to File.Contents or Web.Contents inside loops. For large volumes, test first with 1k–10k rows and measure times; use Table.Buffer judiciously and consider query folding when compatible sources exist (SQL, OData).

How to practice

Practice with a concrete scenario: put 12 CSV files in a directory, each with ~10–100k rows. Create parameters: CaminhoBase, Delimiter and DataFilter. Create a function that imports and normalizes the columns, applies DataFilter and returns the table. Then use Folder.Contents to get the file list and apply the function. Measure the time and experiment with optimizations, such as reducing calls to File.Contents and combining binaries when appropriate.

For PL-300 preparation consult the OFFICIAL and free Microsoft Practice Assessment and the official study guide (both free). Use these resources to check the exam areas and identify knowledge gaps — there is no substitute for practicing in the Power Query Editor with real data.

In summary

• Parameters make queries dynamic and easy to manage without editing steps manually.
• Functions in M allow encapsulating and reusing transformations across multiple sources or partitions.
• Combine Folder.Contents or lists with functions to join files efficiently; for dozens to hundreds of files, think in batches and reduce IO.
• Be mindful of performance: avoid unnecessary row-by-row invocations, split complex functions and test with representative samples before applying to the full set.