add members to group with asterisk

[rodg]

Hi,

I'm looking for a power query solution to group users in a group.

This is my sourcedata :

Source = Table.FromRows(
{
{"Eddie*"},
{"Eddie1"},
{"EddieA"},
{"Eddie2ABC"},
{"ADM*"},
{"ADM_5"},
{"Els"}
},
{"Users"}
)

I want to group this list based on the suffix *.

If there is no * at the end the groupname should be the same as the username.

So something like this :

Can someone point me in the right direction?

Thanks

Ronny

[AlienSx]

let
    group = Table.Group(
        Source, 
        "Users",
        {"x", (x) => Table.SelectRows(x, (row) => not Text.EndsWith(row[Users], "*"))}, 
        GroupKind.Local, 
        (s, c) => Number.From(not Text.StartsWith(c, Text.TrimEnd(s, "*")))
    ), 
    xpand = Table.ExpandTableColumn(group, "x", {"Users"}, {"user"})
in
    xpand

[rodg]

Hi,

If I add an extra record ("Elsje") to the source, the grouping is no longer correct.

Source :

'Elsje' may not be grouped with 'Els' because 'Elsje' does not end with an asterisk.

Result

Desired result

Any idea how I can solve this?

let
  Source = Table.FromRows(
    {{"Eddie*"}, {"Eddie1"}, {"EddieA"}, {"Eddie2ABC"}, {"ADM*"}, {"ADM_5"}, {"Els"}, {"Elsje"}}, 
    {"Users"}
  ), 
  group = Table.Group(
    Source, 
    "Users", 
    {"x", (x) => Table.SelectRows(x, (row) => not Text.EndsWith(row[Users], "*"))}, 
    GroupKind.Local, 
    (s, c) => Number.From(not Text.StartsWith(c, Text.TrimEnd(s, "*")))
  ), 
  xpand = Table.ExpandTableColumn(group, "x", {"Users"}, {"user"})
in
  xpand

Thx

[dufoq3]

Hi @rodg, similar solution but with different logic in 5th argument of Table.Group

Output

let
    Source = Table.FromRows({{"Eddie*"},{"Eddie1"},{"EddieA"},{"Eddie2ABC"},{"ADM*"},{"ADM_5"},{"Els"},{"Elsje"}},{"Users"}),
    GroupedRows = Table.Group(Source, "Users", {{"T", each Table.FillDown(Table.FromColumns({{[Users]{0}?}, if Table.RowCount(_) = 1 then {[Users]{0}?} else List.Skip([Users])}, type table[UserGroup=text, User=text]), {"UserGroup"}), type table }}, 0,
        (x,y)=> [ a = Text.PositionOf(x, "*"),
                  b = if a = -1 then Value.Compare(y, x) else Value.Compare(Text.Start(y, a), Text.Start(x, a))
                ][b] ),
    T = Table.Combine(GroupedRows[T])
in
    T

[rodg]

Hi @dufoq3 

Thanks for your solution.
But to be honest, I find the custom functions hard to understand. So I solved it with some extra steps.
Maybe not as elegant but, for now, easier to understand. 😉

thx

Ronny

[AlienSx · Accepted solution]

let
  Source = Table.FromRows(
    {{"Eddie*"}, {"Eddie1"}, {"EddieA"}, {"Eddie2ABC"}, {"ADM*"}, {"ADM_5"}, {"Els"}, {"Elsje"}}, 
    {"Users"}
  ), 
  group = Table.Group(
    Source, 
    "Users", 
    {"x", (x) => Table.SelectRows(x, (row) => not Text.EndsWith(row[Users], "*"))}, 
    GroupKind.Local, 
    (s, c) => Number.From(not (Text.EndsWith(s, "*") and Text.StartsWith(c, Text.TrimEnd(s, "*"))))
  ), 
  xpand = Table.ExpandTableColumn(group, "x", {"Users"}, {"user"})
in
  xpand

[ZhangKun]

let
    Source = Table.FromRows({{"Eddie*"},{"Eddie1"},{"EddieA"},{"Eddie2ABC"},{"ADM*"},{"ADM_5"},{"Els"}},{"Users"}),
    Group = Table.Group(Source, {"Users"}, {{"tbl", each Table.Skip(_, 1)}}, GroupKind.Local, (x, y) => Number.From(Text.EndsWith(y[Users], "*"))),
    ExpandTable = Table.ExpandTableColumn(Group, "tbl", {"Users"}, {"Users.1"})
in
    ExpandTable