Forum Discussion
Power Query | Replacing Text Recommendation (Wildcard/RegEx)?
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?
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],",",", "),", ",", "))
5 Replies
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],",",", "),", ",", "))
Hi,
Try this
- Select that column heading and right click > Replace
- Find for space and click on Replace All
- Select that column heading and right click > Replace
- Find for , and in the Replace with box, type , i.e. comma and space
- Click on Replace All
Hope this helps.
Ashish_Mathur that are still 2 steps ... :smileywink:
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.
