[Advanced Editor] Conditional Concatenate

[Anonymous]

Hi All,
 
I'm having trouble with what's supposed to be a simple piece of M-script. I want to make a concatenate column. Here is what my data looks like:
Division   Facility     Item
A              1               123
B               -               123
C               2              123
 
Now the concatenate I would like to make is very simple. [Division] & [Facility] & [Item]. However, in case [facility] = "-"; I would like the concatenate to only be [Division] & [item].
So the results should be:
A1123
B123
C2123
 
 
I've got the following piece of M:
 
= Table.AddColumn(#"Create Key_DivFacIteCon", "Key_DivFacIteCon",
each if [Facility] = "-  "
then
each [#"Division"] & [Item]
else
each [#"Division"] & [Facility] & [Item]))
 
However when I run this I get an "Expression.SyntaxError: Token Eof expected." error. I could not find any specifics to this erros relating to concatenates. Has anyone here run into this problem before?
 
 

[AlB]

Hi @Anonymous 

You only need "each" at the beginning. "each" is actually syntax sugar for

(_)=>

i.e., the heading of a function with input parameter called "_".  You can find an excellent explanation here:

https://www.excelguru.ca/blog/2018/01/09/each-keyword-power-query/

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

Thank you for the additional info, I'll have a look at the detailed explanation as well!

[Mariusz · Accepted solution]

Hi @Anonymous 

Try the below script

 #"Added Custom" = Table.AddColumn( Source, "Custom", each [Division] & ( if [Facility] = "-" then "" else [Facility] ) & [Item], type text )

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Anonymous]

Thanks a lot! This did the trick!