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
@CurbalEN
3 жыл бұрын
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!!
@jim_19
Жыл бұрын
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.
@maartenbaumers3448
Жыл бұрын
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.
@kelvinalmarez5495
Жыл бұрын
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.
@cristian.angyal
Жыл бұрын
Hi @@kelvinalmarez5495. UI stands for User Interface
@Unbox747
2 жыл бұрын
I am always so excited when Ruth says "for this - i am going to build a function"
@CurbalEN
2 жыл бұрын
😂😂
@bettina.e-k
3 жыл бұрын
Thank you Ruth, this video also doubles up as a great intro to functions! Glad you are feeling better.
@1yyymmmddd
3 жыл бұрын
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.
@PedroAnacletoOficial
2 жыл бұрын
That's what I've been doing.
@AlvaroPerez-ox7ul
2 жыл бұрын
Woman you rule these issues. What a great teacher too!
@CurbalEN
2 жыл бұрын
Man, glad to 👂
@alexkim7270
3 жыл бұрын
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!
@CurbalEN
3 жыл бұрын
Looking forward to it!
@joanneosborne2428
3 жыл бұрын
Thanks, Ruth. I love all your training videos, I really have learned so much from you. 😊
@CurbalEN
3 жыл бұрын
💛💛💛
@HachiAdachi
3 жыл бұрын
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!! 🤞
@CurbalEN
3 жыл бұрын
Hi Hachi, what a weekend that was! 🤢 For M I would choose Imke or Miguel Escobar, they are amazing!!
@suryasahoo3771
3 жыл бұрын
Thank Ruth, for showing us new methods and techniques
@CurbalEN
3 жыл бұрын
As soon as I learn something....so do you ;)
@suryasahoo3771
3 жыл бұрын
@@CurbalEN Yah me too
@ginesc
3 жыл бұрын
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 !!!!
@CurbalEN
3 жыл бұрын
😂😂 Glad you enjoyed it!
@AlvaroPerez-ox7ul
2 жыл бұрын
You are such a charming teacher when you giggle I giggle too LMAO
@leonelpastorino565
3 жыл бұрын
Genial, me ayudaste a resolver un problema. Muchas gracias y un saludo grande desde Argentina!
@CurbalEN
3 жыл бұрын
Un placer y saludos!!
@late_arvie
3 жыл бұрын
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.
@CurbalEN
3 жыл бұрын
Yes, it was a bit advanced, but try the steps anyway. It helps to understand when you do it yourself. Welcome!!
@marcosviniciusdossantos1693
3 жыл бұрын
You are awesome! Definetly. Thanks sooo much!
@CurbalEN
3 жыл бұрын
Yey!!
@joseagundis1
3 жыл бұрын
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.
@CurbalEN
3 жыл бұрын
Muchas gracias Jose Manuel, por todo!
@Luftschlange123
11 ай бұрын
thank you
@kapiguhan
3 жыл бұрын
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..
@harshdeep8945
2 жыл бұрын
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
@willyms
3 жыл бұрын
Thanks for that great tip!
@CurbalEN
3 жыл бұрын
My pleasure!!
@PedroAnacletoOficial
2 жыл бұрын
Fantastic!!!
@rafelbhuiyan
3 жыл бұрын
Hi, what happens if we got two variables?
@abbielmi9163
3 жыл бұрын
Nice video looking the source file for practice.
@CurbalEN
3 жыл бұрын
I Uploaded it to community downloads in the download center. It is download number 67.
@abbielmi9163
3 жыл бұрын
@@CurbalEN got it thanks . Excellent. Well explained.
@DWBoiler312
Жыл бұрын
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!
@CurbalEN
Жыл бұрын
If you need buffer, then you probably should do this at the source. Buffering will only take you so far …
@hakiki1518
2 жыл бұрын
Are you the founder of those formulas???
@Ahmad_Askar
3 жыл бұрын
super
@MrSpiritUAll
3 жыл бұрын
Nice! 👍😘
@CurbalEN
3 жыл бұрын
It is!
@VladMZ
3 жыл бұрын
You may want to Table.Buffer before this step if your data is not 10 rows. Cool technique tho.
@CurbalEN
3 жыл бұрын
Will do :)
@sajalsinghal8846
3 ай бұрын
how to merge many to many data
@Dadosza
Жыл бұрын
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.
@CurbalEN
Жыл бұрын
My editing skills are not always the best :(
@Dadosza
Жыл бұрын
@@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.
@CurbalEN
Жыл бұрын
Totally understand!
@quentinwinisdoerffer421
3 жыл бұрын
🤯 Do you have any cool video about custom function? Never used it and well...
@CurbalEN
3 жыл бұрын
Like this one: m.kzitem.info/news/bejne/oqiVzIqVqIqEgag I have others, just search the channel:)
Пікірлер: 60