Forum Discussion

RJS849's avatar
RJS849
Frequent Visitor
8 years ago
Solved

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

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(Table...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi RJS849,

     

    Please try:

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

    Best regards,

    Yuliana Gu

Ashish_Mathur's avatar
Ashish_Mathur
Super User
8 years ago

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"