- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Add a new column and sued Table.SelectRows to filter the rows on sheet 2 with the below condition
where sheet1.CreateDate <= Sheet2.MonthEnd
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
There are no common columns between both sheets so i need to add condition for the join itself
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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"
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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"
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Microsoft Fabric Community Conference 2025
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
Subject | Author | Posted | |
---|---|---|---|
07-05-2024 04:09 PM | |||
01-17-2018 03:42 AM | |||
10-03-2024 10:26 AM | |||
11-07-2024 10:26 PM | |||
12-10-2022 09:02 PM |