Forum Discussion

sskoushik's avatar
sskoushik
New Member
9 years ago
Solved

Filtering columns from different sheets Say 2 excel sheets.

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's avatar
    v-yulgu-msft
    9 years ago

    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

1 Reply

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    9 years ago

    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