Hello Ruth and Thank You for everything you do for the community! The technique you've shown with a custom function is great but it's not very useful on larger data sources. I've tested both (Merge and Custom Function) solutions and actually measured the refresh times on Data Sources 10x and 100x bigger than the original source and it seems that on larger data sources the Merge solution is actually waaaaay better (over 90 x better) than the custom function. I've also tried buffering the pricing table inside the function and it was better than without it ... but still the merge option using UI is the CLEAR WINNER
Thanks for taking the time to test this! In both cases PQ needs to iterate through both tables, but I expected it to perform in similar way? Now we know!!
Thank you for showing me this! It enabled me to do the conditional join without actually merging the two full tables. However, I was trying to find a method which could do it faster than the merge (and filtering afterwards), but as Cristian mentions, it is way slower indeed. This custom function is a cool technique, but unfortunately not useful in my case.
What us UI? I tried to apply this custom function and it taking me forever to load. I have two criteria, name and timestamp. Please suggest other way to merge my table. thanks in advance.
Glad that you are feeling better now! Thank you for sharing the technique! Btw, I was watching the Guy-In-A-Cube live cast this past weekend, and I saw that the community wants you on the show to discuss PQ and M! It would be a blockbuster!! I hope this happens!! 🤞
Thanks Ruth, But I have a scenario where I have to merge two tables , whatever is not matched in the first table I need to match it for next set of condition with the same second table .. Like this I have to continuously check and merge the two tables for 5 different set of conditions and finally give output of all that matched in either of the condition with color formatting for each set of conditions and also not matched at all at the end ..(Left outer join) .. eg.. first merge with addr1 .. for the unmatched rows of first set, match with addr2 .. for the unmatched rows of the previous set match for city, for the unmatched rows of the previous set match for state, for the unmatched rows of the previous set match for ZIP.. The two tables that are merged are around 100,000 rows each .. Help me please..
IS it really happens things out of the screen or behind the instructor? It could be I am too newbie, but this is really annoying into a training video.
Thanks for sharing this experiment with us, Ruth! I think for me, the approach I would take is first normalise the date format in both tables using Date.FromText and wrapping it with Number.From to get the date ID from both tables. Then I perform a merge. But of course, this is a hypothetical situation where Date.FromText would be able to read the two different formats in both Usage and Price tables. Otherwise, I would have to resort to splitting the year, day, and month using Text.Middle and concatenate them back and inferring the data type as type date. Apart from this, I suppose this idea can be used in other scenarios, which I just haven't thought of yet at the moment. I will definitely share with you once I come across that situation. Thanks again, Ruth!
This is super helpful! I've been able to take away a lot from your other videos as well. I'm using this to build hierarchy structure for a chart of accounts. My current plan is invoking the formula for each layer in the hierarchy. It's working really well, but on a smaller set of data. I'll try researching table buffering or other best practice to optimize performance. Any tips would be helpful!
A new subscriber here. Thank you for your contribution. I have a looong way to go (this video was way above my current skillset) but I'm looking forward to learning from you.
Primero que nada espero te mejores de salud y tomes tus medicinas; segundo: es una estupenda solución con la función invocada. Gracias y al pendiente de ti.
Hello Ruth and Thank You for everything you do for the community! The technique you've shown with a custom function is great but it's not very useful on larger data sources. I've tested both (Merge and Custom Function) solutions and actually measured the refresh times on Data Sources 10x and 100x bigger than the original source and it seems that on larger data sources the Merge solution is actually waaaaay better (over 90 x better) than the custom function. I've also tried buffering the pricing table inside the function and it was better than without it ... but still the merge option using UI is the CLEAR WINNER
Thanks for taking the time to test this!
In both cases PQ needs to iterate through both tables, but I expected it to perform in similar way?
Now we know!!
How to merge based on a key column and a date range?
I did try the Table.SelectRows() method and it worked but the refresh is taking forever.
Thank you for showing me this! It enabled me to do the conditional join without actually merging the two full tables.
However, I was trying to find a method which could do it faster than the merge (and filtering afterwards), but as Cristian mentions, it is way slower indeed.
This custom function is a cool technique, but unfortunately not useful in my case.
What us UI? I tried to apply this custom function and it taking me forever to load. I have two criteria, name and timestamp. Please suggest other way to merge my table. thanks in advance.
Hi @@kelvinalmarez5495. UI stands for User Interface
I am always so excited when Ruth says "for this - i am going to build a function"
😂😂
Glad that you are feeling better now! Thank you for sharing the technique!
Btw, I was watching the Guy-In-A-Cube live cast this past weekend, and I saw that the community wants you on the show to discuss PQ and M! It would be a blockbuster!! I hope this happens!! 🤞
Hi Hachi, what a weekend that was! 🤢
For M I would choose Imke or Miguel Escobar, they are amazing!!
Good tip. Merging larger tables in Power Query takes ages. However I'd rather make a custom column in both tables and set a relationship in PBI.
That's what I've been doing.
Thanks, Ruth. I love all your training videos, I really have learned so much from you. 😊
💛💛💛
Woman you rule these issues. What a great teacher too!
Man, glad to 👂
Thank Ruth, for showing us new methods and techniques
As soon as I learn something....so do you ;)
@@CurbalEN Yah me too
You are such a charming teacher when you giggle I giggle too LMAO
Genial, me ayudaste a resolver un problema. Muchas gracias y un saludo grande desde Argentina!
Un placer y saludos!!
Thanks Ruth, But I have a scenario where I have to merge two tables , whatever is not matched in the first table I need to match it for next set of condition with the same second table .. Like this I have to continuously check and merge the two tables for 5 different set of conditions and finally give output of all that matched in either of the condition with color formatting for each set of conditions and also not matched at all at the end ..(Left outer join) .. eg.. first merge with addr1 .. for the unmatched rows of first set, match with addr2 .. for the unmatched rows of the previous set match for city, for the unmatched rows of the previous set match for state, for the unmatched rows of the previous set match for ZIP.. The two tables that are merged are around 100,000 rows each ..
Help me please..
Are you the founder of those formulas???
Thank you Ruth, this video also doubles up as a great intro to functions! Glad you are feeling better.
Hi, what happens if we got two variables?
how to merge many to many data
thank you
Nice video
looking the source file for practice.
I Uploaded it to community downloads in the download center. It is download number 67.
@@CurbalEN got it thanks . Excellent. Well explained.
Fantastic!!!
You may want to Table.Buffer before this step if your data is not 10 rows. Cool technique tho.
Will do :)
Nice! 👍😘
It is!
🤯
Do you have any cool video about custom function? Never used it and well...
Like this one:
m.th-cam.com/video/Ar_fV_oXSNs/w-d-xo.html
I have others, just search the channel:)
IS it really happens things out of the screen or behind the instructor? It could be I am too newbie, but this is really annoying into a training video.
My editing skills are not always the best :(
@@CurbalEN Thank you for the answer. I appreciate it. As I tried to mention that if I am new I need each of the little fragment of the information.
Totally understand!
I didnt understand a thing
Thanks for sharing this experiment with us, Ruth! I think for me, the approach I would take is first normalise the date format in both tables using Date.FromText and wrapping it with Number.From to get the date ID from both tables. Then I perform a merge.
But of course, this is a hypothetical situation where Date.FromText would be able to read the two different formats in both Usage and Price tables. Otherwise, I would have to resort to splitting the year, day, and month using Text.Middle and concatenate them back and inferring the data type as type date.
Apart from this, I suppose this idea can be used in other scenarios, which I just haven't thought of yet at the moment. I will definitely share with you once I come across that situation. Thanks again, Ruth!
Looking forward to it!
I HAVE 100,s of tables in source how do i combine those(excel read data from pdf and each page had a table) in power qe
This is super helpful! I've been able to take away a lot from your other videos as well. I'm using this to build hierarchy structure for a chart of accounts. My current plan is invoking the formula for each layer in the hierarchy. It's working really well, but on a smaller set of data. I'll try researching table buffering or other best practice to optimize performance. Any tips would be helpful!
If you need buffer, then you probably should do this at the source. Buffering will only take you so far …
A new subscriber here. Thank you for your contribution. I have a looong way to go (this video was way above my current skillset) but I'm looking forward to learning from you.
Yes, it was a bit advanced, but try the steps anyway. It helps to understand when you do it yourself.
Welcome!!
super
jajaja no sé qué es lo mejor del video : lo divertido (qué lío con menor o mayor ) o las funciones tan prácticas... anyhow.... muchas gracias !!!!
😂😂 Glad you enjoyed it!
Primero que nada espero te mejores de salud y tomes tus medicinas; segundo: es una estupenda solución con la función invocada. Gracias y al pendiente de ti.
Muchas gracias Jose Manuel, por todo!
You are awesome! Definetly. Thanks sooo much!
Yey!!
Thanks for that great tip!
My pleasure!!
What is she saying ?