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.

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

sk007 , How will the row will get decided? both of them need have pay or add an index column in the power query

 

Power Query- Index Column: https://youtu.be/NS4esnCDqVw

 

 

Then have a column in Table on

 

COlumn =

Var _1 = maxx(filter(Table2, Table1[pay] =Table2[Pay]) ,[n_date]) // use index in place of pay if needed

Var _2 = maxx(filter(Table2, Table1[pay] =Table2[Pay]) ,[p_date])// use index in place of pay if needed

return

if([Pay] in {"A", "B"} , _1,_2)