Excel Magic Trick 1381

Power Query: Records In Column into Table then Two Different Top 3 Reports

DESCRIPTION

See how to convert non-proper data set (records that are listed in a single column) into two Top 3 Reports (Including Ties for Third) for Domestic Sales and Agriculture Sales.
Many Amazing Power Query Topics:
(00:14) Introduction
(02:06) Import Parameter Table, rename Query, Drilldown in cell to get Number, Close and Load as Connection.
(03:16) Import Bad Data, rename Query
(03:44) Transform, Format, Trim
(03:55) Filter Out Blanks
(04:14) Add Index
(05:14) Add Modulo
(05:56) Method 1 to Pivot: 4-step method
(07:17) Method 2 to Pivot: 3-step method: 1) Divide Index Column by 3 using Divide-Integer option, 2) Pivot Modulo Column on Data, and Do Not Aggregate, 3) Remove Index
(08:58) Promote Headers
(09:10) Change Number Data Type to Number
(09:20) Sort From Biggest to Smallest
(09:30) Reference Table, Rename Query
(10:37) F2 keyboard to rename tables or steps in Query.
(10:55) Filter for DOMESTIC
(11:26) Rename Step “FilteredRows”
(12:15) List Tools, Keep Top 3 Rows
(12:37) In formula: = List.FirstN(OUTSTANDING,3), change 3 to “TopParameter01”
(12:58) List Tools, Keep Bottom 1 Rows
(13:28) Drill Down
(13:39) Name Step “HurdleValue”
(13:55) Create New step with Fx button and reuse “FilteredRows” (this brings back previous step #16)
(14:12) Rename Step in Query to “FilteredRows02”
(14:19) List Tools, Keep Top 3 Rows (rather than Filter feature)
(14:55) Change Formula to: Table.FirstN(FilteredRows02, each [OUTSTANDING] Greater Than Or Equal To HurdleValue)
(15:44) Copy Query
(16:24) Change “DOMESTIC” to “AGRICULTURE”
(16:46) Load 3 queries As “Connection Only”, then Load Reports to Sheet
(17:33) Test by changing parameter and data source
(18:01) Add new data and Test
(18:48) Encounter problem with new data and Fix Filter by adding an And Logical Test to also filter out null values
(20:20) Summary

WORKBOOKS

Download “Excel Magic Trick 1381-1385” Excel-Magic-Trick-1381-1385.zip – Downloaded 89 times – 81 MB







Be the first to comment

Leave a Reply