Join table with several columns and condition

[Alpha427]

Hello everyone,

I am facing a special case with Power BI Desktop.

Here is the situation, I have two tables with some columns :

+--------------+                +--------------+
|                         |                |                        |
|                         |                |                        |
|    TableA           |                |    TableB          |
|                         |                |                        |
|                         |                |                        |
|                         |                |                        |
++-------------+               ++-------------+
 |                                           |
 +---+columnA                    +--+columnA
 |                                           |
 +---|columnB                      +--+columnDate
 |                                           |
 +---|columnC                     +--+column...
 |
 +---+column...

I want to create a relation between these two tables, with this condition :

TableA.columnA = TableB.columnA and TableB.columnDate between TableA.columnB and TableA.columnC

I am a beginner on Power BI and I have no idea of how to do this..

Could you help me ?

[Alpha427]

I didn't find element which could help me in what you sent 😕

I have found this topic :

https://community.powerbi.com/t5/Desktop/Unable-to-merge-or-join-tables-based-on-condition/m-p/27320...

+ this page : https://msdn.microsoft.com/en-us/query-bi/m/table-selectrows

So, if I follow them, I should have a formula like this ?

Table.SelectRows(TableA, (TableA) => TableA[columnA] = TableB[columnA] and TableB[columnDate] >= TableA[columnB] and TableB[columnDate] <= TableA[columnC])[TableA]

I don't have the possibility to test this formula before tomorrow. However, will this only display TableA columns or TableB's too ?

[Alpha427]

Up

[Alpha427]

Does someone have an idea ? I'm still searching on the net but I can't find how to join the tables...

I'm on an idea currently. The idea is merging the two table (left join), and then apply a filter to fulfill the condition (a date between the two others). I can merge the table via edit queries, but how to apply the filter then ?

[v-yuta-msft]

Hi Alpha427,

About create and manage relationships in power bi, please refer to this documentation: https://docs.microsoft.com/en-us/power-bi/desktop-create-and-manage-relationships. In addtion, if you want to merge tables in power bi, please refer to this documentation: https://docs.microsoft.com/en-us/power-bi/desktop-shape-and-combine-data#combine-data.

Regards,

Jimmy Tao