Forum Discussion

Eric5605's avatar
Eric5605
Icon for Helper I rankHelper I
1 year ago
Solved

How to combine similar values

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?  

 

Need Help
  • FarhanJeelani's avatar
    FarhanJeelani
    1 year ago

    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

3 Replies

  • FarhanJeelani's avatar
    FarhanJeelani
    Icon for Super User rankSuper User
    1 year ago

    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