Power Query Formula Explanation

[DrDebit]

I have a list of ingredients separated by a semicolon and a space: Basil; Roasted Red Peppers; ; ;

However, there are semicolons at the end, followed by a space, and I want to eliminate them so the end is clear.

AI tells me: Text.TrimEnd([ColName],{";"}&{" "})

...and it worked perfectly...except I don't understand the curly cue braces, and why the first semicolon in the formula removes all of the semicolons and not just the final semicolon and space. Please help and thank you in advance.

 

[Kaper]

with

{";"}&{" "}

you create a list of two elements: semicolon and space

if there were just series of semicolons at the end of text you could use just ";" as last (optional) parameter of TrimEnd, but in your case you need to remove both spaces and semicolons in the same step.

To clarify: TrimEnd checks a string from far right, and checks the last character in a string. If it is on this two-element-list it is removed and process is repeated until character not on the list is met.

To see the list yourself you can create an empty query and in advanced editor paste such code:

let
    Source = {";"}&{" "}
in
    Source

 

[DrDebit]

with

{";"}&{" "}

you create a list of two elements: semicolon and space

if there were just series of semicolons at the end of text you could use just ";" as last (optional) parameter of TrimEnd, but in your case you need to remove both spaces and semicolons in the same step.

To clarify: TrimEnd checks a string from far right, and checks the last character in a string. If it is on this two-element-list it is removed and process is repeated until character not on the list is met.

To see the list yourself you can create an empty query and in advanced editor paste such code:

let
    Source = {";"}&{" "}
in
    Source

First, thank you so much for this.
2 questions, please...I tried trimming the column first, and that removed the trailing space at the end.
Then, I tried: Text.TrimEnd([Merged], ";") ... but it didn't work .

Second, when I create the list, is PQ looking for each one individually? Why don't I just list them...why do I need the &?

Thank you again!

 

[Kaper]

Text.TrimEnd([Merged], ";")
would remove trailing semicolons. So last character has to be semicolon. If there are more semicolons (but not separated with other characters, they will be removed in the same step. So
DrDebit; Kaper;;;;;
will be cleared to:
DrDebit; Kaper

You need a list to let PQ remove in a single step of the removing from the right (as I said above, this process is repeated until non-removeable element is met) not just a specific character, but one which is present in a set of two (in your case, but mode general - many) options .

Have a look on such construction without using lists:

Text.TrimEnd(Text.TrimEnd(Text.TrimEnd(Text.TrimEnd(Text.TrimEnd(Text.TrimEnd([ColName]),";")),";")),";")

It shall be analysed from inner-most instruction:

Text.TrimEnd([ColName])

it removes trailing whitespaces then the result of this is exposed to

Text.TrimEnd(result_of_previous_instruction, ";")

so now trailing semicolons are removed
in next step (so in the code we are here:

Text.TrimEnd(Text.TrimEnd(Text.TrimEnd(Text.TrimEnd([ColName]),";"))

again whitespaces
then two more steps are added so your string will be properly purified. But what if you have:
DrDebit; Kaper; ; ; ; ; ; ; ; ;

A list approach will clear all to the right from our nicks, while such a construction as shown abowe only last few characters, like:
DrDebit; Kaper; ; ; ; ; ;

And final comment - to reply please use Reply, not Quote everyone can see a post which you are peplying to. Quote is usefull when you want to comment on on of several posts above, possibly written by sereral forum members.

 

[DrDebit]

Text.TrimEnd([Merged], ";")
would remove trailing semicolons. So last character has to be semicolon. If there are more semicolons (but not separated with other characters, they will be removed in the same step. So
DrDebit; Kaper;;;;;
will be cleared to:
DrDebit; Kaper

You need a list to let PQ remove in a single step of the removing from the right (as I said above, this process is repeated until non-removeable element is met) not just a specific character, but one which is present in a set of two (in your case, but mode general - many) options .

Have a look on such construction without using lists:

Text.TrimEnd(Text.TrimEnd(Text.TrimEnd(Text.TrimEnd(Text.TrimEnd(Text.TrimEnd([ColName]),";")),";")),";")

It shall be analysed from inner-most instruction:

Text.TrimEnd([ColName])

it removes trailing whitespaces then the result of this is exposed to

Text.TrimEnd(result_of_previous_instruction, ";")

so now trailing semicolons are removed
in next step (so in the code we are here:

Text.TrimEnd(Text.TrimEnd(Text.TrimEnd(Text.TrimEnd([ColName]),";"))

again whitespaces
then two more steps are added so your string will be properly purified. But what if you have:
DrDebit; Kaper; ; ; ; ; ; ; ; ;

A list approach will clear all to the right from our nicks, while such a construction as shown abowe only last few characters, like:
DrDebit; Kaper; ; ; ; ; ;

And final comment - to reply please use Reply, not Quote everyone can see a post which you are peplying to. Quote is usefull when you want to comment on on of several posts above, possibly written by sereral forum members.

Thank you so much, Kaper. Have you ever considered a career in education? Where did you learn Power Query?

 

[Kaper]

This is a bit offtopic, but as the problem is solved ...:

As a matter of fact, I am working in education. For 30 years on the position of associate professor at Warsaw University of Technology https://www.linkedin.com/in/janusz-bucki/

I'm by no means expert in PQ. I've learnt it myself (and still learning) case by case, as I had to use it. And first uses were not for myself, but to help others solve their problems at excelforum dot pl (admin there) and excelforum dot com (excel guru) - both sites at the same nick. I'm not active here - probably I'm really missing possibility to add a file as an attachment.

There is a lot of PQ courses, films and solved cases on the web, but I don't think I can name the best.

And once you know, I'm a teacher:
I wouldn't be myself not repeating:
To reply: Please use Reply, not Quote. Everyone can see a post which you are replying to.
Quote is usefull when you want to comment on one of several posts above, possibly written by sereral forum members. Or may be a small part of a post. Just to focus a reader on a specific point you are replying/commenting/reffering to.

And very final - at the bottom-right corner of the post there are 3 buttons. Two of them we discussed above (Quote and Reply). The third (Like) is a way to appreciate answer(s) received .

 

[RoryA]

FWIW, you don't need the & since that just joins the lists into one:

Text.TrimEnd([ColName],{";"," "})

 

[Kaper]

Sure, and that is the standard notation, which shall be used in such case. Thanks Rory for joining with this comment.

 

[DrDebit]

FWIW, you don't need the & since that just joins the lists into one:

Text.TrimEnd([ColName],{";"," "})

Thank you!

 

[DrDebit]

Sure, and that is the standard notation, which shall be used in such case. Thanks Rory for joining with this comment.

Thank you.