Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to Solution.
Hi @Anonymous
Try the below script
#"Added Custom" = Table.AddColumn( Source, "Custom", each [Division] & ( if [Facility] = "-" then "" else [Facility] ) & [Item], type text )
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
Thank you for the additional info, I'll have a look at the detailed explanation as well!
Hi @Anonymous
Try the below script
#"Added Custom" = Table.AddColumn( Source, "Custom", each [Division] & ( if [Facility] = "-" then "" else [Facility] ) & [Item], type text )
Thanks a lot! This did the trick!