Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

[Advanced Editor] Conditional Concatenate

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?
 
 

  • 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

     

4 Replies

  • Mariusz's avatar
    Mariusz
    Community Champion

    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's avatar
      Anonymous
      Not applicable

      Thanks a lot! This did the trick!

  • AlB's avatar
    AlB
    Community Champion

    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's avatar
      Anonymous
      Not applicable

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