Power Query | Replacing Text Recommendation (Wildcard/RegEx)?

[ovetteabejuela]

Hi,

I have a very simple problem, so Im still working on names... full names unfortunately there are some inconsistencies that I wanted to fix.

For example there's a
      <Last Name><comma><First Name>
and theen there's a
      <Last Name><comma><space><First Name>
and I wanted everythin in the latter format, <Last Name><comma><space><First Name>.

What I'm doing is
Replace <Last Name><comma><First Name> with
             <Last Name><comma><space><First Name>
great but that would cause the other entries to turn into
            <Last Name><comma><space><space><First Name>
so I add an extra step to
Replace <Last Name><comma><space><space><First Name> with, or back to
             <Last Name><comma><space><First Name>

so that's two steps. The question, is there a better way of doing this, a one-liner?

[Ashish_Mathur]

Hi,

Try this

1. Select that column heading and right click > Replace
2. Find for space and click on Replace All
3. Select that column heading and right click > Replace
4. Find for , and in the Replace with box, type , i.e. comma and space
5. Click on Replace All

Hope this helps.

[MarcelBeug]

@Ashish_Mathur that are still 2 steps ...

[ovetteabejuela]

@Ashish_Mathur, thanks but I think that was my path already.

@MarcelBeug, thanks for that atleast that's a oneliner. so with you suggesting that I'd presume PQ doesn't have anything like Regular Expressions which I think could address this gracefully. Anyway I'm happy with your suggestion aleady.

[MarcelBeug]

Well, I think my first alternative is pretty graceful (M-style). 

Anyhow, what regular expression would you have in mind?

E.g. in Word, I can't think of any way to replace "," or ", " by ", " in 1 step.

[MarcelBeug · Accepted solution]

Apart from combining multiple steps in 1 line, I don't see any way.

2 Alternatives:

Table.AddColumn(Source, "Custom", each Text.Combine(List.Transform(Text.Split([Name],","), Text.Trim),", "))

or

Table.AddColumn(Source, "Custom", each Text.Replace(Text.Replace([Name],",",", "),",  ",", "))