Forum Discussion

Ski900's avatar
Ski900
Helper II
7 years ago
Solved

Help creating a dimension using two columns with blank values removed

As the title says I am trying to create a dimension/attribute table using distinct, nonblank values from two columns, each from separate tables. For example, using the sample data below, Dimension[As...
Need Help
  • Ski900's avatar
    Ski900
    7 years ago

    Thanks for the reply! I was not able to use ALLNOBLANKROW(c) because the function was expecting a table, and it was treating c as a column. However, adjusting your code slightly I came up with this that works

    Dimension = 
var a = SELECTCOLUMNS(FILTER(Table_A, ISBLANK(Table_A[Assigned To]) = FALSE), "Assigned To", Table_A[Assigned To])
var b = SELECTCOLUMNS(FILTER(Table_B, ISBLANK(Table_B[Assigned To]) = FALSE), "Assigned To", Table_B[Assigned To])
var c = DISTINCT(UNION(a,b))

return c
Ashish_Mathur's avatar
Ashish_Mathur
Super User
7 years ago

Hi,

 

Ensure that both columns have the same heading.  Using the Query Editor, simply append both Tables.  Right click on the column and under Transform Data, select Upper case, Trim and clean.  Right click on the column and click on Remove Duplicates.  In the Filter drop down, unchek the Blanks checkbox.