Forum Discussion
Inner join with condition
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.
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 FindDefectnumberThen 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.
5 Replies
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 FindDefectnumberThen 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.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"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"There are no common columns between both sheets so i need to add condition for the join itself
Add a new column and sued Table.SelectRows to filter the rows on sheet 2 with the below condition
where sheet1.CreateDate <= Sheet2.MonthEnd
