Conditional JOIN using Power Query | A different way of merging in Power BI
Рет қаралды 56,948
Күн бұрын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 KZfaq: 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
@fakename4683 3 жыл бұрын
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.
@10ozGold 2 жыл бұрын
You're a genius! Love this List function. Much better than using the interface of multi-step merge.
@HowtoPowerBI 2 жыл бұрын
thank you so much! Learning a bit of M opens a whole new world of possibilities 😄
@nboisen Жыл бұрын
Brilliant! Your teaching style is excellent, clear and concise.
@HowtoPowerBI Жыл бұрын
Many thanks!
@alexrivera6524 3 жыл бұрын
Thank you so much! Each video you make is so useful!!
@paulj625 3 жыл бұрын
Looks like I may have to watch this a couple of times
@HowtoPowerBI 3 жыл бұрын
aaah that's where all the views are coming from 😀😉😁 ... thx Paul
@eFilet-O-Fish 10 ай бұрын
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. (:
@kishore_p93 3 жыл бұрын
Great content, as always! Thanks for sharing Bas! 🔥
@bryanabreo 2 жыл бұрын
This is the coolest trick I've learnt this week!! Super amazing content...Thank you for sharing☺
@HowtoPowerBI 2 жыл бұрын
Glad you liked it Bryan!
@nathanielklein6326 2 жыл бұрын
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.
@HowtoPowerBI 2 жыл бұрын
thank you Nathaniel! that's really nice to hear 😀
@lazydrain 2 жыл бұрын
Would you know how to do a merge only getting the data that has the highest value of date in a column?
@WolFX_FPS 2 жыл бұрын
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.
@gauthamk1485 3 жыл бұрын
Bas is always Boss with his methods
@shamf6624 Жыл бұрын
how is the conditional join performance wise? when comparing with merge option? which one faster
@ppmendonca1 2 жыл бұрын
What happens if more than 1 item on the list is present in the description? Can we control that behavior?
@lexrobe_fly 2 жыл бұрын
So great! Thanks a lot for showing this alternative!
@HowtoPowerBI 2 жыл бұрын
thanks for watching Lex!!!! 😀😊
@davidcarr7793 2 ай бұрын
Can you do the same thing with a table instead of a list if you have more than one column you need to reference
@mayursharma4644 2 жыл бұрын
Test to Columns option would be easy for getting the vendors I guess. However, I learned something today. Thanks man!
@HowtoPowerBI 2 жыл бұрын
Text to columns, wouldn't return what you want here
@sadyaz64 3 жыл бұрын
great technique thank you
@opod84 3 жыл бұрын
Impressive! I had no idea you could do such a thing!
@HowtoPowerBI 2 жыл бұрын
nice to hear David! It can come in very handy as you can adjust the join condition as you like 🙂
@arnold2706 2 жыл бұрын
How did you create the variable vendor_info
@rerangelt 3 жыл бұрын
Ey Buz. Great video. Love all of them.
@HowtoPowerBI 3 жыл бұрын
thank you so much Rafael! Happy to hear you like them 😀
@guridhillon4356 3 жыл бұрын
Really helpful, Thank you!
@HowtoPowerBI 3 жыл бұрын
awesome, happy to hear that! thx for watching 😀
@MarkDarbyshireyepitsme 3 жыл бұрын
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?
@HowtoPowerBI 3 жыл бұрын
will check! although i don't think so... will let you know in one of the upcoming videos
@xodrinker 3 жыл бұрын
good one! thx Bas
@user-zr4kx5ow9v Жыл бұрын
great video thanks
@Duyhiep115 Жыл бұрын
Thanks you so much, very helpful video for me
@HowtoPowerBI Жыл бұрын
Glad to hear this!
@user-mp6hn9vr2o 5 ай бұрын
You're amazing!!
@PedroCabraldaCamara 3 жыл бұрын
amazing stufff Bas
@deejohn064 Жыл бұрын
excellent tip - Thanks!
@HowtoPowerBI Жыл бұрын
Glad it was helpful! thanks for the support!
@cannanam 3 жыл бұрын
Nice technique!
@HowtoPowerBI 3 жыл бұрын
😊thanks
@thealchemist6133 2 жыл бұрын
Thanks a ton Bas!!
@HowtoPowerBI 2 жыл бұрын
thx for watching 😊
@tremolony4924 6 ай бұрын
Peak KZfaq Entertainment!
@overnightgrowth 3 жыл бұрын
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)
@HowtoPowerBI 3 жыл бұрын
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 😀
@CA_Sandeep_Modi Жыл бұрын
Just a request please show complete screen for better understanding
@IoriYagamiKOF98 3 жыл бұрын
performance wise, which one is better?
@cristianprifti 3 жыл бұрын
i actually wanted to ask the same question :)
@bryanchaves5691 3 жыл бұрын
Amazing Video! Where did you learn to do that??
@HowtoPowerBI 3 жыл бұрын
thanks Bryan!!! 😀 , I needed it once for a project so had to figure it out .. best way to learn
@joseantoniolopezmesa6807 2 жыл бұрын
¡Gracias!
@HowtoPowerBI 2 жыл бұрын
thank you so much José for supporting the channel!!! 😀
@mshparber 3 жыл бұрын
Great!
@HowtoPowerBI 3 жыл бұрын
Thx Michael!!! 😀
@Bharath_PBI 3 жыл бұрын
Great 👍. Can we do a conditional aggregation in the model?
@HowtoPowerBI 3 жыл бұрын
sounds doable, can you explain a bit more of what you are looking for
@Bharath_PBI 3 жыл бұрын
@@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.
@alesiya8841 2 жыл бұрын
How both options impact performance?
@HowtoPowerBI 2 жыл бұрын
probably slower, but I'll still make a video on that 😉
@leebecker8255 2 жыл бұрын
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.
@oddy3340 3 жыл бұрын
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.
@HowtoPowerBI 3 жыл бұрын
appreciate the feedback! will keep it in mind for the next ones
@joenorbe2711 3 жыл бұрын
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.
@UlyssesHaq 3 жыл бұрын
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.
@HowtoPowerBI 3 жыл бұрын
Thx for helping out Imran! 😀
@HowtoPowerBI 3 жыл бұрын
thx Joe! See explanation from Imran below. I will compare the performance with / without list.buffer in one of the upcoming videos
@UlyssesHaq 3 жыл бұрын
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!
@gravestoner2488 Жыл бұрын
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.
@gravestoner2488 Жыл бұрын
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.
@sujit3375 Жыл бұрын
Do you teach m query in detail
@ammarz.3654 2 жыл бұрын
Is there a way i can pay you for this video because you literally saved my life.
@HowtoPowerBI 2 жыл бұрын
no need to pay :) I am very happy it helped you! more, hopefully useful videos are coming !
@ammarz.3654 2 жыл бұрын
@@HowtoPowerBI Is it possible to have multiple columns when including text.contains
@carax006 3 жыл бұрын
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
@HowtoPowerBI 3 жыл бұрын
Hi Angelo, thx for the idea! will make a video on it how to solve it🙂
@carax006 3 жыл бұрын
@@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.
@ajieapen 2 жыл бұрын
Please show the entire query editor interface... I find this immensely confusing... Also, the pbix file doesn't open...
@HowtoPowerBI 2 жыл бұрын
I checked the download link / file. works fine for me .. what error do you get?
@ajieapen 2 жыл бұрын
@@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'."
@HowtoPowerBI 2 жыл бұрын
@@ajieapen the excel file is also provided in the download . You can go to the source step and link it to that excel file
@ajieapen 2 жыл бұрын
@@HowtoPowerBI Ah.. alright... Thanks!
@anushkedlaya Жыл бұрын
Bas...you're a life safer..!
@HowtoPowerBI Жыл бұрын
Thanks Anush, happy i could help!
How to Power BI
Рет қаралды 15 М.
Kirill Multitool
Рет қаралды 74 МЛН
How to Power BI
Рет қаралды 251 М.
Excel On Fire
Рет қаралды 27 М.
How to Power BI
Рет қаралды 29 М.
excelisfun
Рет қаралды 639 М.