Question

How can I apply different transformations to different subsets of data in Power Query?
I have a dataset in Power BI where I need to apply different transformations depending on the subset of data. For example, certain rows require text replacements, while others need type changes or filtering. What is the recommended approach in Power Query to conditionally apply transformations based on specific column values or row-level logic?

Answer 1

Different transformations can be applied to a distinct subset of data through the use of conditions (if-then-else logic) on custom columns, filters, or applied steps to specifically locate and target specific rows and other such data based on several of its values.

Recommended Approach
Conditional Logic in Custom Columns
A custom column could be set with different transformation rules:

if [Category] = "TextFix" then Text.Replace([Value], "old", "new")
else if [Category] = "ConvertToNumber" then Number.FromText([Value])
else null

• Split Queries (Optional)
  If transformations are complex:

  • Duplicate the query.

  • Apply specific transformations to each subset.

  • Use Append Queries to recombine them after transformation.

• Use Filtered Steps for Targeted Changes
  You can apply transformations to filtered views:

  • Filter the subset you want to change.

  • Apply the transformation (e.g., change type, replace values).

  • Remove the filter to restore full dataset (Power Query retains the applied logic to the original subset only).