Excel Magic Trick 1347

Power Query Function: Split Master Table into Sub Tables for Each Product

DESCRIPTION

See how to create a Power Query (Get & Transform) Custom Function with a parameter in order to create five Sub Tables, one for each product, each on a new sheet, from a Master Table:
(00:17) Introduction. See finished Power Query Function to Extract Records to A Sheet Based on Criteria / Condition / Parameter.
(02:23) Import Excel Master Table into Query Editor.
(03:19) Filter Records and Add Custom Column to Calculate Revenue. See the Function Number.Round.
(07:10) Look at M Code and learn about some of the conventions for M Code.
(09:57) Load Query to Sheet to Test where the M Code works.
(10:29) Build Power Query Custom Function. Create Parameter / Input Variable / Changeable Criteria in M Code.
(12:58) Invoke Function to Create Sub Tables
(14:32) Test whether the sub tables
(15:57) Learn how to edit the Parameterized Custom Function so that all five invoked functions and the five Sub Tables update perfectly! Learn how to use the Table.RemoveColumns function.
(20:36) Summary

WORKBOOKS

Download “Excel-Magic-Trick-1341-1360.zip” Excel-Magic-Trick-1341-1360.zip – Downloaded 6979 times – 23 MB







Be the first to comment

Leave a Reply