Inner join with condition

[Vidhyashree153]

Hello,
I have a problem about joining two tables with condition.
Please help.

Eg.
Table : Sheet1
field : Defectnumber, CreateDate

Table : Sheet2
field : MonthStart, MonthEnd

i have to join both the sheets and get the inventory data of defects

with condition likes this :
Sheet1 inner join Sheet2
where sheet1.CreateDate <= Sheet2.MonthEnd

Below screenshot is from Tableau is there any way i can replicate this in PowerBI

Please help me

Thank you.

[Anonymous · Accepted solution]

Hi @Vidhyashree153 ,

You can try this way:
Here is my sample data:

Use this M code to create a Blank Query:

let
    FindDefectnumber = (MonthStart as date, MonthEnd as date) =>
    let
        FilteredRows = Table.SelectRows(Sheet1, each [CreateDate] >= MonthStart and [CreateDate] <= MonthEnd),
        DefectnumberCol = Table.Column(FilteredRows, "Defectnumber")
    in
        DefectnumberCol
in
    FindDefectnumber

Then back to Sheet2 and create a Invoke Custom Function:

Expand columns:

Output:

Then you can inner join Sheet1 and Sheet2 with column Defectnumber and Related Number:

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

[Omid_Motamedise]

Add a new column and sued Table.SelectRows to filter the rows on sheet 2 with the below condition

where sheet1.CreateDate <= Sheet2.MonthEnd

[Vidhyashree153]

There are no common columns between both sheets so i need to add condition for the join itself

[Muhammad_Ahmed]

You can first apply the inner join using the merge queries in power query and then create an additional column to filter it out on that basis.
if [CreateDate] >= [MonthStart] and [CreateDate] <= [MonthEnd] then "Include" else "Exclude"

[Muhammad_Ahmed]

You can first apply the inner join using the merge queries in power query and then create an additional column to filter it out on that basis.
if [CreateDate] >= [MonthStart] and [CreateDate] <= [MonthEnd] then "Include" else "Exclude"