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
irnm8dn
Post Prodigy
Post Prodigy

Concatenation from two different tables - Explanation Needed

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?

 

8 REPLIES 8
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@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,

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@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

 

@irnm8dn - 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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.

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.