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

About this tutorial:

Video duration: 00:21:05
Download File:
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…

Post Author: OfficeTutes.com

Apple lover, ICT and LEAN consultant, MS Office lecturer My other website with video tutorials - Tutorials, guides and news for iPhones and iPads

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

    Pavol Cernak

    (July 10, 2020 - 10:33 pm)

    Pretty cool idea. I'm gonna implement this to my work about quarters analyses. Better than copy queries with quarter parameters. Thanx lot.

    Victor Fernandez

    (July 10, 2020 - 10:33 pm)

    Very well explained. Thanks

    Shehan Thivanka

    (July 10, 2020 - 10:33 pm)

    Thank you very much Mike!

    K D

    (July 10, 2020 - 10:33 pm)

    This is a very beautiful tutorial. Thank Mike. 👍

    Farid Mohammad

    (July 10, 2020 - 10:33 pm)

    Hello, Mike G.
    I have 8 columns in Excel power query editor and 893 rows after completing a number of steps. Now I want first 4 columns then last 4 columns to start from Rows no. 894 to 1786. If I had been able do this, my report would have been complete!
    Or, if I had been able to split the 8 columns table into two separate tables, then I could have been able to append the two tables to get my report!!

    Will you please help out???

    Martin Bønnelykke Jensen

    (July 10, 2020 - 10:33 pm)

    sick trick, and weird that Youtube suggested exactly this video the day after i was in need of this trick (and no i haven't googled it)


    (July 10, 2020 - 10:33 pm)

    Very nice video and clear explanation!
    Too quick suggestions…
    1. another option for modifying the function instead of writing code from scratch would be to modify the original code using the power query interface and then copy that single line of code back into the function.
    2. I believe you could create a query to extract the list of product names and use that as a parameter query input into your function so that you have a drop down list make it unnecessary to type each product name.

    Michael James

    (July 10, 2020 - 10:33 pm)

    Hi there! Thank you for the video. In this example, there are only a few products which you are splitting on. I have a column with 200+ values which I am using to split data into different tables. How would you loop through all the unique values in the column and generate tables for all rows which match the value in my target column?

    John Borg

    (July 10, 2020 - 10:33 pm)

    Great Staff Mike. 🙂 🙂

    Steven Nye

    (July 10, 2020 - 10:33 pm)

    Another excellent explanation!

    Sandy Holt

    (July 10, 2020 - 10:33 pm)

    Is it possible to automate this for each product, by passing an array of product names in? And then close and load on a New Sheet and rename the sheet w/ the product name?

    Ciprian LUPU

    (July 10, 2020 - 10:33 pm)

    Hi, i’m lookink with interest to all your videos. I would like to get some help in a problem! Can u give me an email address?

    Alex Balgalv

    (July 10, 2020 - 10:33 pm)

    You saved my day good sir. Thanks a lot 🙂

    Muhammad Nauman

    (July 10, 2020 - 10:33 pm)

    Amazing… Mike, i needed this and fortunately found in your channel

    Johnny T

    (July 10, 2020 - 10:33 pm)

    What if you have hundreds or thousands of product codes? Do we manually do this for every single product?

    Ahmed Al-Dossary

    (July 10, 2020 - 10:33 pm)

    Amazing solution from PQ

    Joe JG

    (July 10, 2020 - 10:33 pm)

    how come microsoft doesn't add intellisense in the M editor?

    David Green

    (July 10, 2020 - 10:33 pm)

    Great tip – one question, is there a way to automate the generation of new sub reports if the number of products varies?

    Grason Moye

    (July 10, 2020 - 10:33 pm)

    I want to completely automate this exact process only if there is a new piece of data in a column. Any chance you could point me in the right direction?


    (July 10, 2020 - 10:33 pm)

    Great video. Once you have the Master and the subset files completed, is it possible to have a "bi-directional" updating? I divided the Master into Geographical regions and would like each region to use only their respective file, but have it update the Master.

    Saodat P

    (July 10, 2020 - 10:33 pm)

    great video thank you!

    Igor Zinenko

    (July 10, 2020 - 10:33 pm)

    great video, but Mike why did you call net revenue the item which should be called gross revenue?

    Thierno Ibrahima Diallo

    (July 10, 2020 - 10:33 pm)

    Great; thanks a lot Mike; what happens if i have new records with new different ProductName?

    Gerald Sardua

    (July 10, 2020 - 10:33 pm)

    Thanks for these videos mate. Keep it up! Liked and Subbed 🙂

    Sal A

    (July 10, 2020 - 10:33 pm)

    If I have separate workbooks and each has a fiscal year column like 2017, 2016, 2015 how can I extract the most current year (2017) and the prior year (2016) records in a pivot table automatically when I refresh?.  Thanks in advance.


    (July 10, 2020 - 10:33 pm)

    Thanks Mike. I have been following your videos on Power Query and it is helping me a lot with my work. I used to do some of my stuff in Access and dump the data to Excel. Now, i'm just using Power Query instead.

    Nis 007

    (July 10, 2020 - 10:33 pm)

    Thank you very much.
    Is there any way to split master table to sub table based on month.

    Mark Baxter

    (July 10, 2020 - 10:33 pm)

    Hi Mike, I have a question about the code for the function. When you added the RemoveColumnsUnitsDiscountPrice line why didn't you have to add the # value to the beginning like all the other rows? Great video by the way!

    ubo ras

    (July 10, 2020 - 10:33 pm)

    what dose 0 thumb down mean?

    Osamah Alabdi

    (July 10, 2020 - 10:33 pm)

    Thank you very much Mike. You really helping me a lot with your videos. But, after I saw this video I asked myself a question which is:
    ?Is it possible to change instead of the Text pattern to drown-down list in the product column and load it to separated sheet

    I mean instead of write letters in the ProductName(text) to appear the table using Dorp-Down List

    Best Luck, 🙂

    Giang Pham

    (July 10, 2020 - 10:33 pm)

    Great trick as usual!
    But is there a way to make the function automatically create new sheet, rename it as product name and put the table in the created worksheet?

    Doug H

    (July 10, 2020 - 10:33 pm)

    Super-duper video! Thanks Mike!

    Daniel Lamarche

    (July 10, 2020 - 10:33 pm)

    Great stuff Mike. Thanks. Power Query in Seattle!!!


    (July 10, 2020 - 10:33 pm)

    Sorry my poor english. Thank you.


    (July 10, 2020 - 10:33 pm)

    Great explanation!

    Douglas Zulu

    (July 10, 2020 - 10:33 pm)

    Totally enjoying the M code

    Excel 24/7

    (July 10, 2020 - 10:33 pm)

    Hey Mike,

    check out my solution for your given example which works without a custom function:


    Abhay Gadiya

    (July 10, 2020 - 10:33 pm)

    Other approach I would take:

    1. Create a Pivot Table from raw data
    2. Put Date, Product, Region and Category Column in "Row Area" of pivot table
    3. Create a Calculated Field for revenue as per formula and put in Value area
    4. Put Product in Report filter area
    5. Go to PivotTable Tools (ribbon) > PivotTable Options > Show Filter Pages

    Excel will create all separate sheets for products. I feel this is easy and faster compared to Power Query.

Leave a Reply

Your email address will not be published. Required fields are marked *