How to avoid rows being grouped in Matrix

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.