Distinct union of two tables but concatenatex one column

[Neiners]

I have two tables with similar columns that I want to union together with distinct values. I wrote the following code:

Distinct(

   UNION(

      SELECTCOLUMNS(table1,

      "Datasource", table1[Datasource],

      "Column1", table1[column1],

      "Column2", table1[column2],

      "Column3", table1[column3],

      "Column4", table1[column4]

      ), //closes table1

      SELECTCOLUMNS(table2,

      "Datasource", table2[Datasource],

      "Column1", table2[column1],

      "Column2", table 2[column2],

      "Column3", table3[column3],

      "Column4", table4[column4]

     ) //closes table 2

)//closes union

)//closes distinct

It is working great except I need to somehow combine (concatenate, group by, ????) the datasource columns together so I can quickly see where those two tables have all 4 matching columns because it will list the datasources together instead of separate rows.

Expected outcome:

Datasource|Column1|Column2|Column3|Column4

datasource1,datasource2|value1|value2|value3|value4

datasource1|value5|value6|value7|value8

datasource2|value9|value10|value11|value12

Any help would be greatly appreciated! I have tried numerous ways and nothing seems to work for me using DAX. I know this can be done in Power Query with appending the data and then grouping using Text.Combine on the DataSource column but I need to be able to do this in DAX.

[rajendraongole1 · Accepted solution]

Hi @Neiners -create a table that unions the two table as below:

CombinedTable =
DISTINCT(
UNION(
SELECTCOLUMNS(
table1,
"Datasource", table1[Datasource],
"Column1", table1[Column1],
"Column2", table1[Column2],
"Column3", table1[Column3],
"Column4", table1[Column4]
),
SELECTCOLUMNS(
table2,
"Datasource", table2[Datasource],
"Column1", table2[Column1],
"Column2", table2[Column2],
"Column3", table2[Column3],
"Column4", table2[Column4]
)
)
)

Next, group the combined table by the other columns and concatenate the Datasource column 

ResultTable =
ADDCOLUMNS(
SUMMARIZE(
CombinedTable,
[Column1],
[Column2],
[Column3],
[Column4],
"Datasources",
CONCATENATEX(
VALUES(CombinedTable[Datasource]),
CombinedTable[Datasource],
","
)
)
)

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[Neiners]

I have tried duplicating this using the Combined Table as a variable and then the Results Table as a second variable but when I get to Concatenatex(Values, the CombinedTable variable with the datasource column isn't available as an option.

[Neiners]

I am marking this as accepted and I started a new thread for the questions about the other datasources.

https://community.fabric.microsoft.com/t5/Desktop/Union-multiple-tables-with-uneven-columns-and-then...

Thank you for your help!

[Neiners]

I got it to work, is there a way to do this without having to make two separate tables but instead use variables

MasterTable=

var _combinedtable = ...

var _resulttable =.....

RETURN _resulttable

I will need to do further joins from other datasources but I am trying to wrap my head around 2 datasources first. A total of 7 different datasources. The union of those other datasources have a different number of columns. Some only have column1, some have columns 1,3,4, and some have columns 2 and 3. I've unioned them using "columnx", blank() but it causes extra rows where all I need is the datasource column.

Datasource 1 & 2 =datasource, column1-4

Datasource 3 = datasource, column 1,3,4

Datasource 4 and 5=datasource, column 1

Datasource 6=datasource, column 2,3

Datasource 7=datasource, column 2,3,4

[Neiners]

I am getting an error on the ResultTable "Too few arguments were passed to the addcolumns function. The minimum argument count for the function is 3.