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

Download Files:

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

[JoeG]

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

[StopWhining]

Thanks for another great video. What makes it great? You explain why; you relate what you're doing to how the interface reflects what you did. You're my hero!

[ryanbartlett]

You continue to amaze, sir. Thanks again

[muhammadfathi]

Thank you. Can’t even begin to express how much this is appreciated at this video.

[rajkirandhyani]

Hi sir, i have a question, in very previous unpivot video "Excel Magic Trick 1357?, when u made function by changed the M code, at that time u delete the first line (source=Current.Workbook()) confused

[elit]

❤ Tha’s really nice but what if you don’t want user to enter PQ to change the query name? Thank you!!!

[MichaelJamesActually]

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?

[KKimKKolwiek]

Hi Mike! I need to do similar exercise but have 20+ sheets to be created and list of the sheets might change. I cannot creat each of the sheets manually as you did on the movie. Is there any way to do it more automatically. Like a second query that has a list of parameters that is used to extract those additional queries?

[leetran]

any method in power query to split automatically?. thanks

[sumardjo]

thanks for all
i have learn to sample file
thank you very much
goooddd

[SiyamandRashid]

Thank you very much for the great video, is there a way to automatically split the table into multiple tables without providing the variable?

[markbaxter]

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

[sandyholt]

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?

[crapformyfriends]

So, while I may be a mouth-breathing knuckle-drager (consider your source) and a little s-faced, on a odd-Friday during a pandemic, I stand on a soap-box and proclaim into the void of the writhing inter-webs of toxic communication that this man, with his radio like inflection (sped up to 1.75x) MAKES excel fun! not that 'excel is fun....on it's own', but that this guy MAKES IT FUCKING FUN! Fuck that- if you think excel is fun on it's own- but this resource, with others (curbal and the other dood that wears the f-ing weird hats) kills it (when giving people the down and dirty about all the nooks and crannies of nuance that excel has to offer)! I'm not going to say that anyone is single-handedly responsible for my learning (I'm not THAT drunk) but I will say is this: GARVIN (is it Girvin?) IS. CRUSHING... IT! This is where I started when I had began to have questions, and this is where I ALWAYS come back to. Girvin, brotha, I will happily watch all the commercials for you. Don't lead me astray. Don't let me down. I'll follow you to hell. keep up the good work!

[mishey]

instead of worksheets, is it possible to split to different workbooks?

[iankr]

This is great. Very clearly explained. Many thanks.🙂

[abhaygadiya]

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.

[jarrettb]

I don't normally comment on YouTube video but I have to say this is GENIUS, AWESOME, I consider myself a strong Excel User but this video is probably the most useful video I've seen on Excel, I can do so much with this... Thank you!!!

[giangpham]

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?

[davidgreen]

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