Exceptionally presented in bite-size steps! Maybe, it would be better to start the video with demonstration of the downsides of the fuzzy merge that was done at the end of the video, that would set the context for the lesson. However, I knew what I was looking for and this technique absolutely saved my day! Thank you so much!
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.
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. (:
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?
Great content, now my question is, up to what size would those two options make sense? would that also work if one table has for example 30000 rows and the other one 1000 or are those solutions more for smaller tables?
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. 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.
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!
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.
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 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 😀
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 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.
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.
@@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'."
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 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.
Looks like I may have to watch this a couple of times
aaah that's where all the views are coming from 😀😉😁 ... thx Paul
Exceptionally presented in bite-size steps! Maybe, it would be better to start the video with demonstration of the downsides of the fuzzy merge that was done at the end of the video, that would set the context for the lesson. However, I knew what I was looking for and this technique absolutely saved my day! Thank you so much!
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 😄
Brilliant! Your teaching style is excellent, clear and concise.
Many thanks!
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 😀
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. (:
excellent tip - Thanks!
Glad it was helpful! thanks for the support!
Thank you so much! Each video you make is so useful!!
This is the coolest trick I've learnt this week!! Super amazing content...Thank you for sharing☺
Glad you liked it Bryan!
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
Bas is always Boss with his methods
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 🙂
how is the conditional join performance wise? when comparing with merge option? which one faster
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
Would you know how to do a merge only getting the data that has the highest value of date in a column?
What happens if more than 1 item on the list is present in the description?
Can we control that behavior?
¡Gracias!
thank you so much José for supporting the channel!!! 😀
Thanks
thanks so much for supporting the channel!!! really appreciate it :)
Great content, now my question is, up to what size would those two options make sense? would that also work if one table has for example 30000 rows and the other one 1000 or are those solutions more for smaller tables?
Ey Buz. Great video. Love all of them.
thank you so much Rafael! Happy to hear you like them 😀
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.
Can you do the same thing with a table instead of a list if you have more than one column you need to reference
So great! Thanks a lot for showing this alternative!
thanks for watching Lex!!!! 😀😊
Peak TH-cam Entertainment!
Really helpful, Thank you!
awesome, happy to hear that! thx for watching 😀
Great content, as always! Thanks for sharing Bas! 🔥
good one! thx Bas
performance wise, which one is better?
i actually wanted to ask the same question :)
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!
How did you create the variable vendor_info
You're amazing!!
Thanks a ton Bas!!
thx for watching 😊
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.
great video thanks
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 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 😀
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
great technique thank you
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
Nice technique!
😊thanks
amazing stufff Bas
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.
Great!
Thx Michael!!! 😀
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.
Just a request please show complete screen for better understanding
Would be a lot more helpful if we see a bigger pane of the power BI query pane instead
Thx for the feedback
Do you teach m query in detail
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
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!
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!
Id never realused that " each" was invoking a function...
Thanks you so much, very helpful video for me
Glad to hear this!