Concatenation from two different tables - Explanation Needed

[Anonymous]

Using the New Column function, I am attempting to concatenate a column from one table with a column in another table.

Depending on which table I create the column, it either recognizes one data element or the other.  What's up?

Tabel A = ID (formatted as Whole Number)

Table B = Product Category (Formatted as Text)

Because these live in two different tables, it doesn't seem to recognize that I want to concatenate two elements from two different tables?

Thoughts?

[Greg_Deckler]

I believe that you want to create a Merge or Append query. Kind of sounds like a Merge query is what you are describing.

[Anonymous]

@Greg_Deckler

Possibly, though I am not sure how to do this?

I am siply trying to create a third column in my data that combines the Product with the ID.

Becasue they live in two different tables, the Concatenate function doesn't work (but would if both data elements were in the same table).

Recommendations?

[Greg_Deckler]

Can you post a short sample of your data? What I am wondering is if there is a common key between your tables. In the Query Editor, on the Home tab all the way to the right is the "Merge Queries" link. This allows you to merge two queries as you are suggesting as long as you have some kind of common key between them.

[Anonymous]

@Greg_Deckler

I apprecitate that, however I don't think I want to merge the queries.

Do we agree that a CONCATENATE function is not available when there are two tables?

Thanks,

[Greg_Deckler]

Might be able to use it if you use RELATED or RELATEDTABLE. You could also look at using LOOKUPVALUE function. I'm still not clear what you are trying to accomplish. If you would post sample data and sample output that would greatly assist.

[Anonymous]

@Greg_Deckler

Wish I could post a sample dataset...I cannot.

I am siply trying to combine the the value from one column in Table A with a column in Table B.

i.e. 

Table A  = Apple

Table B = 123456

New column equals "Apple 123456"

Appreciate your interest and advice.

Thanks,

Adam

[Greg_Deckler]

@Anonymous - We don't need actual data, just sample data that is representative of your data. Do you really have just a single column in your data tables? Is it at least in the same order for both? If it is, you might be able to add an Index column on import for both and relate them that way via the Index columns.

The issue I think that we are all struggling with is that you want to concatenate two values from two separate tables but I do not see anything in any of your posts that shows how these tables are related to one another. That is going to be a requirement for any solution, a way to say that "Apple" is related to "123456". If there is no way to relate these two things programmatically I don't see a solution unless you would manually create a relationship table but at that point, you would already have the solution in manually creating that table.

[ruecj5]

As mentioned by @smoupre you can concatenate vlaues from two separate tables as long as there is an established relationship between the two by nesting the RELATED function inside CONCATENATE.

Column  = CONCATENATE(Table1[column], RELATED(Table2[column])) worked for me when I tested.