Union multiple tables with uneven columns and then concatenatex rows together

[Neiners]

I have 7 tables that I would like to combine together into a master table using DAX.  There are an uneven amount of colums between all the tables out of 5 possible columns. For the tables with missing columns, I have incorporated the column name using "column name", blank(). They all will have a datasource column, and then a combination of the other 4 columns. 

Table 1 and Table 2: datasource and columns 1-4

Table 3: datasource and columns 1,3,4

Table 4 and 5: datasource and column 1

Table 6: datasource and columns 2-3

Table 7: datasource and columns 3-4

I have used the code below for the union of all the tables and then created a second Table to summarize columns 1-4 and then add a datasource column that concatenates the datasource columns together. This works great, but what I'm finding is those that have blank() for columns, could potentially be summarized, but are not being summarized because the blank value doesn't match what the other datasources have for those columns. For example, Table 4 and 5 only have the 1st column (computername) and nothing else. I'm basically trying to do in DAX like you would do with append, merge and Group by using Text.Combine in Power Query but I need to do this in DAX. I am open to any and all suggestions. 

CombinedAssets =

Distinct(

UNION(

SELECTCOLUMNS(table1,

"Datasource", table1[Datasource],

"Column1", table1[computername],

"Column2", table1[computerid],

"Column3", table1[serialnumber],

"Column4", table1[macaddress]

), //closes table1

SELECTCOLUMNS(table2,

"Datasource", table2[Datasource],

"Column1", table2[computername],

"Column2", table2[computerid],

"Column3", table2[serialnumber],

"Column4", table2[macaddress]

), //closes table2

SELECTCOLUMNS(table3,

"Datasource", table3[Datasource],

"Column1", table3[computername],

"Column2", blank(),

"Column3", table3[serialnumber],

"Column4", table3[macaddress]

), //closes table3

SELECTCOLUMNS(table4,

"Datasource", table4[Datasource],

"Column1", table4[computername],

"Column2", blank(),

"Column3", blank(),

"Column4", blank()

), //closes table4

SELECTCOLUMNS(table5,

"Datasource", table5[Datasource],

"Column1", table5[computername],

"Column2", blank(),

"Column3", blank(),

"Column4", blank()

), //closes table5

SELECTCOLUMNS(table6,

"Datasource", table6[Datasource],

"Column1", blank(),

"Column2", table6[computerid],

"Column3", table6[serialnumber],

"Column4", blank

), //closes table6

SELECTCOLUMNS(table7,

"Datasource", table7[Datasource],

"Column1", blank(),

"Column2", table2[computerid],

"Column3", table2[serialnumber],

"Column4", table2[macaddress]

) //closes table7

)//closes union

) //closes distinct

[v-yilong-msft · Accepted solution]

Hi @Neiners ,

I think you can change your DAX codes. 

CombinedAssets =
DISTINCT(
    UNION(
        SELECTCOLUMNS(
            table1,
            "Datasource", table1[Datasource],
            "Column1", table1[computername],
            "Column2", table1[computerid],
            "Column3", table1[serialnumber],
            "Column4", table1[macaddress]
        ),
        SELECTCOLUMNS(
            table2,
            "Datasource", table2[Datasource],
            "Column1", table2[computername],
            "Column2", table2[computerid],
            "Column3", table2[serialnumber],
            "Column4", table2[macaddress]
        ),
        SELECTCOLUMNS(
            table3,
            "Datasource", table3[Datasource],
            "Column1", table3[computername],
            "Column2", COALESCE(blank(), "N/A"),
            "Column3", table3[serialnumber],
            "Column4", table3[macaddress]
        ),
        SELECTCOLUMNS(
            table4,
            "Datasource", table4[Datasource],
            "Column1", table4[computername],
            "Column2", COALESCE(blank(), "N/A"),
            "Column3", COALESCE(blank(), "N/A"),
            "Column4", COALESCE(blank(), "N/A")
        ),
        SELECTCOLUMNS(
            table5,
            "Datasource", table5[Datasource],
            "Column1", table5[computername],
            "Column2", COALESCE(blank(), "N/A"),
            "Column3", COALESCE(blank(), "N/A"),
            "Column4", COALESCE(blank(), "N/A")
        ),
        SELECTCOLUMNS(
            table6,
            "Datasource", table6[Datasource],
            "Column1", COALESCE(blank(), "N/A"),
            "Column2", table6[computerid],
            "Column3", table6[serialnumber],
            "Column4", COALESCE(blank(), "N/A")
        ),
        SELECTCOLUMNS(
            table7,
            "Datasource", table7[Datasource],
            "Column1", COALESCE(blank(), "N/A"),
            "Column2", table7[computerid],
            "Column3", table7[serialnumber],
            "Column4", table7[macaddress]
        )
    )
)

 You can use COALESCE function and "N/A"  placeholder.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.