Hi,
I have a set of users' full forenames, and their preferred forename. I've been asked to transform the forenames so that the preferred forename is shown in full and others show just the initial.
Example, Albert Ben Charles, with a preferred forename of Ben, woukld show as A Ben C. There could be up to five names in the full forename.
I've achieved this with basic M-Code (qryBasic), but it's obviously not scaleable to accommodate full forenames with even more elements. I have successfully used Table.TransformColumns to convert each full forenames into a list (qryConvertFullToList) and have also extracted the list position of a static preferred name (qryFindPositionInList), but can't reference a field in the same row. Here's what I have so far:
I'd be very grateful if someone could point me in the right direction - ideally without adding extra columns. My thoughts are:
- Check each list;
- If an element of that list matches the field [Preferred] then retain it as-is; else use (Text.Start,1) to return just the first letter; this is the part that's eluding me!
- Use Text.Combine to produce the final output.
Any help would be much appreciated!
Hi again,
Having thought about it further overnight (in UK), I think my question boils down to finding a function that takes a text string and tests each item in a list, returning TRUE or FALSE for each item depending on whether it matches. So in my earlier example, if the text string is Ben and the list is {Albert, Ben, Charles}, the list would be transformed to {FALSE, TRUE, FALSE}.
If I could achieve that, I could then use TRY to determine whether to abbreviate each name or write it in full.
Finally, I'd be thinking of using a nested list such as {{FullNames},{Preferred}}, where FullNames is the list of full forenames I'm testing, to make it all scaleable, ie to accommodate different lengths of FullNames.
Does this look reasonable?
Thanks again,
Pieter
Must you use PQ? If you can live with a formula solution the modified names can be created with the following formula in your blue table.
=LET(
names, TEXTSPLIT([@Full]," "),
pref, [@Preferred],
transf, IF( names = pref, pref, LEFT(names,1)),
return, TEXTJOIN(" ", ,transf),
return)
@riny, thanks for a great formula solution. To be honest, I was already working on a formula solution in case I couldn't do it in PQ, to meet my deadline. Yours is a great solution. However, I'd still like to try for a PQ solution as (a) I'd prefer a code-free workbook and (b) I'd like to develop my PQ skills.
Thanks again,
Piet
Fair enough, though my motto is to solve problems the easiest way. Often with PQ, but in this case it seems much easier with Excel formulas. Personally, I'm not worried about having formulas in Excel. Why bother looking for a difficult solution?
You're right, of course! Nonetheless, a further point is that I needed to use PQ to create the data that I showed in simplistic form in my example, which draws from multiple sources. Because of that, I was hoping to continue my queries to achieve the desired output. No matter - I can always copy what I have into a flat Excel file, then apply the formula.
Hi again,
I've made some progress and, noting @riny's invaluable formula, I'm still trying to develop my M-Code skills by using PQ. I've attached an updated version:
In short, I can test each original full name by converting it to a list, then transforming that list by using Text.Combine to return just the first letter of each item that doesn't match a text string, and the full item where it does match.
Where I'm still stuck is substituting the Preferred field value for each text string. The logic I'm seeking is, "For each row, modify list Full according to field Preferred." This seems to take the form of a {List,[Field]} nested list, but at that point my logic fails me.
Any thoughts, please?
Hi Piet,
If this is still relevant for you, the attached file contains a simple PQ solution that does what you asked for. It suddenly occurred to me that we were thinking of a too complex solution.
Riny
Hi Riny, It's still very much of relevance to me and I've been working on it for a couple of days. You're right - I was looking for too complex a solution. I like your simpler version - many thanks. I've also come up with an alternative, which isn't so elegant, but helped my understanding quite a bit!
I actually find your solution very elegant. Would be interesting to see how both solutions perform on large data sets.
R
Thanks Riny, I'm still trying to work out if I can convert the Table.AddColumn to a function that allows me to use Table.TransformColumn instead, but that'll have to take a back seat for a while. I met my deadline, so it's only for self-improvement!