Sign up for our free Insiders program and get the example file: exceloffthegrid.com/insiders-signup/ File Reference for this video: 0223 Combine Tables - PQ v VSTACK
I like the part "When to use each". Sometimes people know the formulas or power query, but they do use that in wrong situations and... as mentioned, leading to future problems.
Exactly. - Trying to use Power Query as a calculation engine leads to inflexible workbooks. - Trying to use formulas for reshaping data leads to a lot of copy/paste and manual actions. We need to use the correct tool at the correct point in the process.
Tip: I work with files with multiple tables containing different data types and layouts, but I prefix them all with 'tbl' (also using 'pvt' for pivot tables, 'qry' for queries, etc.). In this situation, filtering just on 'tbl' would give really disjoined data. But joining on 'tblSales_' will only give me the specific subset I may want, be it Monthly or yearly, by line, by customer, etc., depending on how the data for the project is organized. Naming is key. And if the naming is good, keeping the original table names may prove quite useful too.
Hello Mark, is it possible to create a dynamic and filterable column total by using SUBTOTAL(109, ...) inside the VSTACK() function? I think this is an important feature to create dynamic tables (via formulas) but it always gives me an error message... any idea from your side? This might also be an interesting topic for a new video. Thanks in advance and greetings from Germany. Love your videos!
I've got a video coming this week that might answer your question. Have you checked out any of my videos about slicers? That might give you some ideas: ua-cam.com/video/hOMgjI_Tlt8/v-deo.html ua-cam.com/video/jFU5Cdd7Ys8/v-deo.html ua-cam.com/video/v-vrEtCMKiI/v-deo.html
Dear Mark, With the new TRIMRANGE function, it's even easier to combine tables with dynamic formulas: =TRIMRANGE(SORT(VSTACK('Start Sheet:End Sheet'!B5:E104))) 🤗
I'm on the beta channel and even I don't have TRIMRANGE yet; so I can only comment from a theoretical point of view. I think you would need to TRIMRANGE before using VSTACK. VSTACK will change the blanks to 0's. Therefore, there will be no blanks to trim. TRIMRANGE does not work on 3D references, so you couldn't use a Start and End Sheet, you would need to hard reference any sheets. So, I'm not convinced that it would work... but I might be wrong.
@@ExcelOffTheGrid Dear Mark, Unfortunately, your theoretical analysis is completely wrong. I wouldn't send you a formula if I hadn't tested it first. So, the TRIMRANGE function considers 0 (zeros) as blank spaces, and works perfectly in 3D references. I used the SORT function so that it would place all the zeros at the end of the stack, so that they could be trimmed by the TRIMRANGE function, otherwise it would fail, since the stacked tables have different numbers of rows. I forgot to say that I am also in the Beta channel, Microsoft Excel for Microsoft 365 MSO (Version 2409 Build 16.0.18025.20006) 64-bit version. 🤗
This is very useful thank you. When i first read the video name, i was expecting something else. When i produce reports, I often have to dig an information through separate variables, meaning it would need several pivot tables or several groupbys in PQ. For instance, if i want a report showing the percentage of male female, then stacked under it, the percentage of 3 to 14 yo people/15-34 yo etc then another one with the percentage of people leaving in London vs rest of the country etc etc. Is there a known method to easily create a report that would include these ?
I'm not aware of an easy method to do that - as every scenario could be different. So I think you've just got to go through the steps to create what you need.
I'm familiar with your work, and know and like starting Table names with "tbl", but in this instance, why not either leave it as "Data" or name it "TblData" so that the second filter wasn't needed? It's worth mentioning that renaming a PQ Table after it's been loaded will still direct the Query to load to the same place.
It just comes down to an individuals preference as to how important naming conventions are vs convenience. Personally, I don't have a strong preference either way; it comes down to circumstance.
That means there were some required fields thatwhere empty. Please try again. If that's still not working, please reach out through Customer Support and we can help you. exceloffthegrid.com/customer-support
Sign up for our free Insiders program and get the example file:
exceloffthegrid.com/insiders-signup/
File Reference for this video: 0223 Combine Tables - PQ v VSTACK
using let and choosecols for filtering values of vstack is so smart
I was familiar with this method using VSTACK. It is certainly a great way to use it, saving hours of work. Thanks for sharing, Mark.
Thanks Ivan, I'm glad you like it. 👍
I like the part "When to use each". Sometimes people know the formulas or power query, but they do use that in wrong situations and... as mentioned, leading to future problems.
Exactly.
- Trying to use Power Query as a calculation engine leads to inflexible workbooks.
- Trying to use formulas for reshaping data leads to a lot of copy/paste and manual actions.
We need to use the correct tool at the correct point in the process.
... a very inspiring way for VSTACK! Thank you for the detailed presentation. ✨️
Thank You. I'm glad you liked the video! Yes, VSTACK is a great function.
Tip: I work with files with multiple tables containing different data types and layouts, but I prefix them all with 'tbl' (also using 'pvt' for pivot tables, 'qry' for queries, etc.). In this situation, filtering just on 'tbl' would give really disjoined data. But joining on 'tblSales_' will only give me the specific subset I may want, be it Monthly or yearly, by line, by customer, etc., depending on how the data for the project is organized. Naming is key. And if the naming is good, keeping the original table names may prove quite useful too.
Yes - 100%. Naming conventions is critical.
Hello Mark, is it possible to create a dynamic and filterable column total by using SUBTOTAL(109, ...) inside the VSTACK() function?
I think this is an important feature to create dynamic tables (via formulas) but it always gives me an error message... any idea from your side? This might also be an interesting topic for a new video. Thanks in advance and greetings from Germany. Love your videos!
I've got a video coming this week that might answer your question. Have you checked out any of my videos about slicers? That might give you some ideas:
ua-cam.com/video/hOMgjI_Tlt8/v-deo.html
ua-cam.com/video/jFU5Cdd7Ys8/v-deo.html
ua-cam.com/video/v-vrEtCMKiI/v-deo.html
Dear Mark,
With the new TRIMRANGE function, it's even easier to combine tables with dynamic formulas:
=TRIMRANGE(SORT(VSTACK('Start Sheet:End Sheet'!B5:E104))) 🤗
I'm on the beta channel and even I don't have TRIMRANGE yet; so I can only comment from a theoretical point of view.
I think you would need to TRIMRANGE before using VSTACK. VSTACK will change the blanks to 0's. Therefore, there will be no blanks to trim.
TRIMRANGE does not work on 3D references, so you couldn't use a Start and End Sheet, you would need to hard reference any sheets.
So, I'm not convinced that it would work... but I might be wrong.
@@ExcelOffTheGrid Dear Mark,
Unfortunately, your theoretical analysis is completely wrong.
I wouldn't send you a formula if I hadn't tested it first.
So, the TRIMRANGE function considers 0 (zeros) as blank spaces, and works perfectly in 3D references.
I used the SORT function so that it would place all the zeros at the end of the stack, so that they could be trimmed by the TRIMRANGE function, otherwise it would fail, since the stacked tables have different numbers of rows.
I forgot to say that I am also in the Beta channel, Microsoft Excel for Microsoft 365 MSO (Version 2409 Build 16.0.18025.20006) 64-bit version. 🤗
Interesting - thanks for confirming.
At the moment, I don’t have TRIMRANGE. So, I appreciate you following up and confirming.
This is very useful thank you.
When i first read the video name, i was expecting something else.
When i produce reports, I often have to dig an information through separate variables, meaning it would need several pivot tables or several groupbys in PQ. For instance, if i want a report showing the percentage of male female, then stacked under it, the percentage of 3 to 14 yo people/15-34 yo etc then another one with the percentage of people leaving in London vs rest of the country etc etc.
Is there a known method to easily create a report that would include these ?
I'm not aware of an easy method to do that - as every scenario could be different. So I think you've just got to go through the steps to create what you need.
@@ExcelOffTheGrid thanks for taking the time to answer
Brilliant as always. Thanks Mark 👍
Thanks Kebin - I appreciate your on-going encouragement 😁
I'm familiar with your work, and know and like starting Table names with "tbl", but in this instance, why not either leave it as "Data" or name it "TblData" so that the second filter wasn't needed? It's worth mentioning that renaming a PQ Table after it's been loaded will still direct the Query to load to the same place.
It just comes down to an individuals preference as to how important naming conventions are vs convenience. Personally, I don't have a strong preference either way; it comes down to circumstance.
Very good insights!
Thank you. Glad it was helpful!
Awesome ❤
Thanks 😁
Thanks.
Btw, I got
"Oops! Please provide the missing fields" when signing up your program
That means there were some required fields thatwhere empty. Please try again. If that's still not working, please reach out through Customer Support and we can help you.
exceloffthegrid.com/customer-support