Have you heard about the new "Semi Joins" available in #PowerQuery? They are similar to the Anti Joins in that they deliver what records match between 2 tables, but they don't bring over the values. To help explain the new Left and Right Semi Joins, I added 2 new recipe cards to my Power Query Recipes collection. 👩🍳 You can purchase your copy of the full collection on our Skillwave Training site: https://lnkd.in/gZMvqdKc 🎥 And if you're enrolled in Power Query Academy, #SelfServiceBI Academy, or #Excel Fundamentals Academy, you can see them in action with a new lesson that I added to each course!
No - what version do these come in? I still have the old ones. This is what accountants need for reconciliations!
That's a really handy new feature. In accountancy I'm often using Joins to check if something is present in another list without actually wanting to lookup the values.
Founder, BizTrix | Director, Finance
1moKen Puls Thanks for sharing. I never inspected Power Query's "semi" join until your post. So, I did a quick test using the data below in Excel and rebuilt it using SQL to compare the "anti" vs. "semi" join. In case the original SQL model is helpful for others who may read this, I included the SQL below. Note the CTE structure, so position the final select ([0], [1], or [2]) in the correct location if you plan to view all three outputs. (After thinking through this, I actually don't like the "LeftSemi" label in Power Query because this is really an inner join that select from A only, but that's for another day). with tblA as ( select 1 id union all select 2 union all select 3 ) , tblB as ( select 1 id union all select 4 union all select 5 ) -- [0] left "anti" join select a.id , b.id from tblA a left join tblB b on a.id = b.id where b.id is null ; -- [1] "semi" join (using left) select a.id , null id -- or exclude the field altogether from tblA a left join tblB b on a.id = b.id where b.id is not null ; -- [2] "semi" join (using inner) select a.id , null id -- or exclude the field altogether from tblA a join tblB b on a.id = b.id ;