To create table using distinct values and using the filter to remove blanks

[RJS849]

I need to create a table from two tables by combining distinct values but at the same time using filter to remove any blank values

NEWTable1 = DISTINCT(UNION(VALUES(Table1[Dept_ID]), VALUES(Table2[DeptID])))

When it create this new table "NEWTable1", it needs to filter out blank values from Table1 and Table2 which have Dept_ID or DeptID blanks. So that the new column created from these two tables won't have any blank values.

Table1

A100

A101

A103

A104

Table2

B201

B204

B205

B205

NewTable1

A100

A101

A103

A104

B201

B204

B205

B205

[v-yulgu-msft · Accepted solution]

Hi @RJS849,

Please try:

NEWTable1 =
FILTER (
    DISTINCT ( UNION ( VALUES ( Table1[Dept_ID] ), VALUES ( Table2[DeptID] ) ) ),
    [Dept_ID] <> BLANK ()
)

Best regards,

Yuliana Gu

[Ashish_Mathur]

Hi,

You can solve it using thise code in the Query Editor

let
    Source = Table.Combine({Table1, Table2}),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Codes] <> "")),
    #"Removed Duplicates" = Table.Distinct(#"Filtered Rows")
in
    #"Removed Duplicates"