Create dimension table from 2 different tables

[PBI_newuser]

Hi, how to create a dimension table from 2 different tables without including the (Blank) value?

Below doesn't work as it includes (Blank) as one of the value which cause many to many relationship.

Country = distinct(union(all(TableA[Country]),all(TableB[Country])))

TableA:

Country

(Blank)

USA

Canada

Guam

TableB:

Country

USA

Puerto Rico

Australia

Dim_Table:

Country

USA

Canada

Guam

USA

Puerto Rico

Australia

[whereismydata]

Hi @PBI_newuser 

not sure if the most elegant solution, but it works:

Country = 
VAR vTableA = SELECTCOLUMNS('Table A',"Country",'Table A'[Country])
VAR vTableAFilter = FILTER(vTableA,[Country] <> BLANK() && [Country] <> "(Blank)" && [Country] <> "" && [Country] <> "" && [Country] <> "null")
VAR vTableB = SELECTCOLUMNS('Table B',"Country",'Table B'[Country])
VAR vTableBFilter = FILTER(vTableB,[Country] <> BLANK() && [Country] <> "(Blank)" && [Country] <> "" && [Country] <> "" && [Country] <> "null")
VAR vUnion = UNION(vTableAFilter,vTableBFilter)

return vUnion

[whereismydata · Accepted solution]

updated version:

Country = 
VAR vFilter = {"","null","(Blank)",BLANK()}
VAR vTableA = SELECTCOLUMNS('Table A',"Country",'Table A'[Country])
VAR vTableAFilter = FILTER(vTableA,NOT( [Country] in vFilter))
VAR vTableB = SELECTCOLUMNS('Table B',"Country",'Table B'[Country])
VAR vTableBFilter = FILTER(vTableB,NOT( [Country] in vFilter))
VAR vUnion = UNION(vTableAFilter,vTableBFilter)

return vUnion

This is a more dynamic approach,

1) select define values

2) select columns from source table A | B

3) Filter vTableA|B

4) Union

[PBI_newuser]

Thank you so much @whereismydata ! It works. 

[whereismydata]

Hi, in DAX or PowerQuery?

In PQ you could append those two table and then remove duplicates

[PBI_newuser]

Hi @whereismydata , I wanna append two tables in DAX.