thanks for the video! very clear instructions. How would you approach this if you had, in addition to the "Quantity" column, a "Sells" column, and you wanted to extract only the rows from table 1 that had "Quantities" > 0 and "Sells" > a specified value?
it's interesting cos when you added another row with the same combination as the first one (i.e. Ford / Explorer), I thought I would add the amount to the first one (80+100= 180) but instead it simply added another row so now you have 2 similar rows but with a different amount on each row. does it always do that or is there a way to increase the total for this combination all on the same row?
not sure on your exact scenarios but you can either do the lookups as separate queries and then append them for final or keyword lookup like these th-cam.com/users/DougHExcelsearch?query=power%20query%20keyword
Nice example Doug. Thanks. You could make a new (reference) query and group by, to see how many Items you have in the list. If you then filter you have a list of unique (selection1) or multiple entries.
Hi Doug, this is awesome, I however am in a situation where i need to lookup sales people assigned to specific customers during a specific period... one customer could be assigned to one sales person from January until May End and then to another from beginning of June until November End etc. For this i would need parameters such as Higher or equal and less then equal, but since power query is pretty new to me i am not sure how to do it.
Can we do MATCH function in Power Query to find out if a value is missing in table A compared to table B? For example, I want to check whether table A doesn't have some people's names that are in table B, so I want to append those names into it. Thank you. Just when I think of it, can I use Right Anti Join to solve this problem?
This is great. It brings me some idea in my spreadsheet. Thank you much.
Hi Indra Kusuma, glad you liked it, thanks for commenting!
My friend this is Gold for me at the time. Thanks.
Glad to hear it!
Right Outer fun : )
Yep fun stuff!
Amazing. Thanks!
You’re welcome!
thanks for the video! very clear instructions.
How would you approach this if you had, in addition to the "Quantity" column, a "Sells" column, and you wanted to extract only the rows from table 1 that had "Quantities" > 0 and "Sells" > a specified value?
try conditions as helper column, this vid may give ideas th-cam.com/video/HlOO18uSpb8/w-d-xo.html
Doug, nice feature; I did not know but LOVE power query. It would have been nice to have a download file to be able to refer back to.
Hi @sherryfox227, thanks for the comment !...sorry don't have files to download :-(
sir where i get the workbooks for your examples.
Hi Khalid Majeed, sorry don't have files to download :-(
it's interesting cos when you added another row with the same combination as the first one (i.e. Ford / Explorer), I thought I would add the amount to the first one (80+100= 180) but instead it simply added another row so now you have 2 similar rows but with a different amount on each row. does it always do that or is there a way to increase the total for this combination all on the same row?
Is this possible with OR logic where one of multiple columns can be used for lookup?
not sure on your exact scenarios but you can either do the lookups as separate queries and then append them for final or keyword lookup like these th-cam.com/users/DougHExcelsearch?query=power%20query%20keyword
That is absolutely unbelievable 😍
Hi Ahmed Al-Dossary, thanks for the comment!
Nice example Doug. Thanks. You could make a new (reference) query and group by, to see how many Items you have in the list. If you then filter you have a list of unique (selection1) or multiple entries.
Nice addition...thanks Bart!
Hi Doug, this is awesome, I however am in a situation where i need to lookup sales people assigned to specific customers during a specific period... one customer could be assigned to one sales person from January until May End and then to another from beginning of June until November End etc. For this i would need parameters such as Higher or equal and less then equal, but since power query is pretty new to me i am not sure how to do it.
Many, many thanks!
You're Welcome!
Can we do MATCH function in Power Query to find out if a value is missing in table A compared to table B?
For example, I want to check whether table A doesn't have some people's names that are in table B, so I want to append those names into it.
Thank you.
Just when I think of it, can I use Right Anti Join to solve this problem?
Hope these can give ideas
th-cam.com/video/GlMFBNQUQQ8/w-d-xo.html
th-cam.com/video/fzfN84kDqgU/w-d-xo.html
Sir ji an another superb way to explain how useful and powerful tool is power query
Thanks sir 😇😇😇
Hi HARISH PAL DHIR, glad you liked it, thanks for commenting!
well done!
Hi Steven Nye, thanks for the comment!
Muito bom Doug, boa dica, obrigado.
Obrigado!
How do you match multiple columns or rows
Some of these videos may give insight
th-cam.com/users/dough517search?view_as=subscriber&query=lookup+multiple
Thank u so much sir
You're welcome!
above example can be solved by PIVOT Table
Hi MAJID SIDDIQUE, thanks for adding to the thread!
Too much dithering, just get on with it.
Hi jonboy150279, thanks for the feedback!