Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Everyone,
I need some advice or help to write a M syntax that matches a date that fall within a date range in another table.
E.g.
Table 1
ID | Production Date
1 | 2015
Table 2
ID | Production Start | Production End | Project Name
1 | 2008 | 2018 | DodoBird
Basically, if Table 1 production date falls within Table 2. I can merge and expand to give me "DodoBird".
http://radacad.com/dates-between-merge-join-in-power-query
I tried the above method with a subset data and somehow it expanded to about 1 mil rows (the initial row without applying this method was 200+ rows)
Thanks in Advance 🙂
Solved! Go to Solution.
Hi @Rookarumba,
You could first merge two tables with Full Outer Join, then, filter rows based on specific condition.
Add below filter code in Advanced editor.
#"Filtered Rows" = Table.SelectRows(#"Expanded Table2", each [Production Date] >= [Table2.Production Start] and [Production Date] <= [Table2.Production End])
Best regards,
Yuliana Gu
Hi @Rookarumba,
You could first merge two tables with Full Outer Join, then, filter rows based on specific condition.
Add below filter code in Advanced editor.
#"Filtered Rows" = Table.SelectRows(#"Expanded Table2", each [Production Date] >= [Table2.Production Start] and [Production Date] <= [Table2.Production End])
Best regards,
Yuliana Gu
What if you wanted to put in fixed dates like 1/1-2018 to 12/31/2018?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
101 | |
76 | |
64 | |
63 |
User | Count |
---|---|
141 | |
104 | |
101 | |
80 | |
67 |