How to combine similar values

To consolidate enrollments under a single parent agency (e.g., "DHS" for all DHS sub-agencies) in Power Query, you can use the following approach:

1. Add a Custom Column:
- In Power Query, go to Add Column > Custom Column.
- Use a formula to check if the `Agency` contains "DHS". If it does, set it to "DHS"; otherwise, keep the original value.

Power Query

if Text.Contains([Agency], "DHS") then "DHS" else [Agency]

- This will create a new column where any agency name containing "DHS" is replaced with "DHS".

2. Group By Parent Agency:
- After adding the custom column, go to Home > Group By.
- Group by the new custom column (let’s call it "Parent Agency").
- Aggregate the Active Enrolled column (or any enrollment figures) by using the Sum operation.

This method allows you to total all sub-agencies under a parent agency like "DHS" and display it as one total figure.

Please mark this as a solution if it helps you. Appreciate Kudos