How to combine similar values

[Eric5605]

Hello, I receive a list of governement agencies each week with a program enrollment figures for each agency.  Some agencies are separated into sub-agencies, but I want to total each sub agency to the parent agency and just report that.  For example, all of the enrollments for the different DHS offices highlighted below to just be totaled into one DHS total.  I was trying to use find and replace to find any line containing DHS and replace it with just DHS.  I tried a wildcard but that does not seem to work.  Any suggestions?  

[FarhanJeelani · Accepted solution]

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

[Eric5605]

Thank you!  

[FarhanJeelani]

Can you please mark it as solution ??