Expression error

[Nancymara]

So I have this data, I merged, renamed and I’m trying to clean it using the Text.Select( function but it giving me an expression error message like this, I have a deadline to meetup. I would appreciate a effective split to this l. Anticipating

 

[alansidman]

Instead of a picture (which we cannot manipulate), suggest you upload a sample worksheet (8-10 records) showing your data and then manually mock up what your solution should look like. Use XL2BB to upload your data.

 

[smozgur]

Welcome to the MrExcel Message Board!

I don't know when it was actually included but it looks like the Text.Select function was not included in the Power Query version that you are using.

You can get a list of functions by creating a new blank query and entering = #shared as the only formula.

As you can see, it exists in the Power Query coming with Excel 365 on my computer.

 

[Nancymara]

So you are right about the function not added to the list of functions available on my power query, so how do I get to add the Text.Select function or is there an alternative please. Thank you

 

[smozgur]

If you are working in a PQ version that Text.Select doesn't exist, then you can try using a custom function like the following:
Just replace the Text.Select function in your formula with fnTextSelect with the same arguments.

    fnTextSelect = (txt as text, allowedChars as any ) => 
        let 
            Chars = Text.ToList(txt),
            Filtered = List.Select(Chars, each List.Contains(allowedChars, _)),
            Result = Text.Combine(Filtered)
        in
            Result

Or, if List.Select also doesn't exist, then the following might help:

    fnTextSelect = (txt as text, allowedChars as any ) => 
        let 
            Result = List.Generate(
                () => [i = 0],
                each [i] < Text.Length(txt),
                each [i = [i] + 1],
                each 
                    let 
                        Letter = Text.Middle(txt, [i], 1),
                        Filtered = if List.Contains(allowedChars, Letter) then Letter else null,
                        Result = Text.Combine(List.RemoveNulls(Filtered))
                    in
                        Result
            )
        in
            Result

 

[smozgur]

Note: You can add the custom function into the existing code directly, or set it up separately.

Directly into the single query:

let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
 
    #"Changed Type1" = Source, // Use your own code here
   
    Result = Table.AddColumn(#"Changed Type1", "Custom", each fnTextSelect([FullName], {"A".."z", " ", "."})),

    fnTextSelect = (txt as text, allowedChars as any ) =>
        let
            Chars = Text.ToList(txt),
            Filtered = List.Select(Chars, each List.Contains(allowedChars, _)),
            Result = Text.Combine(Filtered)
        in
            Result

in
    Result

Or, if you'd like to use this function in other queries as well, then create a blank query, copy and paste the following code in the Advanced Editor, and name the query as fnTextSelect.

(txt as text, allowedChars as any ) =>
    let
        Chars = Text.ToList(txt),
        Filtered = List.Select(Chars, each List.Contains(allowedChars, _)),
        Result = Text.Combine(Filtered)
    in
        Result