EMT 1495: Power Query: Allocate Invoice Header Amounts To Transaction Line Item Table
HTML-код
- Опубликовано: 6 сен 2024
- Download Excel Files: Start: people.highlin... Finished: people.highlin...
Entire page with all Excel Files for All Videos: people.highline...
In this video learn how to take Invoice Shipping and Discount Amounts from an Invoice Level (Header) Transaction or Fact Table and allocate those amounts to the Invoice Line Item Level Fact Table so we can use the Line Level Product Criteria (Filter) to create a Shipping and Discount Amount Report by Product. This is a typical business problem where the grain (granularity) of the two Transaction / Fact Tables is different and in order to use a filter or condition / criteria from the Line Level Transaction / Fact Table we must first allocate the Header Level amounts to the Line Level.
In this video we use Power Query to take the three tables and convert them using Merges (Joins) and Custom Columns in Power Query into our final report.
Topics:
1. (00:07) Introduction & Problem Setup
2. (02:08) Power Query Solution including multiple Mergers (Joins) and Custom Columns.
3. (04:33) Duplicate Query
4. (04:51) F2 key to re-name column
5. (05:00) Add Custom Column using Add Column, Multiply to calculate Line Sales which will automatically add Table.AddColumn Power Query Function (M Code Function)
6. (07:45) Use Merge feature (Join feature) to “lookup” Product Weight.
7. (09:49) Add Custom Column using Add Column, Multiply to calculate Line Weight.
8. (10:21) Use Group By feature to add Sales and Weight for each Invoice. This is a Power Query feature to aggregate by a condition that is similar to PivotTables or SUMIFS in Excel.
9. (12:14) Use Merge feature (Join feature) to “lookup” Invoice Discount & Invoice Shipping
10. (13:10) Add Custom Column using Add Column, Divide to calculate Invoice % Discount.
11. (14:18) Merge Header Invoice Level Table with Line Item Table.
12. (15:30) Second Consecutive Merge, now to get Weight from dProduct Table.
13. (16:04) Add Custom Column using Add Column, Multiply to calculate Line Discount
14. (16:29) Add Custom Column by manually typing out formula for Line Shipping.
15. (18:07) Group By to get Final Product Report with correct amounts for Shipping and Discount
16. (19:03) Load Report to Excel Sheet
17. (19:30) Summary
Related Videos:
EMT 1493: Excel Formulas & PivotTable: Allocate Invoice Header Amounts To Transaction Line Item Table
EMT 1493 Part 2: Excel Array Formulas Allocate Invoice Header Amounts To Transaction Line Item Table
EMT 1494: DAX & Power Pivot: Allocate Invoice Header Amounts To Transaction Line Item Table
EMT 1495: Power Query: Allocate Invoice Header Amounts To Transaction Line Item Table
EMT 1495 Part 02: Power Query w Group By Rows: Allocate Invoice Amounts To Line Item Table
EMT 1496: Power BI Desktop: Allocate Invoice Header Amounts To Transaction Line Item Table
EMT 1497: Vote For Your Favorite “Allocate Invoice Header Amounts To Transaction Line Item Table”
Search terms in this video: Header Detail Granularity Reporting Problem, Invoice Level, Invoice Detail Level Mismatch, Invoice Granularity Mismatch Reporting Issue, Granularity Invoice Reporting Problem: Invoice Total / Invoice Detail, Reporting Invoice Shipping & Discount at Invoice Detail Level?, Allocating Invoice Totals to Invoice Detail Level (Granularity Reporting Problem), Header Detail Granularity Reporting Invoice Example, Header/Line Item Transactions, Header / Line Item Transactions Reporting Issues, Allocating Invoice Shipping & Discount to Product Report, Allocating Invoice Shipping & Discount to Invoice Line Level, Allocating Invoice Shipping Discount to Invoice Line Level, Two Fact Tables, Different Granularity, How To Allocate Header Amounts to Line Item Fact Table so we can Slicer by Product?, Allocate Invoice Header Amounts, To Transaction Line Item Table, Two Transaction Tables, Different Granularity, Slice Report by Product
Excel Magic Trick 1495
![Megan Thee Stallion - Neva Play (feat. RM) [Official Video]](http://i.ytimg.com/vi/TpYTyAaTRts/mqdefault.jpg)
Wow! That's some real PQ fun!!!
Thanks, Teammate Leila!!!!
@LeilaGharani Now you are another master of EXCEL
The fact that PQ can show/evaluates each step of your action to me seems easier to understand than DAX. Thanks for sharing your efficient steps!
You are welcome Sal A!!!! Thanks for being such a long time supporter of this channel : )
I love this journey through comparative solutions - illustrating the adage, "There is always more than one way skin a cat." I'd like to see more series like this.
I have many other series like this, but most of them are all contains in a single long video. More to come becasue they are fun and informative for me also : )
Just subscribed few days ago, like all your videos, Power Query method is most beautiful way in my opinion, i just voted for this method before watch it
Thanks for your support, Jung, with your comments, Thumbs Up and Sub : ) Many more fun videos to come!
Great use of PQ for this example and it didn't even have to go into the M code! Nice.
Glad you like it, Doug!!! We did go into M Code when we changed the column name in the formula bar ; )
Another Power(Query)full video!
It's interesting to notice how the DAX solution from the previous video hides the mechanics of queries and merges and joins which the PowerQuery solution requires. Thanks for showing the same problem with different solutions as it makes learning much more effective and fun!
Glad the learning is effective and fun. It is fun for me too - to make the connections how how to solve a particular problem using Excel Spreadsheet, DAX & Data Model and Power Query and Power BI Desktop. BTW, One of the whole meanings of the Data Model and Relationships (called a semantic model) is that you build the relationships (Merge, VLOOKUPS and so on) into the model, so: 1) You don't have to build the relationships into the formula, 2) and more importantly, because the Relationships are in the model; then, a given single formula can work across many different reports and relationships.
Thanks for your support, Rob!
Beautiful. PQ always ahead and more dynamic ... Thanks for sharing..
You are welcome, Haider!!! Thanks for the support : )
Great video on Power Query. Thanks for posting.
You are welcome, Tim!!! And thanks for helping to support by always clicking that Thumbs Up and leaving a comment when so inspired : ) And of course Sub so you know when new vids come out! Many more PQ videos to come : )
Beautiful, beautiful and fun! I had more steps to do the same as I'm not really familiar with 'Add Column From Number'
Now it is so much simpler...
Thanks for the continuing inspiration and your tireless enthusiasm.
You are welcome, Teammate!!!!!
Simply fantastic! What a beautiful PQ tutorial ! A little bit advanced at that, but perfectly understandable!
Those years of teaching are paying big dividents now! ;-) And we are all here to profit from it and enjoy. Yay!
Method evaluation:
Pros: probably easier to understand and construct than DAX: those merges are brilliant with their implicit filters (of sorts).
Cons: no ported number format (like from Measures) and especially: not that dynamically flexible in the final report (no slicing and dicing on the fly): in order to change the report you have to modify the query.
Unless you load a detailed table and aggregate on the Pivot Table level. At which point you'd better "load to the Data Model" for performance...
In conclusion: at this point my preference would probably go to a mixed method: PQ for most of what you did in this video, but would end with a complete table at the max granularity level, and load that into the Data Model. Then finish it of in DAX with some Measures. Serve while still hot.
When I get better at DAX, I would minimize PQ and maximize DAX and the Data Model, because that will yield the best performance, especially in Big Data scenarios.
BTW: thank you very much for this beautiful and most instructive little series here!
You're doing the most beautiful job with it and have me (/us?) hooked even more than usual!
It's not unlike watching one's favorite TV-series...
(also proves your point to not concatenate these videos)
We'll catch you on the next one, for sure!...
Thank you for your awesome support, Geert! Can't wait for the next one too : )
well Mike, I had lots of more steps for the same solution. Well done with this merge and merge and merge...Only one custom collum...I never used these basic math operators but I definitively will from now on....
Yes, the math operators are great, as is so many of the user interface features that Excel gives us in Power Query : )
Thanks for the support, Bart !
Great merges! :)
Mike, thank you! you are the best teacher! its mind spinning, but got to do it.
Yes, got to do it!!! You are welcome, Brain x : ) and Thanks for your support!
EXCELlent video Mike. Thanks for the efforts you put in to make learning fun.
You are welcome, Syed!!!
Power Query, ,, the best addition to Excel.
It is quite amazing!!!!! Thanks for your support : )
I live very much Power Query and your use of basic math operations :)
I live it too!!! I love it too!!!! Thank you for your comment, Excel.i.Adam, and for the support with Thumbs Up and Sub : )
You are a machine, sir. Thanks!!
Fun Machine!!!!! Thanks, ExcelVbaIsFun!!!!
Very very good and informartive sir ji 😇😇😇
You are welcome! And thanks for helping to support by always clicking that Thumbs Up and leaving a comment when so inspired : ) And of course Sub so you know when new vids come out!
Great solution Mike!! Love Power Query!
Yes, Power Query is the greatest thing since the PivotTable, or maybe DAX and Data Model for millions of rows...
Great one mikee..... I really use power query for data cleaning and arranging.....just like arranging ingredients for cooking.... Then I cook BIRYANI / PIZZAA on DAX measuresssss...
Hope u had biryani ??
Thanks for the support ... yes, spicy rice is great : )
Once again absolutely brilliant solution ....could understand this better than DAX mainly because I have explored PQ more than DAX.
Question - If you had to recommend an organized way of learning DAX and PQ for beginner, which books / approach would you recommend.
Will appreciate your feed back
Thanks
To learn DAX and Power Query, watch the new class i have on both free here at RUclips over the next months : ) Books: For DAX, and book by Russo and Ferrarri. Power Query, there is no real good book that teaches Basics in a solid way, except for M is For Data Monkey by Pauls.
Thanks Mike for your reply. I look forward to your new class. For books - thanks. I already have M is for Data Monkey which got me started at least...
I do have a full free class coming up here at excelisfun @ RUclips, not a book though...
Sir u had made my interest in power query to peek of everst mountain
Thanks sir
Youa re welcome! Many more Power Query Videos to come. I also have over 100 videos about Power Query already posted. Are you a subscriber and do you know hoe to use the channel and find playlists?
ExcelIsFun
Yes sir i know how to check ur playlist of power query and i will surely do it
😇😇😇😇😇
ExcelIsFun
And yes i am a suscriber and eagerly waits ur new videos on power query
Many more to come : )
Thanks for grouping and marge fun :). I think I have my favourite solution!
It is very a very good solution. Thanks for watching, Teammate : )
Insanely good.
Insanely cool that you liked it and support excelisfun with comment, Thumbs Up and Sub : ) Thanks, Hoppi!!!!
Thanks Mike for carusel of merge :-)))
Merge over too many rivers : )
Thanks Mike!!!! It has been fun watching :) :)
Glad it is fun, John!!
Brilliant as always! Thank you!
You are welcome! Thanks for the support with your comment, Thumbs Up and Sub : )
Wawwwww great brain in excel :-)
Thanks Mohamed!!!!
Like math operation feature (multiplying/dividing|) by selecting columns and changing column name in M code
Yes, that is a really cool feature : ) Thanks for your support!!
I need to learn about DAX it looks really handy but a little difficult
It is very difficult. I have read many books since 2013 and taken classes from the best in the world, Marco Russo and Alberto Ferrari, but it still is hard. So many invisible forces behind the scenes. But it is worth it because it is so unique and powerful : ) More videos on DAX over the next year.
ExcelIsFun thank you so much Mr. Mike for your explanation i really always appreciate
Wow, powerful software. Awesome.
Yes, Power Query change every thing : ) For that matter, so does Data Model and DAX : )
Thanks for the support, DRSteele!
Always! You're welcome.
You should try using the community feature
Please help me: what is the community feature?
The real question is: Do you see the community tab on your channel? (You have enough subs to get the feature)