Excel Magic Trick 1347: Power Query Function: Split Master Table into Sub Tables for Each Product

Download Files:

Start File: https://excelisfun.net/files/EMT1347Start.xlsx

Finish File: https://excelisfun.net/files/EMT1349Finished.xlsm

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:
1. (00:17) Introduction. See finished Power Query Function to Extract Records to A Sheet Based on Criteria / Condition / Parameter.
2. (02:23) Import Excel Master Table into Query Editor.
3. (03:19) Filter Records and Add Custom Column to Calculate Revenue. See the Function Number.Round.
4. (07:10) Look at M Code and learn about some of the conventions for M Code.
5. (09:57) Load Query to Sheet to Test where the M Code works.
6. (10:29) Build Power Query Custom Function. Create Parameter / Input Variable / Changeable Criteria in M Code.
7. (12:58) Invoke Function to Create Sub Tables
8. (14:32) Test whether the sub tables
9. (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.
10. (20:36) Summary
Power Query (Get & Transform) Custom Function
Power Query (Get & Transform) Code with an Input Variable
Power Query (Get & Transform) with Input Criteria
Power Query (Get & Transform) Parameterized Custom Function
Build Re-Usable M Code in Power Query (Get & Transform)
Create Power Query (Get & Transform) Function

Видео Excel Magic Trick 1347: Power Query Function: Split Master Table into Sub Tables for Each Product канала ExcelIsFun