JOIN to another table based on condition

[vishy86]

Hi,

I have a dataset in Power BI with Policy Number, Flag and Program Code i.e. 3 separate columns.

There is another excel table with columns Flag, Policy No/Program Code and other attributes.

I need my current Power BI dataset to JOIN to this excel table based on a condition.

- If Flag is Y then join using Policy Number and Flag to return the other attributes.

- If Flag is N then join using Program Code and Flag to return the other attributes.

Is it possible to do this using one dataset itself so that eventually one report can cater to all instead of two separate reports.

Thanks,

Vishy

[v-shex-msft]

Hi @vishy86 ,

I'd like to suggest you enter to query edit and use 'unpivot columns' on attribute columns of these tables, then you can union two tables to one.

Unpivot columns (Power Query) 

Regards,

Xiaoxin Sheng

[vishy86]

Hi @v-shex-msft ,

Both the tables do not have the same number of columns hence I cannot do Union on the tables.

I tried splitting the table into 2 different tables one with Policy No and one with Program Code, not sure this is working correctly either.

Regards,

Vishy

[v-shex-msft]

Hi @vishy86 ,

If you can please share some dummy data with similar data structure and expected result to help us clarify your requirement and test on it.

How to Get Your Question Answered Quickly  

Notice: please do mask or remove sensitive data before share.
Regards,

Xiaoxin Sheng

[amitchandak]

You can populate a column in one table using another table. Only this is you need to use aggregate functions 

Refer

Max Sales order id = Maxx(filter(sales,Sales[item_id]='Item'[Item ID]),Sales[Order Id]) 
Max customer = MAXX(filter(Sales,Sales[Order Id]='Item'[Max Sales order id] && Sales[item_id]='Item'[Item ID]),Sales[Customer ID])

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601