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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Neiners
Helper I
Helper I

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
v-yilong-msft
Community Support
Community Support

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
v-yilong-msft
Community Support
Community Support

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Kudoed Authors