Log In
Register
Home
Trusted Members
October 18, 2018
Offline
I am attempting to remove all consonants from a column of Text only data.
ie. Alan will result in Aa
Maxwell will result in ae
Combine will result in oie
I have attempted using Text.Select([FieldName],{"A","E", "I", "O", "U","a","e","i","o","u"}) with out success. PQ generates an error and indicates the error at the first comma.
Any thoughts on a workaround
Alan
See Answer
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Try this one:
let
Custom2 = List.Select(Text.ToList("Combine"),each List.Contains({"A","E", "I", "O", "U","a","e","i","o","u"},_))
in
Custom2
Trusted Members
October 18, 2018
Offline
Thought we might be onto something there. However, that returns a List and when expanded shows only the letters oie for each record. The data being tested is seven rows of data, each row the name of the day of the week, ie. Sunday, Monday, etc.
Really didn't think that this would be so difficult.
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
It's not that difficult, I gave you the formula, was hoping you can adapt it to your scenario.
Returns the oie list because I used Text.ToList("Combine"), where the part in red is the analized text. Combining a list back into text is very easy: Text.Combine.
Full example attached.
Answers Post
Trusted Members
October 18, 2018
Offline
My apologies. I missed changing your lookup word to my field name when I copied and pasted for testing. Thank you for your help and pointing out my carelessness. Much appreciated. Your scenario works perfectly when applied properly.
Alan
1 Guest(s)
