Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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.
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
Solved! Go to 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.
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.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
10 | |
9 | |
8 | |
7 | |
6 |
User | Count |
---|---|
14 | |
12 | |
11 | |
9 | |
9 |