Excel Magic Trick 1334: Power Query or Formula: Lookup Name Based on Max Value and more…

Download Files:

See how to use Power Query (Get & Transform) and a set of Formulas to create a report that Adds Total Units for Each Product, Finds Max Units Sold for Each Product, and Lists the Sales Rep Name Who Made Max Sale:
1. (00:16) Introduction
2. (00:57) SUMIFS to add with 1 condition
3. (01:59) MAXIFS to add with 1 condition (New Excel 2016 Office 365 Insider Edition)
4. (02:46) INDEX & MATCH Array Formula to retrieved name of Sales Rep with Max Unit Sale
5. (06:52) Power Query to Group By Product and Add Associated Units Sold
6. (08:30) Power Query to Group By Product with All Rows to Show All Records For Each Product. Action Returns a Table.
7. (10:18) Power Query Table.Max Function to get Record for Max Units Sold For Each Product.
8. (13:53) Summary

[DougHExcel]

Excellent video! A great use of the group by feature in Power Query.

[pmsocho]

Super cool! Those new Table. functions are awesome!

[TheOndricek]

Just what I needed - thank you thank you thank you!

[francismacisaac]

Wow, these video are great, I am putting them into practice and boss thinks I can now walk on water.

[simonhughes]

Get & Transform is going to transform how we use Excel!

[muhammadfathi]

Max Unit
=IFERROR(1/(1/MAX(IF(Sales[Product]=F5, Sales[Units Sold]))), 1)

[wangjing]

Great video! Thanks for sharing! How do you set up the view to see the record at the bottom of the queries?

[sunnyqin]

hi Mike! Thanks for the helpful video! Can you share with me how to code in power query if one of my parameter is "date"? The queries doesn't seem to work with the date parameter.

Thanks in advance!

[besadakhazarian]

i have a question please,
i want to connect to cells together so that if i change one the other changes automatically and vice versa.
Can you help me with that?

[patricknyamu]

The aim is to learn how to use power query

[patricknyamu]

Hey Mike
Can you make a video to solve Highline Excel 2013 Class Video 51 SheetM2(1) using power query instead of VBA.
Hint: it involves Pivot
Power Query is a true power tool