APQ09: Advanced Power Query | Exact match lookup in Power Query | No Merge Queries

[@DhruvDua88]

You have explained very succintly, and this is one of the easiest videos to understand look up in Powerquery

[@glostermeteor2]

These PQ videos are really good, some of the best I've seen on YT. I've been using PQ for a couple of years now so they are pitched right at my level and full of useful techniques. Thank you.

[@AAAExcel]

Happy that you found them useful ☺️

[@ebf6562]

Génial, merci beaucoup! Vous êtes un excellent professeur.

[@AAAExcel]

Avec plaisir, merci pour ton gentil commentaire

[@zahoorsarbandi2982]

both techniques are superb. Thank you for sharing your valuable knowledge.

[@AAAExcel]

My pleasure

[@danthompson8309]

Thank you so much. Excellent overview of examples. It’s been hard to find videos with these deeper examples.

[@AAAExcel]

My pleasure 🙏

[@kennethstephani692]

Terrific video!!

[@AAAExcel]

Thank you 🙏

[@boissierepascal5755]

Very helpful and interesting 👌🏽

[@AAAExcel]

thank you so much

[@kishorpanara4191]

Awesome

[@AAAExcel]

thank you

[@SaleemShouket-r3p]

Great

[@AAAExcel]

Thank you 🙏

[@johnwabaki7125]

Appreciate it 👌

[@AAAExcel]

Thank you

[@leetran8328]

tks u

[@AAAExcel]

Most welcome

[@MichaelVB226]

Are there any significant performance issue differences with the use of either technique? Thank you for your expertise! I enjoyed the video!

[@AAAExcel]

This question needs more investigation, I did a quick search, but i found a comparison between merge and List.PositionOf, although List.PositionOf result in less steps and cleaner code, some opinions say that merge is still faster. Also, it's recommended to use Table.Buffer if you are working with larger data sets to enhance performance.

[@mz2433]

@@AAAExcel Hi Amr, would you please walk us through Table.Buffer in a separate video? Thank you very much!!

[@AAAExcel]

Will consider 👍

[@mostafafarag8078]

Hello I have an inquiry , is there any method to link the record with a pdf like a hyperlink?

[@AAAExcel]

I found this in "Fabric Comunity", it may help community.fabric.microsoft.com/t5/Power-Query/Add-Hyperlink-to-a-cell-column-in-Power-Query/m-p/3440263

[@ebf6562]

Hello. How can we use a Query converted in function in List.PositionOf? Thank you very much

[@AAAExcel]

i think it is doable, you can use variable inside the List.PositionOf, but no example in my mind for now. i have several videos discussing custom functions, like the one talking about running totals, you can check the following link: kzbin.info/www/bejne/in-ZqmuYq7qnlck

[@amoserene1553]

Thank you! But a question, if Customer Id is not defined in customer name then error is showing.

[@AAAExcel]

you can solve this issue using try and otherwise I have a video explaining how to use it: kzbin.info/www/bejne/g2eup3x6pLGmh6c

[@osoriomatucurane9511]

Do you mean, if there is no match (a given customer that has not made one single purchase, therefore not recorded in the sales table) we get an error for that row/record. Correct?

[@sdr5593]

How would you solve the following problem? I have a table called budgetsales. It is the forecast sales for the upcoming year by customer and product type. In total around 300 rows. There are then columns for revenue, volume, gross profit, freight cost, overhead cost and net profit. The annual budget table needs to be split into monthly numbers with the phasing different per month. Q1 months = 7.333% each, Q2 months = 8.3333% each, Q3 months = 8.3333% each and Q4 months = 9.3333% each. The fiscal year starts on 1st June The monthly phasing needs to be applied to all months as per above. So June sales volume = annual sales volume X 7.33333%. same for GP, volume etc September sales revenue = annual sales revenue x 8.3333% And so on...... The output should be a table than can be analysed with a pivot table.

[@AAAExcel]

Create a table with the monthly allocation %, reference it inside the budget table, then expand the table. you will have the table expanded to include a month column, multiply all columns containing numbers (i.e. sales, cost, profit, etc..) by the % came from the new table (one by one). then you are good to go

[@aymanabuelmaged7230]

Happy Eid Al-Fitr to you, my teacher 🎉 . I would like to thank you and express my gratitude for this generous Wonderful and unique content And I under your permission ask if there is nothing in the lookup table that match the other table, is it possible to use "if statement" or better "try and otherwise", appreciate your patience. ❤

[@AAAExcel]

Happy Eid Ayman, thank you so much for your kind words. Absolutely right, can't agree more

[@roywilson9580]

Thank you for the interesting video. I have no real preference for the method of obtaining results, though I think I would add a list buffer statement to the lists if the dataset was particularly large. Having worked with several people who love click the gear icon, change things and break perfectly good queries I would make the following update to the metod to remove the gear icon from the last function in the query and thus, hopefully, stop changes being made by colleagues who don't understand what they are playing with: let Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer ID", Int64.Type}, {"Product ID", Int64.Type}, {"Quantity", Int64.Type}}), FinalReport = let a = Table.AddColumn( #"Changed Type", "Customer Name", each Customer{ List.PositionOf( Customer[Customer ID], [Customer ID] ) }[Customer Name], type text ), b = Table.AddColumn( a, "Revenue", each Price{ [Product ID = [Product ID]] } [Price] * [Quantity], type number ), c = Table.Group( b, { "Customer Name" }, { { "Revenue", each List.Sum( [Revenue] ), type number } } ) in c in FinalReport Then I would hide the formula bar in optioins just to be on the safe side.

[@AAAExcel]

nice, thank you for the useful contribution

[@osoriomatucurane9511]

This paradigm is an awesome trick indeed. Grouping the transformation steps into nested let in statments. In addition to safety advantages from hiding the gear icon, we could keep the transformation process transparent , informative, resulting in compact displayed steps as well as a better structured and much more readible code. Here I have tweaked your code as follows: Let Source(....), //------------ MergeCustomerName = let LookUpName = Table.AddColumn(Source, .....) in LookUpName, //----------------- MergePrice = let LookUpPrice = AddColumn(......) in LookUpPrice, // ----------------- Grouping = let Revenue = AddColumn(.....), GroupedRevenue = Table.Group(....), SortedRevenue = Table.Sort(...) in SortedRevenue, in Grouping ------------- In this structured way, the displayed visible steps renamed meaningful will be 1.Source 2. MergedCustomerName 3. MergedPrice 4. Grouping Final Note. The Data Types could be changed along embbeded in the Table.AddColumn(), Table.TansformationColumn(), or relying on the data type coersing functions or separetly using the Table.TransformColumnTypes() in each grouped steps.