Cache Text.Split / List Select line for multiple evaluation

[Goodkat]

Dear Power Query Enthusiasts,

Last night I had an idea how to improve some text string cleaning and editing. I do split a string with SplitAny to enable for multiple delimiters, then check if the last word matches a key word and if yes, combine the list back into a text, EXCEPT this last position. It works. But I realized, that I must use the core component
List.Select(Text.SplitAny([hea], " _"), each _ <> "")
Three times (perspectively even more often) in this procedure.

And here I questioned myself, but could nothing find in the internet, if I could wrap the core component into a List.Buffer statement and subsequently only reference that. I tried but it did not work.

Sample file can be found here: https://c.gmx.net/@324888734501700174/gC3MEGn8wu7DQH0H_bXjKg

Does the forum have any conclusive idea how to achieve that?

Thank you!

Best regards, Andreas

[jgeddes]

Can you provide examples of the intended output of the list provided in the file?
It will help to shape the code required.

[Goodkat]

Hi Jgeddes,

in the file behind the link in the Query 'Table1' Step 'SplitAndCombine' achieves the intended output. Step 'SplitAndBuffer' shall achieve the same, but with somehow buffering the List.Select(Text.SplitAny)) piece.

Best regards, Andreas

[jgeddes]

Here is another possible solution that uses List.Difference(). You can test it on your data to see if it works faster/better for you.

    Table.AddColumn(
        Source, 
        "Test", 
        each 
        let 
            keyWords = {"ID", "Key", "Number"},
            delimters = {"_", " "}, 
            nested = Text.SplitAny([hea], Text.Combine(delimters, "")) 
        in 
            if List.Count(nested) > 1 
                then Text.Combine(List.Difference(nested, keyWords, Comparer.OrdinalIgnoreCase), " ") 
                else [hea], 
        type text
    )

[jgeddes · Accepted solution]

Is this along the lines of what you are looking for?

SplitAndCombine =   
Table.AddColumn(
    Quelle, 
    "Test", 
    each
        let 
            codeBlock = List.Buffer(List.Select(Text.SplitAny([hea], " _"), each _ <> ""))
        in 
            if List.Contains({"id", "key", "number"}, List.Last( codeBlock ), Comparer.OrdinalIgnoreCase) = true and List.Count( codeBlock ) > 1 then
                Text.Combine( List.RemoveLastN( codeBlock, 1 ), " ")
            else
                [hea]
)

[Goodkat]

Dear Jgeddes,

Holy Smokes Sir!

That is exactly what I was looking to achieve! Super! And now I have a hands-on example of wrapping a 'let / in' statement within another 'let / in'. I had seen this before, but did not fully understand when and where I could use such construct. Now I know...

Thank you so much! You helped a ton and provided good insights to further bank on at my end!

Best regards, Andreas