Create new column basedon fields from other tables

[Back2Basics]

Hi

I am trying to add a new column and create an expression to generate the data in the column. I have 3 tables, they are all linked but not all linked together - so TableA is linked to TableB and TableA is linked to TableC, but there is not linked between TableB and TableC.

I'm hoping to add a new column in TableA is joins information from a column in each of the two other tables.

eg. New_Column = TableB[FieldA]&" - "&TableC[FieldB]

TableB is a lookup table for the field in TableA, where the fieldin TableA is a number and the same for the field in TableC.

However, when I try to do this I get an error saying that the syntax is incorrect. Is there a way to do this??

[vanessafvg]

are you trying to combine a number and string? 

[Back2Basics]

Hi Vanessafvg

I'm trying to combine two text fields.

the field in TableA is a number and the related TableB has two columns, one with the number and one with the correct text. i've used this for lookup to provide the correct text and it has worked in all other instances. What i'm trying to do now is combine two fields from TableA, but each field has a sperate lookup table. So I can combine them by using:

New_Column = TableA[fieldA]&" - "&TableB[fieldB]

but this will give me the two numbers in one field, and I'd like the text joined together.

[v-huizhn-msft · Accepted solution]

Hi @Back2Basics,

You can create calculated coulmn using the formulas and check if it works fine.

Column1 = RELATED(TableA[fieldA])
Column2=RELATED(TableB[fieldB])

Then create another calculated column CONCATENATE function.

New_Column=CONCATENATE(column1,column2)

Please feel free to ask if you have other issue.

Best Regards,
Angelia

[Back2Basics]

Many thanks Angelia

I haven't tried this yet but looks like it will work. I was trying to avoid adding in new columns where I already had the data, but it doesn't matter in the grand scheme of things - as long as it produces the right outcome.

Thanks again for your help