Forum Discussion

ekodami's avatar
ekodami
Icon for Helper I rankHelper I
19 days ago
Solved

Need help with Column Pivoting

Hi folks, thanks in advance for your help with this one.

I have a table named 'Application User' containing relationship between users and the Application they use.

Table 'Application User' includes following columns

assetinstanceid; full_name; role ; assetid

Indeed, An application can be used by many users 

What I need is to turn the data so that all user associated to an application be inserted into one field so to have one row per application. See below the steps I have taken and my expectations:

I could run up-to Step 3 and currently stucked there. See attached

Any thoughts?

Thanks

power bi
Tips and Tricks
  • Shai_Karmani's avatar
    Shai_Karmani
    19 days ago

    Hi,

     

    Power Query's Group By is the right tool here, but the UI only exposes numeric aggregations (Sum, Count, Max) and not a text concatenation. The trick is to pick "All Rows" as the operation, which gives you a nested table per group, then build the concatenated text column from it.

     

    1. Select the assetinstanceid column.
    2. 2. Transform > Group By > Advanced.
    3. 3. Group by assetinstanceid (add asset_id as a second grouping if you want to keep it).
    4. 4. Add aggregation: new column name "Users", Operation = "All Rows".
    5. 5. Add a Custom Column with the formula: Text.Combine([Users][full_name], ";")
    6. 6. Remove the nested [Users] column.

    Or do it in a single M step in the formula bar:

     

    = Table.Group(#"PreviousStepName", {"assetinstanceid", "asset_id"}, {{"Used by", each Text.Combine([full_name], ";"), type text}})

     

    That gives you one row per application with all usernames joined by a semicolon, which matches your expected result.

     

    If this works for you, kindly mark it as the solution and give a thumbs up.

     

    Thanks,

    Shai Karmani

3 Replies

  • Shai_Karmani's avatar
    Shai_Karmani
    Icon for Super User rankSuper User
    19 days ago

    Hi,

     

    Power Query's Group By is the right tool here, but the UI only exposes numeric aggregations (Sum, Count, Max) and not a text concatenation. The trick is to pick "All Rows" as the operation, which gives you a nested table per group, then build the concatenated text column from it.

     

    1. Select the assetinstanceid column.
    2. 2. Transform > Group By > Advanced.
    3. 3. Group by assetinstanceid (add asset_id as a second grouping if you want to keep it).
    4. 4. Add aggregation: new column name "Users", Operation = "All Rows".
    5. 5. Add a Custom Column with the formula: Text.Combine([Users][full_name], ";")
    6. 6. Remove the nested [Users] column.

    Or do it in a single M step in the formula bar:

     

    = Table.Group(#"PreviousStepName", {"assetinstanceid", "asset_id"}, {{"Used by", each Text.Combine([full_name], ";"), type text}})

     

    That gives you one row per application with all usernames joined by a semicolon, which matches your expected result.

     

    If this works for you, kindly mark it as the solution and give a thumbs up.

     

    Thanks,

    Shai Karmani

    • ekodami's avatar
      ekodami
      Icon for Helper I rankHelper I
      18 days ago

      Hi Ashish, I already had it resolved from Shai's proposed solution above. Thanks anyway!