APQ09: Advanced Power Query | Exact match lookup in Power Query | No Merge Queries
ฝัง
- เผยแพร่เมื่อ 31 พ.ค. 2024
- #exceltips #powerquery #exceltutorial #msexcel
Website: www.aaa-power.net
--------------------------------------------------------------------------------------
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
• Advanced Power Query
--------------------------------------------------------------------------------------
To download the working files:
drive.google.com/drive/folder...
--------------------------------------------------------------------------------------
To subscribe:
/ @aaaexcel
--------------------------------------------------------------------------------------
To follow on Facebook
/ excelispower
--------------------------------------------------------------------------------------
To follow on LinkedIn:
/ amratef
Génial, merci beaucoup! Vous êtes un excellent professeur.
Avec plaisir, merci pour ton gentil commentaire
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.
Happy that you found them useful ☺️
Thank you so much. Excellent overview of examples. It’s been hard to find videos with these deeper examples.
My pleasure 🙏
both techniques are superb. Thank you for sharing your valuable knowledge.
My pleasure
Awesome
thank you
Very helpful and interesting 👌🏽
thank you so much
Are there any significant performance issue differences with the use of either technique? Thank you for your expertise! I enjoyed the video!
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.
@@AAAExcel Hi Amr, would you please walk us through Table.Buffer in a separate video? Thank you very much!!
Will consider 👍
Appreciate it 👌
Thank you
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. ❤
Happy Eid Ayman, thank you so much for your kind words. Absolutely right, can't agree more
Thank you! But a question, if Customer Id is not defined in customer name then error is showing.
you can solve this issue using try and otherwise
I have a video explaining how to use it:
th-cam.com/video/M6KvIDoLsQE/w-d-xo.html
Hello. How can we use a Query converted in function in List.PositionOf? Thank you very much
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:
th-cam.com/video/TN6y8bvUt_g/w-d-xo.html
Hello
I have an inquiry , is there any method to link the record with a pdf like a hyperlink?
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
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.
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
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.
nice, thank you for the useful contribution