Awesome tutorial on an absolutely fundamental topic in data analytics. More than tabular data structure, the Data layout is key concept and it is indeed at the heart of the Dplyr in R, with the emphasis on tidy data (variables in columns, observations in rows and values in cells). So important to the point hey have a dedicated pakage, tidyR just to reshape datasets. Thank you once again for sheding light on the matter and contributing to drive Excel to new hights with a clear and consisitent path to approach data manipulation and analysis. You are truly a legend.
Great video, dealing with different data structures is often the hardest part of solving problems and isn’t discussed that often. I’d love to see more videos like this.
I had a different approach to finding the average term in the unpivoted column. At first I thought it would be easier, but after I finished I'm not so sure. But without a lookup function, my gut tells me it might be quicker. =AVERAGE(CHOOSECOLS(UNIQUE(FILTER(Unpivoted[[Property]:[Term (Yrs)]],Unpivoted[Type]=J10)),3)) I also think unpivoted is better than pivoted, so it should have a score of 5 or 6.
It took me a second to see what you had done there. My only thought is that you're relying on the Term (Yrs) being the 3rd column. So I think the following with HSTACK could be more robust as we know the Term (Yrs) is always the 2nd column, even if somebody moves it. =AVERAGE(CHOOSECOLS(UNIQUE(FILTER(HSTACK(Unpvioted[Property],Unpivoted[Term (Yrs)]),Unpivoted[Type]=J10)),2))
Great video, Mark. Thanks. Ideally, it could have also factored in the score related to the preparatory work needed to get to the three different starting points.
The preparatory work is almost irrelevant - as we will should be using Power Query no matter the situation. Therefore, it will all update with a refresh.
I feel like structuring the data correctly can solve or at least mitigate a lot of challenges. You end up devising the craziest formulas and tricks, because you're working to conform to the structure instead of making the structure conform to you. It really pays to learn some basic data modeling theory, for example, dimension tables (here your "Property, Type and Term Yrs table) vs. fact tables (here your Property, Year and Value table). IMO, mixing the two table types can be done, but it's rarely pretty or easy to work with, and they tend to only get worse as they grow! I know you know all of this, Mark, but this is for viewers who may not! :)
Great video and very helpful for different scenarios at work. Just one humble request, if you can please remove zap sound (or any sound!) from slide transitions. I listen to videos using ear plugs and while focusing on a topic and if suddenly zapping sound comes, it hurts. Thank you for your amazing work! ❤
Thanks for the feedback about the sound, that is really useful.. Is the sound just too loud? If I reduce the volume significantly, to make it subtle, will that help? Or is it the frequencies of that sound which are the issue?
@@ExcelOffTheGrid Could you show how to do the same thing for pivotted table with duplicate values, say I have my department column divide into 2 sections, upper section is Inpatient and and lower section is Outpatient but the departments are the same, how would I do it, thanks
6:20 your formula is for unique property names when terms are the same every year. How about if the same property has different terms every year ? Great video by the way. I love your teaching format, instead of giving the correct formula from the start, you build your formula and explain each step and what we need to do to reach the goal. It helps me realize mistakes and how to look at what the formulas are doing so I can correct it (Example putting the double dash to turn text years into number format)
Thanks, I appreciate that feedback. If you have different terms each year, then the granularity is by lease and by year. So it will be the same as the Value calculation, but will use AVERAGE instead of SUM.
![[UA] NAVI проти G2 Esports | Blast Premier Fall Final](http://i.ytimg.com/vi/QWlIm4FGESQ/mqdefault.jpg)
Awesome tutorial on an absolutely fundamental topic in data analytics. More than tabular data structure, the Data layout is key concept and it is indeed at the heart of the Dplyr in R, with the emphasis on tidy data (variables in columns, observations in rows and values in cells). So important to the point hey have a dedicated pakage, tidyR just to reshape datasets.
Thank you once again for sheding light on the matter and contributing to drive Excel to new hights with a clear and consisitent path to approach data manipulation and analysis.
You are truly a legend.
Yes, it’s certainly not just an Excel issue. It’a fundamental to any data analysis. ✅
Great video, dealing with different data structures is often the hardest part of solving problems and isn’t discussed that often. I’d love to see more videos like this.
I’m glad you found it so useful.
Don’t worry, more videos coming soon.
Sign up for our free Insiders program and get the example file:
exceloffthegrid.com/insiders
File Reference for this video: 0213 Data structure
I had a different approach to finding the average term in the unpivoted column. At first I thought it would be easier, but after I finished I'm not so sure. But without a lookup function, my gut tells me it might be quicker.
=AVERAGE(CHOOSECOLS(UNIQUE(FILTER(Unpivoted[[Property]:[Term (Yrs)]],Unpivoted[Type]=J10)),3))
I also think unpivoted is better than pivoted, so it should have a score of 5 or 6.
It took me a second to see what you had done there. My only thought is that you're relying on the Term (Yrs) being the 3rd column.
So I think the following with HSTACK could be more robust as we know the Term (Yrs) is always the 2nd column, even if somebody moves it.
=AVERAGE(CHOOSECOLS(UNIQUE(FILTER(HSTACK(Unpvioted[Property],Unpivoted[Term (Yrs)]),Unpivoted[Type]=J10)),2))
@@ExcelOffTheGrid Good thinking.
Great video, Mark. Thanks.
Ideally, it could have also factored in the score related to the preparatory work needed to get to the three different starting points.
The preparatory work is almost irrelevant - as we will should be using Power Query no matter the situation. Therefore, it will all update with a refresh.
Sir kindly make a video
Sendkeys for onother program data entry please
I feel like structuring the data correctly can solve or at least mitigate a lot of challenges. You end up devising the craziest formulas and tricks, because you're working to conform to the structure instead of making the structure conform to you. It really pays to learn some basic data modeling theory, for example, dimension tables (here your "Property, Type and Term Yrs table) vs. fact tables (here your Property, Year and Value table). IMO, mixing the two table types can be done, but it's rarely pretty or easy to work with, and they tend to only get worse as they grow!
I know you know all of this, Mark, but this is for viewers who may not! :)
100% - Couldn't have said it better. 👍
Great video and very helpful for different scenarios at work. Just one humble request, if you can please remove zap sound (or any sound!) from slide transitions. I listen to videos using ear plugs and while focusing on a topic and if suddenly zapping sound comes, it hurts. Thank you for your amazing work! ❤
Thanks for the feedback about the sound, that is really useful..
Is the sound just too loud? If I reduce the volume significantly, to make it subtle, will that help? Or is it the frequencies of that sound which are the issue?
Great video about a great concept, your explanation is right on the money as well.
Thanks, Mark. :-)
Thanks. I know you know this stuff already. So I also know you appreciate how important it is.
Wow! What a tutorial. Really important and very useful 👌
Thanks 😁
aim to simplify !, thank for this demonstration ;-)
Exactly - use Power Query to make your life easier.
your videos are highly educative, keep doing this type of sessions, please
Thanks, will do! 👍
FILTER is more awesome than i realized. solved a huge problem for me.
Yes, FILTER is awesome!
Make sure you check out last week’s video for more FILTER techniques.
I've been looking for this for so long. You helped me solved99% of my problems at work
99% - that’s awesome 😁
@@ExcelOffTheGrid Could you show how to do the same thing for pivotted table with duplicate values, say I have my department column divide into 2 sections, upper section is Inpatient and and lower section is Outpatient but the departments are the same, how would I do it, thanks
Great explanation. Many times we find pivoted data structures. Power Query is always a great help. Thanks Mark.
Thanks Ivan. Glad it was helpful!
Super!! Awsome!!
Thank you! Cheers! 😁
Loved it!!
Thanks Chris 😁
Great video thank you!
My pleasure! Glad you liked it. 😁
Brilliant as always, thanks Mark
Thanks Kebin 👍
6:20 your formula is for unique property names when terms are the same every year. How about if the same property has different terms every year ?
Great video by the way.
I love your teaching format, instead of giving the correct formula from the start, you build your formula and explain each step and what we need to do to reach the goal. It helps me realize mistakes and how to look at what the formulas are doing so I can correct it (Example putting the double dash to turn text years into number format)
Thanks, I appreciate that feedback.
If you have different terms each year, then the granularity is by lease and by year. So it will be the same as the Value calculation, but will use AVERAGE instead of SUM.