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

#exceltips #powerquery #exceltutorial #msexcel
--------------------------------------------------------------------------------------
Chapters:
0:00 Intro
1:19 List.PositionOf
6:51 Using lookup operator []
11:38 Group By to build a report
13:04 close
--------------------------------------------------------------------------------------
To watch the full playlist
--------------------------------------------------------------------------------------
To download the working files:
--------------------------------------------------------------------------------------
To subscribe:
--------------------------------------------------------------------------------------
To follow on Facebook
--------------------------------------------------------------------------------------
To follow on LinkedIn:

[DhruvDua]

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

[ebf]

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

[glostermeteor]

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.

[zahoorsarbandi]

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

[danthompson]

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

[MichaelVB]

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

[sdr]

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 same for GP, volume etc
September sales revenue = annual sales revenue x 8.3333%
And so
The output should be a table than can be analysed with a pivot table.

[amoserene]

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

[aymanabuelmaged]

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. ❤

[ebf]

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

[mostafafarag]

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

[roywilson]

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.