cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Creating Column in Table View Combining Two Tables with same ids but one with duplicates

I have two tables as depicted in the visuals here. As you can see table 2 has a mix of blanks and additional entries. I need table A exactly as is but with the blanks removed and additional entries from table 2 for the same id separated by commas as shown.

Preferably via DAX.

TIA

Needed table

 1 IA,XU 2 PG,GTM 3 XU 4 XU 5 GTM,IA

Table 1

 1 IA 2 PG 3 XU 4 XU 5 GTM

Table 2

 XU PG 1 IA 1 XU 2 PG 2 GTM 3 XU 4 XU 5 GTM 5 IA
3 REPLIES 3
Solution Sage

@aldoha  Hi try it 2 calculate table

``````CombinedTable =
FILTER (
UNION (
SELECTCOLUMNS ( 'Table 1', "ID", 'Table 1'[Col1], "Value", 'Table 1'[Col2] ),
SELECTCOLUMNS ( 'Table 2', "ID", 'Table 2'[Col1], "Value", 'Table 2'[Col2] )
),
NOT ISBLANK ( [ID] )
)``````

and

``````NeededTable =
SUMMARIZE (
CombinedTable,
CombinedTable[ID],
"CombinedValues", CONCATENATEX (
VALUES ( CombinedTable[Value] ),
CombinedTable[Value],
","
)
)``````

__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com
Frequent Visitor

Thanks for the reply. For the 1st one i get an error. The expression refers to muliple columns. Multiple columns cannot be converted to a scalar value

Solution Sage

@aldoha Hi, Show your DAX code

__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.