Filtering columns from different sheets Say 2 excel sheets.

[sskoushik]

Hello.

I am new to PowerBI, I am trying to filter two same  columns from two different sheets. How can I do that. Let me explain you my problem clearly.

say for example I have two excel sheets T1 and T2.

T1                          T2

C1    C2                 C1       C2   C3   C4

134  245               134     245 .......   .......

123  345               123    345  ......    ......

234  453               234     453  .......   .......

if c1 and c2 from T1 is equal to c1 and c2 in T2, then i want the complete row if it matches.If that doesnt match then leave that and continue. Is there any possibility for doing this operation?

Thanks in advance.

Sumanth.

[v-yulgu-msft · Accepted solution]

Hi @sskoushik,

Suggestion1: In query editor mode

In this case we want to merge queries. From the left pane of Query Editor we select the query into which we want the other query to merge. Then select Merge Queries -> Merge Queries as New from the Home tab on the ribbon.

Then, you will get a new merged table combining the original two tables, now you can delete some unnecessary columns and rename columns.

Suggestion2: Using DAX

Apart from combining tables in Query Editor, we can create a new calculated table in visual mode. Please refer to below DAX formula:

T3 =
CALCULATETABLE (
    T2,
    FILTER (
        T2,
        LOOKUPVALUE ( T1[C1], T1[C1], T2[C1], T1[C2], T2[C2] ) <> BLANK ()
    )
)

Best regards,
Yuliana Gu