Conditional JOIN using Power Query | A different way of merging in Power BI
HTML-код
- Опубликовано: 26 июл 2024
- In this video I show you how to do a conditional join in Power Query. Instead of using the merge feature from the ribbon I use an alternative way of joining two tables and compare it to a normal merge.
Download file here datatraining.io/powerbi-how-to
--------------------------------
📊 TRAININGS 📊
---------------------------------
Power BI Design 4 Weeks Transformation Program my.datatraining.io/pages/powe...
Power BI Essentials datatraining.io/powerbilearni...
Business User Training datatraining.io/powerbi-busin...
For Custom Trainings and Consulting email directly support@datatraining.io
---------------------------------
⏱️ TIMESTAMPS ⏱️
---------------------------------
0:00 Intro
0:31 Example
1:22 Creating a list
03:07 Filtering a list using List.Select
5:36 Using a filtered list to perform a conditional join
7:00 Fuzzy Match instead of a conditional join
08:47 End
---------------------------------
😍 JOIN 😍
----------------------------------
Join bit.ly/4b453bi
Subscribe bit.ly/31MnQGO
Insta / howtopowerbi
LinkedIn / basdohmen
TikTok / how.to.power.bi
X / howtopowerbi
fb / howtopowerbi
Threads www.threads.net/@howtopowerbi
Newsletter datatraining.io/newsletter
---------------------------------
👇 CHECK THIS OUT! 👇
---------------------------------
💻 My gear amzn.to/47F21Yc
📚 Power BI books MUST READ! amzn.to/3tUfFcj
💡 General books I recommend amzn.to/48YNo33
🎶 Music for my videos www.epidemicsound.com/referra...
🚀 For growing on RUclips: www.tubebuddy.com/bas
🏄 Stuff I use daily amzn.to/3HqfMQ2
* Above are affiliate links, which means at no additional cost to you, if you make a purchase using these links we will receive a small commission. It supports us and helps us to continue making more How to Power BI videos!
Thanks for being a part of this channel and all your support! 💪 🙏
#HowToPowerBI #PowerBI #DataTraining
#powerbidesktop #powerbitraining #powerbideveloper #DAX 
Наука
Looks like I may have to watch this a couple of times
aaah that's where all the views are coming from 😀😉😁 ... thx Paul
Thank you so much! Each video you make is so useful!!
Great content, as always! Thanks for sharing Bas! 🔥
Brilliant! Your teaching style is excellent, clear and concise.
Many thanks!
You're a genius! Love this List function. Much better than using the interface of multi-step merge.
thank you so much! Learning a bit of M opens a whole new world of possibilities 😄
Bas, this is phenomenal! I've been wondering for quite some time if there was a way to do a conditional merge in PQ. I do most of my transformations before bringing my data into PQ, but I was still curious if it was even possible. Your videos are like gold! I love your work - I follow a lot of Power BI channels, and I think your approaches are some of the most creative of anyone that I've seen. I personally would prefer if you removed the music from your videos, as I find it to be distracting from your brilliant content, but you and Chandeep of Goodly fame are easily my first stops if I'm looking for an out-of-the-box approach to a problem.
thank you Nathaniel! that's really nice to hear 😀
This is the coolest trick I've learnt this week!! Super amazing content...Thank you for sharing☺
Glad you liked it Bryan!
So great! Thanks a lot for showing this alternative!
thanks for watching Lex!!!! 😀😊
Bas is always Boss with his methods
Thanks for this. This gets me a bit farther down my road. I am trying to generate a column that will refer to a second table that will evaluate a date range and return a value from that table.
great technique thank you
Ey Buz. Great video. Love all of them.
thank you so much Rafael! Happy to hear you like them 😀
Really helpful, Thank you!
awesome, happy to hear that! thx for watching 😀
Impressive! I had no idea you could do such a thing!
nice to hear David! It can come in very handy as you can adjust the join condition as you like 🙂
You're amazing!!
good one! thx Bas
great video thanks
For a more precise result return, simply merge the two tables upon selecting the merge toggle and utilize, "Inner Join" instead of "Left Join" for an exact match. To me, it seems to be more intuitive, and fewer steps involved. Love to hear your thoughts. (:
Test to Columns option would be easy for getting the vendors I guess. However, I learned something today. Thanks man!
Text to columns, wouldn't return what you want here
excellent tip - Thanks!
Glad it was helpful! thanks for the support!
Thanks you so much, very helpful video for me
Glad to hear this!
Thanks a ton Bas!!
thx for watching 😊
Peak RUclips Entertainment!
Pretty awesome vid, would you happen to have a video or a 'know how' to create a basket analysis using a conditional join? As such joining a table on itself with a not equal to operator so that when the same attribute (that we join on) is thrown into the view for comparability (in a matrix), we get a blank value for each value of a measure, where the same field is compared to, but all other comparitive fields show remaining distribution? if that makes sense? I can do this in under a minute in tableau, but in power bi, I am having difficulty.
amazing stufff Bas
Nice technique!
😊thanks
Very cool. I've always had a problem with the Power Query Merge for large datasets since PBI takes so long to evaluate the results. Does this offer any performance improvements?
will check! although i don't think so... will let you know in one of the upcoming videos
how is the conditional join performance wise? when comparing with merge option? which one faster
Would you know how to do a merge only getting the data that has the highest value of date in a column?
¡Gracias!
thank you so much José for supporting the channel!!! 😀
Great!
Thx Michael!!! 😀
What happens if more than 1 item on the list is present in the description?
Can we control that behavior?
Can you do the same thing with a table instead of a list if you have more than one column you need to reference
Amazing idea, never realized this technique. One question how does the performance compare to the 'regular' merge? And does this performance differ for example if you use a header/detail merge where you just merge on say the invoice number? Normally I use the detail table and then merge the header info I need onto it. Would using this method provide a quicker query step perhaps? Just trying to get a feel for this technique. (Also greetings from Amsterdam ;) geweldige videos)
my guess would be that it is slower, but only one way to find out 🙃 .. I will check (and show it in one of the upcoming videos) .. dankjewel! groetjes 😀
Amazing Video!
Where did you learn to do that??
thanks Bryan!!! 😀 , I needed it once for a project so had to figure it out .. best way to learn
Great technique. Watched it several times to understand. Thank you for creating this. I have a few questions? You said you don't want to load your list every time so you use List.Buffer. What does that do? Does it load one time, a few times, random times, not at all? Why did you add music to your videos? You're teaching complex content and the background music is distracting.
Hopefully i can help. List.Buffer loads your table into PQ memory. That is to say, it wont load every time from the Source, it will read it from the query step. So it can technically improve performance. But with big big datasets, it may even hinder performance. One has to be strategic in where to execute the List.Buffer function.
Thx for helping out Imran! 😀
thx Joe! See explanation from Imran below. I will compare the performance with / without list.buffer in one of the upcoming videos
Thank You sir! I am learnef a great deal from your content, I imagine you must be very busy replying to hundreds of comments! Thank you for your continued support!
Great content, but just a bit of feedback - maybe have the Power BI area large on the screen and less of you, atm its 60/40 to you, would be better 70/30 to Power BI
Still good to get the human touch - but want to focus more on what you're trying to do, and not just you.
appreciate the feedback! will keep it in mind for the next ones
performance wise, which one is better?
i actually wanted to ask the same question :)
How did you create the variable vendor_info
Just a request please show complete screen for better understanding
Great 👍. Can we do a conditional aggregation in the model?
sounds doable, can you explain a bit more of what you are looking for
@@HowtoPowerBI
Great!
Measures:
Total Sales := SUM(Sales [Amount])
This is a straightforward aggregation mapping, this hits the agg table.. whereas, requirement is
Total Discounted Sales :=
CALCULATE ([Total Sales], Sales[Discount] > 0)
This fails to hit the agg table even if both amount and discount column is available and mapped in agg table.
Hope this helps.
Is there a way i can pay you for this video because you literally saved my life.
no need to pay :) I am very happy it helped you! more, hopefully useful videos are coming !
@@HowtoPowerBI Is it possible to have multiple columns when including text.contains
How both options impact performance?
probably slower, but I'll still make a video on that 😉
Yes, I was also wondering about performance. Really, I'm looking for something to improve the performance of merges. Cool video though. I can definitely see how this could be useful when wanting to merge in a particular way.
Do you teach m query in detail
Its kinda good.... really hard to follow when you keep going backwards with your steps. Makes it hard to understand what exactly is going on. Especially when I can only see a snippet of what you are looking at. I struggled when you started going "vendor, vendor name" back a step "Vendors, vendor name, vendor" got to the point i had no clue where it came from. Idk if the list exists still. idk where it got its info from and most importantly, idk how to get the data i need into my table.
Like... honestly extremley frustrating to know have a clue how you can still be using vendorname after you showed us you compiled it into a list. Obviously i missed something small but my data is different than yours therefore i cant do this exactly the way you did. Maybe has something to do with buffering i really dont know. Some of the shorter, more complex things like buffering, and putting a function in there, and "go to" whatever that is, all should come with some sort of explination of what its doing why you are using it and how i can understand it.
I've a big challenge for you ;) I should do a dinamic Groupby for clusters of customers. I.e. I've 2 years, 2021 and 2020, I want do a count of customers that had invoiced more than 50k. However I want compare the same time frame and this is the big issue. For 2020 I've the entire year, for 2021 instead I've a dinamic time frame (for now June). How can I do a group by the selected months? If I want count how many customers have invoiced more than 50k and I keep (in the group by or summarize) the column "months", he checks each line for month & customer. For example if ANGELO has invoiced 10k on February and 40k on March he didn't count it since on a single month he didn't invoiced more than 50k but I want count it since the total is 50k! A stupid solution is to create as many groupings as many months for 2020 and 2021 but it is very annoying job.
Do you have any different idea?
Sorry for this long comment, I hope it is clear
Hi Angelo, thx for the idea! will make a video on it how to solve it🙂
@@HowtoPowerBI I love you man! I'm looking forward for your video.
here there is a picture that showed what i want obtain, number of customers dived by different clusters. For this one i've done a group by customer, but i cannot keep month (otherwise he didn't summarize all invoices of the same customer) so i cannot make it dinamic with a month filter ibb.co/WywWxpj
Further elements could be a group by products and months, and country and so on.
Bas...you're a life safer..!
Thanks Anush, happy i could help!
Please show the entire query editor interface... I find this immensely confusing... Also, the pbix file doesn't open...
I checked the download link / file. works fine for me .. what error do you get?
@@HowtoPowerBI Many thanks for reverting... The error message says, "Could not find a part of the path 'C:\Users\basdo\OneDrive - Data Training eK\Desktop\conditional joins.xlsx'."
@@ajieapen the excel file is also provided in the download . You can go to the source step and link it to that excel file
@@HowtoPowerBI Ah.. alright... Thanks!