Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Create dimension table from 2 different tables

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
Need Help
  • whereismydata's avatar
    whereismydata
    4 years ago

    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

     

     

5 Replies

  • whereismydata's avatar
    whereismydata
    Icon for Resolver IV rankResolver IV
    4 years ago

    Hi Anonymous 

     

    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's avatar
      whereismydata
      Icon for Resolver IV rankResolver IV
      4 years ago

      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

       

       

  • whereismydata's avatar
    whereismydata
    Icon for Resolver IV rankResolver IV
    4 years ago

    Hi, in DAX or PowerQuery?

     

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