I need to pull every instance of the substring "Whisper from.• \d{1,2}:\d{2}:\d{2} (AM|PM) \n" from a larger string variable and convert it to "Whisper from.• \d{1,2}:\d{2}:\d{2} (AM|PM) - "

Essentially I need to replace the new line character with a "- " in every line that includes "Whisper from"

Set(
    varMessagesString,
    Substitute(
        varMessagesString,
        Text(Match(varMessagesString, "Whisper from.• \d{1,2}:\d{2}:\d{2} (AM|PM) \n").FullMatch),
        Concatenate(
            Text(Match(varMessagesString, "Whisper from.• \d{1,2}:\d{2}:\d{2} (AM|PM) ").FullMatch),
            "- "
        )
    )
)

This is only substituting the first instance of the substring within the string. I need it to substitute all instances but MatchAll is returning an error that it is expecting text. I assume MatchAll is converting it to an array. This is how I attempted the MatchAll:

Set(
    varMessagesString,
    Substitute(
        varMessagesString,
        Text(MatchAll(varMessagesString, "Whisper from.*• \d{1,2}:\d{2}:\d{2} (AM|PM) \n").FullMatch),
        Concatenate(
            Text(MatchAll(varMessagesString, "Whisper from.*• \d{1,2}:\d{2}:\d{2} (AM|PM) ").FullMatch),
            "- "
        )
    )
);

The error returned on the ".FullMatch" part of this is "Expected text or a number. We expect text or a number at this point in the formula."

I need to pull every instance of the substring "Whisper from.• \d{1,2}:\d{2}:\d{2} (AM|PM) \n" from a larger string variable and convert it to "Whisper from.• \d{1,2}:\d{2}:\d{2} (AM|PM) - "

Essentially I need to replace the new line character with a "- " in every line that includes "Whisper from"

Set(
    varMessagesString,
    Substitute(
        varMessagesString,
        Text(Match(varMessagesString, "Whisper from.• \d{1,2}:\d{2}:\d{2} (AM|PM) \n").FullMatch),
        Concatenate(
            Text(Match(varMessagesString, "Whisper from.• \d{1,2}:\d{2}:\d{2} (AM|PM) ").FullMatch),
            "- "
        )
    )
)

This is only substituting the first instance of the substring within the string. I need it to substitute all instances but MatchAll is returning an error that it is expecting text. I assume MatchAll is converting it to an array. This is how I attempted the MatchAll:

Set(
    varMessagesString,
    Substitute(
        varMessagesString,
        Text(MatchAll(varMessagesString, "Whisper from.*• \d{1,2}:\d{2}:\d{2} (AM|PM) \n").FullMatch),
        Concatenate(
            Text(MatchAll(varMessagesString, "Whisper from.*• \d{1,2}:\d{2}:\d{2} (AM|PM) ").FullMatch),
            "- "
        )
    )
);

The error returned on the ".FullMatch" part of this is "Expected text or a number. We expect text or a number at this point in the formula."

• powerapps
• powerapps-canvas
• powerapps-formula

1 Answer 1

Currently Power Fx doesn't have an easy way to do a "substitute all" based on regular expressions. There is a way to do that (see below), but it's way more complicated than it should be.

With(
  // Creating a "local value" called 'matches'
  { matches:MatchAll(TextInputCanvas1.Value, "Whisper from.*• \d{1,2}:\d{2}:\d{2} (AM|PM) \n") },
  If(
    CountRows(matches) = 0,
    TextInputCanvas1.Value,
    Concat(
      ForAll(
        Sequence(CountRows(matches) * 2 + 1) As indices,
        With(
          // Some local aliases to make the expressions below (a little) simpler
          {
            isFromMatch: Mod(indices.Value, 2) = 0,
            isLast: indices.Value = CountRows(matches) * 2 + 1,
            isFirst: indices.Value = 1
          },
          If(
            isLast, // After the last match, take substring
            Mid(TextInputCanvas1.Value, Last(matches).StartMatch + Len(Last(matches).FullMatch)),

            isFromMatch, // It's one of the matches, replace \n with -
            Substitute(Index(matches, indices.Value / 2).FullMatch, Char(10), "-"),

            // Between matches, take substring
            With(
              {
                start: If(isFirst, 1, Index(matches, indices.Value / 2).StartMatch + Len(Index(matches, indices.Value / 2).FullMatch)),
                end: Index(matches, (indices.Value + 1) / 2).StartMatch
              },
              Mid(TextInputCanvas1.Value, start, end - start)
            )
          )
        )
      ),
      Value
    )
  )
)

Reading this expression from the "outside", it:

• Creates a local 'alias' for all the matches
• If there are no matches, simply return the text value
• Otherwise create a sequence from 1..(#matches * 2 + 1)
  • Odd numbers represent the parts outside the matches, even numbers represent the matches
  • If outside the matches, take the corresponding substring based on the StartMatch indices
  • If inside the matches, take the full match, and replace '\n' with '-'
• Concatenate all the parts

Again, it's doable, but it should be much, much simpler.