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!
I don't normally comment on UA-cam 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!!!
BTW, this is only scratching the surface of the power of Power Query. Here is my playlist with many PQ vids: ua-cam.com/play/PLrRPvpgDmw0m3ohSvgwoHvd0KO8QsQdiK.html&app=desktop
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.
I'm pretty sure you developped this solution because you were pissed not being able to split pivot table into multiple report in the data model. lol. Great video Mr.Excel ! Stay safe !
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?
I have the same question. Is there a way Advanced Editor in Power Query can generate the worksheet for each product, instead of manually making a query for each product?
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?
I do not know how to build the Load or the naming of a sheet inside the Excel workbook into the function. Maybe with VBA? But I just do not know and have not seen a way to do this. Anyone else know?
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.
Because PivotTables aggregate, if there were any records that contains the same data for each column (a type of duplicate record that was not extraneous), the PivotTable Options, Show Filter Pages method would not achieve the goal of Extracting a Complete List of Records. The Array Formulas and Power Query Method would achieve the goal of Extracting a Complete List of Records regardless of whether of not there were duplicates.
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.
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!
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?
thanks alot sir! do u think there might be a way to enter the name in the box and load the table to the sheet without entering the query menu and hit load?
Not that I know of. But we can do a parameter query that I will show in a couple of videos ahead. This is where we enter criteria in cell in Excel and hit refresh and it will update the query with new criteria... It will be a few days to make. It takes a long time to make these videos ...
hi excelisfun, would appreciate alot if you could let me know how do make it more efficient for the master table that again need to reference back to sub table again? beside, dataflow, or table.buffer
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, :)
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?
This Video is so great, but I am looking exactly for the same like you. Means that I want to have this solution for an indifferent amount of products automatically generated. Did you get a solution for that?
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.
@@colleenglavan7151 Hi Colleen. The short answer is no. I have since retired and use Excel a lot less these days. I miss it a little. Lol. Grandkids, golf and Pickleball keep me busy.
Sounds like you need to automate with VBA. I am not good with VBA. If you look in the comments below, Devin Swann posted some VBA code that might be able to do it. Try his code. Post a comment below his code and ask. You can also post Excel questions to THE best Excel question site: mrexcel.com/forum
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???
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.
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
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!
Token of Gratitude!
Thank you so very much for your kind donation, Ankur!!!!
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!
You continue to amaze, sir. Thanks again
Thank you. Can’t even begin to express how much this is appreciated at this video.
You are welcome! Thanks for the Thumbs Up and Sub!
I don't normally comment on UA-cam 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!!!
Yes indeed - Power Query is the greatest invention since the PivotTable back in the 90s.
BTW, this is only scratching the surface of the power of Power Query. Here is my playlist with many PQ vids: ua-cam.com/play/PLrRPvpgDmw0m3ohSvgwoHvd0KO8QsQdiK.html&app=desktop
Can't believe this from 4 years ago. It's just as relevant today!
Much of what we need to do in Excel has been the same or similar for many years : )
Excellent . Thank you so much
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.
I am glad that the videos help! Access does seem to be on the way out...
This is great. Very clearly explained. Many thanks.🙂
Glad it helps. Ian : )
This is a very beautiful tutorial. Thank Mike. 👍
: ) : ) : )
I'm pretty sure you developped this solution because you were pissed not being able to split pivot table into multiple report in the data model. lol. Great video Mr.Excel ! Stay safe !
Thank you Mike, thanks for sharing, I learnt a lot from you. 🤗
Pretty cool idea. I'm gonna implement this to my work about quarters analyses. Better than copy queries with quarter parameters. Thanx lot.
Thank you very much for the great video, is there a way to automatically split the table into multiple tables without providing the variable?
Brilliant vid. Thanks!
Thank you very much Mike!
You are welcome, Shehan!!!
Amazing... Mike, i needed this and fortunately found in your channel
Very well explained. Thanks
Great stuff Mike. Thanks. Power Query in Seattle!!!
Glad ya like it! : )
Another excellent explanation!
Glad you like it, Steven Nye : )
sick trick, and weird that UA-cam suggested exactly this video the day after i was in need of this trick (and no i haven't googled it)
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?
Even I have the same query? Will appricieate your responce on this. Thanks in advance. The tabs should automatically created based on the products.
thanks for all
i have learn to sample file
thank you very much
goooddd
Super-duper video! Thanks Mike!
Thanks, for the super-duper Thank You!! : )
Totally enjoying the M code
Glad you like it! (It is quite fun! : ) )
Great tip - one question, is there a way to automate the generation of new sub reports if the number of products varies?
I have the same question. Is there a way Advanced Editor in Power Query can generate the worksheet for each product, instead of manually making a query for each product?
You are correct with using VBA for this purpose. There are a few examples floating around for this specific purpose. Thanks!
@@bill4123 hi Bill. I am wondering if you have found solution to this? Thanks.
Great Staff Mike. :) :)
Thanks, John Borg : )
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?
I do not know how to build the Load or the naming of a sheet inside the Excel workbook into the function. Maybe with VBA? But I just do not know and have not seen a way to do this. Anyone else know?
An other Question Sir, how can I parameterized 2 things instead of 1, like product name and the name of table where I want to split?
hi Giang. I am wondering if you have found solution to this? Thanks.
Great explanation!
Thanks, Teammate!
Amazing solution from PQ
Glad it is amazing, Ahmed!!! Thanks for your support : )
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.
I totally agree: Options, Show Filter Pages is almost THE best feature in all of Excel!!!!
Because PivotTables aggregate, if there were any records that contains the same data for each column (a type of duplicate record that was not extraneous), the PivotTable Options, Show Filter Pages method would not achieve the goal of Extracting a Complete List of Records. The Array Formulas and Power Query Method would achieve the goal of Extracting a Complete List of Records regardless of whether of not there were duplicates.
@@excelisfun Well, thanks to you, we now know at least two ways to go about it. Again, thank you.
You saved my day good sir. Thanks a lot :)
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.
how come microsoft doesn't add intellisense in the M editor?
❤ Tha’s really nice but what if you don’t want user to enter PQ to change the query name? Thank you!!!
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!
Because the text had no spaces.
Excellent video, Mike. Thanks a lot. :)
You are welcome a lot!
Many thanks sir
Most welcome
Wow, Wow, Wow. Second to none :)
Glad you liked it, as always! : )
Great; thanks a lot Mike; what happens if i have new records with new different ProductName?
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?
instead of worksheets, is it possible to split to different workbooks?
Absolutaley great Mike that funny...thankssss
Glad you like it! You are welcomeeeee!
any method in power query to split automatically?. thanks
great video thank you!
You are welcome, Saodat P!!! Thanks for Your support : )
Thanks for these videos mate. Keep it up! Liked and Subbed :)
thanks alot sir! do u think there might be a way to enter the name in the box and load the table to the sheet without entering the query menu and hit load?
could you please help me out if anything worked with you because i dont know VBA
Not that I know of. But we can do a parameter query that I will show in a couple of videos ahead. This is where we enter criteria in cell in Excel and hit refresh and it will update the query with new criteria... It will be a few days to make. It takes a long time to make these videos ...
ohh this would help too thanks dear for being such a great help
Have you ever considered making a video on how you make the videos? I know I would find that very interesting.
There is a way to enter criteria through the cell in current sheet (parameter table, like advanced filter).
hi excelisfun, would appreciate alot if you could let me know how do make it more efficient for the master table that again need to reference back to sub table again? beside, dataflow, or table.buffer
Thanks a lot! Can we do the same to extract to workbooks, not sheets?
I do not know how to send the data to a new Workbook File like we can do with Formulas or Advanced Filter.
=(
Thank you very much.
Is there any way to split master table to sub table based on month.
I would suggest creating a new column that extract the month from the date and using that column instead of product as you parameter input.
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, :)
I am sorry, I do not understand what you wrote. Can you re-state / re-ask your question?
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?
This Video is so great, but I am looking exactly for the same like you. Means that I want to have this solution for an indifferent amount of products automatically generated. Did you get a solution for that?
@@barbaragerbert1847 No, I have not. :(
hi Sandy. I am wondering if you have solution to this? Thanks.
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.
did anyone ever get back to you about this?
@@colleenglavan7151 Hi Colleen. The short answer is no. I have since retired and use Excel a lot less these days. I miss it a little. Lol. Grandkids, golf and Pickleball keep me busy.
What would you recommend if I have 5 master tables and 100 products. Which method would you use?
Sounds like you need to automate with VBA. I am not good with VBA. If you look in the comments below, Devin Swann posted some VBA code that might be able to do it. Try his code. Post a comment below his code and ask. You can also post Excel questions to THE best Excel question site: mrexcel.com/forum
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???
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.
great video, but Mike why did you call net revenue the item which should be called gross revenue?
Gross Revenue less Discounts equals Net Revenue
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
What if you have hundreds or thousands of product codes? Do we manually do this for every single product?
Thanks but how to split the master file into 5 tables by product at one time?
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!
Glad that I can be of service : )
Sorry my poor english. Thank you.
Your poor English in fine : ) You are welcome for the video!