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