Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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],",",", "),", ",", "))
Hi,
Try this
Hope this helps.
@Ashish_Mathur that are still 2 steps ...
@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.
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.
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],",",", "),", ",", "))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |