Forum Discussion

sk007's avatar
sk007
Regular Visitor
4 years ago
Solved

Switch statement for columns from different tables

I have two tables like this:  Table1   Table2   I want to create a new column in either of the table with condition like this:  If pay is Table1[pay] = "A" or Table1[pay] = "B" then, ...
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi sk007 ,

     

    If you want to calculate between Table1 and Table2, there should be a keycolumn to relate two tables. Or we couldn't determind which row each pay will be related to.

    I agree with amitchandak, you can refer to his video to add an [Index] column in two tables in Power Query Editor. Please make sure you have sort two tables as you want before you add an [Index] column.

    Relationship:

    Then your calculation will be easy.

    Calculated Column:

    NewDate =
SWITCH (
    TRUE (),
    Table1[pay] IN { "A", "B" }, RELATED ( Table2[n_date] ),
    Table1[pay] IN { "C", "D" }, RELATED ( Table2[p_date] )
)

     Result is as below.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi sk007 ,

 

If you want to calculate between Table1 and Table2, there should be a keycolumn to relate two tables. Or we couldn't determind which row each pay will be related to.

I agree with amitchandak, you can refer to his video to add an [Index] column in two tables in Power Query Editor. Please make sure you have sort two tables as you want before you add an [Index] column.

Relationship:

Then your calculation will be easy.

Calculated Column:

NewDate =
SWITCH (
    TRUE (),
    Table1[pay] IN { "A", "B" }, RELATED ( Table2[n_date] ),
    Table1[pay] IN { "C", "D" }, RELATED ( Table2[p_date] )
)

 Result is as below.

 

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.