Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.