Power Query Help!

[littlemojopuppy]

HI!  Hoping someone can help with this...

Trying to put together a sample for students based on 2022 D1 college football rankings week by week history.  My source data has several values (team, wins, losses, and first place votes) in the same field.  Trying to parse the data into several fields.  Screen snip of sample data below.

On the left is source data for two teams from the AP Poll, in orange is the expected output.  Theoretically, all of these are possible:

• Alabama (6–0) (11)
• Ohio State (6–0) (11)
• Miami (FL) (6–0) (11)

I can't parse on the first occurence of a space (Ohio State) or open parenthesis (Miami (FL)).  Had the idea that if I started parsing from the right instead of left I might be able to get what I wanted, but no.  Not every record has a value for first place votes.  These are also possible values:

• Miami (FL) (2–0)
• Alabama (7–1)

So it seems like looking for a variable number of open parenthesis as a delimiter from either the start or end...

Would truly appreciate some ideas on how to parse out (in this example) 6 and 0 for wins and losses respectively.  Attaching the pbix since it's publicly available data.

Thank you!

[ThxAlot · Accepted solution]

To end up your monologue,

let
    Source = Table.Combine({#"CFP Rankings", #"AP Poll", #"Coaches Poll"}),

    #"Extracted Score" = Table.AddColumn(
        Source,
        "Score",
        each let
            pos_dash = Text.PositionOf([Team],"–"),
            num1 = try Text.AfterDelimiter(Text.Range([Team],0,pos_dash), "(", {0, RelativePosition.FromEnd}) otherwise null,
            num2 = try Text.Remove(Text.BeforeDelimiter(Text.Range([Team],pos_dash), ")"), "–") otherwise null
        in [Wins=num1, Losses=num2]  
    ),
    #"Expanded Score" = Table.ExpandRecordColumn(#"Extracted Score", "Score", {"Wins", "Losses"}),
    #"Transformed Team" = Table.TransformColumns(#"Expanded Score", {"Team", each Text.Trim(Text.BeforeDelimiter(_, "("))})
in
    #"Transformed Team"

[littlemojopuppy]

@ThxAlot this is perfect!  Thank you!  😊

PS: those were mostly notes to myself so when I went back and looked at it next weekend I'd remember my thought process

[littlemojopuppy]

What could work is if I could extract from the string the portion in the format of (XX-XX).  Meaning finding where there is an open parenthesis and a close parenthesis with a hyphen between then, with any number of characters between the hyphen and either open/close parenthesis.  Maybe???

But I have no idea how to implement that in Power Query...

[littlemojopuppy]

Played around with the column from examples feature in PQ.  No luck, which isn't surprising given that there are conditions like "maybe the first but possiblly the second open parenthesis" and "maybe the first but sometimes the second close parenthesis".  But I had an idea!

1. Find the position of the hyphen in the string.
2. Work backward (to the left) from the hyphen until the open parenthesis to get wins.
3. Work forward (to the right) to close parenthesis for losses.

Have to keep the position of the open parenthesis to extract everything before that for the team name (so that Miami (FL) Hurricanes or Miami (OH) Redhawks don't screw things up).

Don't recall ever creating a custom function in Power Query, but this seems like the kind of thing that could be accomplished with one.

If anyone has other, better suggestions I'm open to them!  Thank you!