How to avoid rows being grouped in Matrix

[alexbjorlig]

I have a matrix, where I would like user names for each row.

My problem arises when there are multiple users with the same name. The Matrix "group" them together.

If I add the unique user id as a subfield, you can see there are actually 3 Ryan Georgetowns:

So my question is, how can I get the Matrix so I have Ryan Georgetown 3 times?

[v-juanli-msft]

Hi @alexbjorlig 

Is this problem sloved? 

If it is sloved, could you kindly accept it as a solution to close this case and help the other members find it more quickly?

If not, please feel free to let me know.

Best Regards

Maggie

[Greg_Deckler]

Create a column:

[Name] & "(" & [User ID] & ")"

Use that in your rows?

[alexbjorlig]

I should have added, that I don't want the id to be present in the matrix. Is there not a concept in Power BI for value/display value in a field? 

[Greg_Deckler]

Yes, it has that concept, but that more applies to renaming columns within a visual. So, let's take a different approach since this is the first time that I can remember in 5 years or so of someone wanting a non-aggregating Matrix. What is intention behind having a non-aggregating matrix? What are you trying to achieve?

[alexbjorlig]

Hehe sure - I am new to Power BI so that sounds like a good plan 😊

The objective is to create a hierarchy of user logins, for different sites vs roles:

/*
                "role 1"    "role 2"
"site A"
    user1                       0
    user2           1
    user3           2

"site B"
    user4                       0
    user5           3

*/

Works pretty good currently, only problem is when users have the same name.

[v-juanli-msft · Accepted solution]

Hi @alexbjorlig 

Would you mind add index to the repeated names?

If so, please add index in Power Query, then merge name and index column to a new user name column.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKs4sSVVIVNJRSswFEgZKsTpIYjk5QMIQVQyszgghlgTkFhWh6gWJlRRB9MYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [site = _t, user = _t, role = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"site", type text}, {"user", type text}, {"role", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"user"}, {{"AllRows", each _, type table [site=text, user=text, role=number]}}),
    AddIndex0 = Table.TransformColumns
    (
        #"Grouped Rows",
        {{"AllRows", each Table.AddIndexColumn
        (
            _,
            "Index0",
            0
        )}}
    ),
    AddIndex1 = Table.TransformColumns
    (
        AddIndex0,
        {{"AllRows", each Table.AddIndexColumn
        (
            _,
            "Index1",
            1
        )}}
    ),
    #"Expanded AllRows" = Table.ExpandTableColumn(AddIndex1, "AllRows", {"site", "user", "role", "Index0", "Index1"}, {"AllRows.site", "AllRows.user", "AllRows.role", "AllRows.Index0", "AllRows.Index1"}),
    #"Inserted Merged Column" = Table.AddColumn(#"Expanded AllRows", "new user name", each Text.Combine({[user], Text.From([AllRows.Index1], "en-US")}, " "), type text)
in
    #"Inserted Merged Column"

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.