Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

[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?
 
 

1 ACCEPTED SOLUTION
Mariusz
Community Champion
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

 

View solution in original post

4 REPLIES 4
AlB
Super User
Super User

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 

SU18_powerbi_badge

Anonymous
Not applicable

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

 

 

Mariusz
Community Champion
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
Not applicable

Thanks a lot! This did the trick!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors