Sign up for our free Insiders program and get the example file: exceloffthegrid.com/insiders File Reference for this video: 0232 Quick Variance Analysis
Love it. Gave me some great ideas for putting PQ data back into Excel as a Table and from there use formulas. I seldom put data back as a table. Usually into Data Mode3l and Pivots from there. This opened my eyes for doing stuff this way. Thanks.
I find the calculation engine of standard PivotTables is too limited. So, it’s either Tables + Formulas or Data Model + DAX. I view them as equivalent technologies where scenario determines which to choose.
@@ExcelOffTheGrid I just tried and feel very satisfied after click 'REFRESH'. Excellent! Your video is really helpful! I have a question. I try to add a column to show the employee's name. I used this code ' =UNIQUE(VSTACK(Current_Month[Name],Prior_Month[Name]))' . I am not sure if the names would align with their ID numbers which were shown in the 'Ref' column. Thanks!
@@jaimesantana2834 I believe so, as I notice that there's an option (from SharePoint folder) for us to choose. I didn't try it. You may give it a try. :)
@@ExcelOffTheGrid Oh, I know the techniques. The problem is, other people don't know these techniques, and I am left dealing with their poorly setup spreadsheets.
Yes, you can. th-cam.com/video/AijmQ5uuwGw/w-d-xo.html But PQ vs Formulas methods are not interchangeable. You need to understand whether you are in the data phase or calculation phase to decide which method to use.
This is great if you have reports coming in on an expected cadence, but I’m wondering if you have a solution to do this same thing with previous versions of the file on SharePoint through its past history of updates for 1 wk/1 mo/1 yr?
The # and Tables are different. Tables expand with data (i.e. the raw inputs), while the # expands based on the calculations on the table. You need to use both together.
I just got a short note for the chapter variance analysis, and i think i have to share that with you all.... th-cam.com/video/dbyMOLKSd9Q/w-d-xo.htmlfeature=shared
Sign up for our free Insiders program and get the example file:
exceloffthegrid.com/insiders
File Reference for this video: 0232 Quick Variance Analysis
I have been loading the named range into a query to create a parameter. I didn't realize you could do it direct! 🤯
You get the same code either way, so doesn’t make a huge difference. I just find it faster to have a code snippet.
Love the new setup Mark.
Thanks Chandeep - just trying to up my TH-cam game. 😎
This is a fantastic video on Payroll Variance Analysis that you can literally use at work.
Thanks Mark.
Love it. Gave me some great ideas for putting PQ data back into Excel as a Table and from there use formulas. I seldom put data back as a table. Usually into Data Mode3l and Pivots from there. This opened my eyes for doing stuff this way. Thanks.
I find the calculation engine of standard PivotTables is too limited. So, it’s either Tables + Formulas or Data Model + DAX.
I view them as equivalent technologies where scenario determines which to choose.
It is a fairly refined method, much more practical than doing it in "wild mode" using functions, without using Power Query. Thank you, Mark.
I agree - It's the combo of PQ and DAs which creates the true POWER!
Like this video very much! I would watch it several times and then work my own file alongside with your video. Thanks for sharing this!
Great news - I’m glad it’s helpful. 😁
Hi, it is possible to make it work with a file in sharepoint?…thanks
@@ExcelOffTheGrid I just tried and feel very satisfied after click 'REFRESH'. Excellent! Your video is really helpful! I have a question. I try to add a column to show the employee's name. I used this code ' =UNIQUE(VSTACK(Current_Month[Name],Prior_Month[Name]))' . I am not sure if the names would align with their ID numbers which were shown in the 'Ref' column. Thanks!
@@jaimesantana2834 I believe so, as I notice that there's an option (from SharePoint folder) for us to choose. I didn't try it. You may give it a try. :)
Brilliant video! I've been designing a payroll variance analysis tool in PowerQuery all week. Perfect timing! and thank you
Perfect timing - I'm glad it was helpful!
That is some crazy efficiency there and some great business wisdom about the purpose of the analysis. Thank you!
Thanks, I'm glad you found it useful.
Another great video showing how far one can go with cleverly setting up the workflow!
Thanks. It's all pretty simply once you know the techniques.
@@ExcelOffTheGrid Oh, I know the techniques. The problem is, other people don't know these techniques, and I am left dealing with their poorly setup spreadsheets.
This is amazing! I always learn new techniques that help streamline my work! Thank you so much for sharing! ☺
Amazing and very very useful 👌
Great - I'm like you liked it. 😁
Power query ❤❤❤
Why do you do the hashes after cell references (in SUMIFS, for example)?
Great video !! Great integrated solution !! ✌
Thank you. 😁
Very useful
Can do the same with PQ only?
Will be very insightful
Yes, you can. th-cam.com/video/AijmQ5uuwGw/w-d-xo.html
But PQ vs Formulas methods are not interchangeable. You need to understand whether you are in the data phase or calculation phase to decide which method to use.
Thanks for sharing the other video ❤
Really this videi is awesome
Can u share one video for more than 2 periods for ex 2022 2023 2024 2025 sales
Can you use groupby function instead of hstack and compute each column?
You need a complete list of reference numbers to identify what is missing from one or the other. So you will need something to combine values.
Brilliant 👍❤, thanks Mark
Thanks Kebin - as always, I appreciate your support. 😁
Thanks Mark
Brilliant!
Thanks for watching. 👍
Excellent.
Many thanks!
This is brilliant ❤
quick and clean 👍
That's the goal - quick and clean 😁
Brilliant Mark, thanks!
Thanks Chris, I'm glad you enjoyed it.
Spot on!
Thanks - hopefully you can put it to good use.
Great video, but how do I change the path if I had the file on sharepoint, I use web.content or file.content
Please your help, thanks
A file on SharePoint is Web.Content.
This is great if you have reports coming in on an expected cadence, but I’m wondering if you have a solution to do this same thing with previous versions of the file on SharePoint through its past history of updates for 1 wk/1 mo/1 yr?
Yes, you can use SharePoint - though it will use different connectors at the start.
And Yes, you can do it with any time periods.
Very useful! Thanks
This is awesome. Thanks much
You're welcome.
Great video
Glad you enjoyed it 😁
Love it 👍
Thank you! Cheers!
Now i know value of the hash #. Better than using a table?
The # and Tables are different.
Tables expand with data (i.e. the raw inputs), while the # expands based on the calculations on the table.
You need to use both together.
@@ExcelOffTheGrid I guess because VSTACK used, then hashtag for other columns.
@@ExcelOffTheGrid I guess bc VSTACK used, then need to use hashtag for other columns.
while replacing name file path , i am getting error path not found
❤❤❤
Thanks Grainne, I appreciate that. 😁
I just got a short note for the chapter variance analysis, and i think i have to share that with you all....
th-cam.com/video/dbyMOLKSd9Q/w-d-xo.htmlfeature=shared