Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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],",",", "),", ",", "))
User | Count |
---|---|
102 | |
84 | |
77 | |
70 | |
67 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |