Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

How to match and get value from other table

Hi Users,

I have to create calculated column in source table where the result would be from look up table provided below.

I have to filter, CurrencyNamre = Source table "C" column, Financial year = Source table "A" column, Filter P1-P12  by  Source table "B" column

Source table sample below.

 

Thanks.

Need Help
  • v-deddai1-msft's avatar
    v-deddai1-msft
    5 years ago

    Hi Anonymous ,

     

    You can use calculated column by dax, but you need to unpivot your first table first. I suggest you use the following m query. 

     

    My sample data:

    Then you can create a cutom function in m-query:

    = (X,Y,Z) =>Record.Field(Table{[FY = X,Currency = Y]},Z)

     

    You can use the function to add custom column in query editor:

     

    For more details, please refer to the pbix file.

     

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

     

    Best Regards,

    Dedmon Dai

     

     

     

2 Replies

  • v-deddai1-msft's avatar
    v-deddai1-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi Anonymous ,

     

    You can use calculated column by dax, but you need to unpivot your first table first. I suggest you use the following m query. 

     

    My sample data:

    Then you can create a cutom function in m-query:

    = (X,Y,Z) =>Record.Field(Table{[FY = X,Currency = Y]},Z)

     

    You can use the function to add custom column in query editor:

     

    For more details, please refer to the pbix file.

     

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

     

    Best Regards,

    Dedmon Dai