Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Neiners
Helper II
Helper II

Union multiple tables with uneven columns and then concatenatex rows together

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

 

      

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

View solution in original post

1 REPLY 1
Anonymous
Not applicable

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.

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.