18:06 I did not know you could just paste data into PQ! 😮 I would have gone for a formula approach for this size of data, but PQ is definitely more future proof if the data is going to grow (a few years and a few locations would already make this big!). Thanks Oz and Helen - I always enjoy watching these!
What a great series, nice to see others going thru a similar process as me. I panic when I’ve been asked to summarize data on the spot because there are many ways to grab the info and feel like I should know the best way instantly. Thank you for taken the pressure off of me knowing I’m not alone. I started my excel journey in the 1980s and up until the 2000s I found it a hassle when I’d have to repeat the process later. Excel was pretty much a manual process especially when the data was text or required cleaning. This is when I switched to Altair Monarch data extraction tool. Monarch did everything plus I could easily repeat and schedule to run automatically with data pump (now called Automator scheduling product. This was until 2022 when I heard about Power Query. One of the problems with Monarch is it has a pretty good learning curve. Power query changed everything I’m in the process of converting all my processes to excel. But the best part is most people have used excel. Over the last year and half I’ve learned so much thanks to your channel ❤❤❤
TRACY!!!! Thank you so so much ... for your comment and for the support. Power Query has changed so many lives and I'm glad to have played a part in your journey. And you confirm that the goal of this series is being met. I wanted people to see that their processes and experiences are normal. We all go through starts and stops and change direction. It was so good to see Helen switch directions and explain why she opted for Power Query. She surprised me by describing how--if for no other reason--Power Query can save you space on your worksheets. It's true. I just never thought to describe that as a benefit. 🙏🏽
This is a great video concept, love the idea of talking through a problem set from start to finish. Keep up the great work! Two quick notes that stood out to me while watching: 20:50 - oh, using a custom delimiter, interesting! I would have selected the resulting columns and done a trim & clean, but if the "; " is uniform in the original data, the custom delimiter is quicker! 24:48 - Distinct count didn't appear b/c that table wasn't loaded as part of the data model. (Requires Power Pivot) Average may have worked though?
Thanks for watching and for the feedback. This is one of the fascinating things about watching other people work. My own habit is to use a custom delimiter to include comma-space. So, I watched to see how Helen would deal with that, then she did a cool thing by modifying the formula/code. Thanks for the insight about the Pivot table attempt. I had no clue about any of that. You and Helen are taking me places I didn't know existed. 🤯
Sir, Thanks for your entertaining and educative videos. I like the way you present it . One day, I expect you yourself being live challenged in this channel.
Thanks! Got my answers in 20 minutes, no power query, just basic Excel formulas! (textsplit, vstack, unique, countif). Almost forgot trim (sneaky of you Oz....) LOL
Dear Brother, can you give us the file in upcoming challenges so that we can try if for ourselves please? Learning Powerquery and Powerpivot was the number 1 most valuable tool for me. Many thanks for all the video's, I learned a ton.
Hi Oz, nice to see such videos. Maybe Hellen Wall should watch your LInkedin Course "Get and tranform" 😉there you explained the advanced option to split columns to rows! Very interesting to see that someone of the stature of Helen with all her programming skills does not know some basic Excel language. Well apparently you can't have it all, and that is Ok !! I saw in the remarks that @DimEarly did not know that you can copy paste data in PQ (enter data). Well I also recently just got this, but for me this is a very interesting issue. In this way my students who work with power BI can now all work on the PQ statements. In the past this was only possible for the one student who originally uploaded the data from his/her laptop.
Thanks Oz, I love these challenges. To make the 2nd and 3rd question in the challenge more dynamic, I came up with this solution: =IFNA(XLOOKUP(SUBSTITUTE(TEXTAFTER(C6," ",-1),"?",""),A2:A15,B2:B15),"Employee Not Found"). Is there a more efficient way to accomplish this?
Thank you for the video, Oz and your guest. Just to note that I didn't see here nothing ADVANCED related to Excel, like I used to see in your other videos. Sorry, but that's a fact. Just an ordinary user level, not an expert one.
What I want to get at in this series is to have someone take us through their thinking, watch how someone else works, and show that we're all human. I loved seeing Helen abandon the formula method and switch to Power Query. I liked seeing her method of dealing with the data types. I'm not striving for advanced. One issue is that if I get too advanced, then the guest might need 3 or 4 hours. I really just want to peel back the curtains to share how others think through solutions, change directions and make decisions.
18:06 I did not know you could just paste data into PQ! 😮
I would have gone for a formula approach for this size of data, but PQ is definitely more future proof if the data is going to grow (a few years and a few locations would already make this big!).
Thanks Oz and Helen - I always enjoy watching these!
That’s something I learned as well. I’d never seen someone paste data into Power Query until Helen did it. 🤯
What a great series, nice to see others going thru a similar process as me. I panic when I’ve been asked to summarize data on the spot because there are many ways to grab the info and feel like I should know the best way instantly. Thank you for taken the pressure off of me knowing I’m not alone. I started my excel journey in the 1980s and up until the 2000s I found it a hassle when I’d have to repeat the process later. Excel was pretty much a manual process especially when the data was text or required cleaning. This is when I switched to Altair Monarch data extraction tool. Monarch did everything plus I could easily repeat and schedule to run automatically with data pump (now called Automator scheduling product. This was until 2022 when I heard about Power Query. One of the problems with Monarch is it has a pretty good learning curve. Power query changed everything I’m in the process of converting all my processes to excel. But the best part is most people have used excel. Over the last year and half I’ve learned so much thanks to your channel ❤❤❤
TRACY!!!! Thank you so so much ... for your comment and for the support.
Power Query has changed so many lives and I'm glad to have played a part in your journey. And you confirm that the goal of this series is being met.
I wanted people to see that their processes and experiences are normal. We all go through starts and stops and change direction. It was so good to see Helen switch directions and explain why she opted for Power Query. She surprised me by describing how--if for no other reason--Power Query can save you space on your worksheets. It's true. I just never thought to describe that as a benefit.
🙏🏽
This is a great video concept, love the idea of talking through a problem set from start to finish. Keep up the great work! Two quick notes that stood out to me while watching:
20:50 - oh, using a custom delimiter, interesting! I would have selected the resulting columns and done a trim & clean, but if the "; " is uniform in the original data, the custom delimiter is quicker!
24:48 - Distinct count didn't appear b/c that table wasn't loaded as part of the data model. (Requires Power Pivot) Average may have worked though?
Thanks for watching and for the feedback. This is one of the fascinating things about watching other people work. My own habit is to use a custom delimiter to include comma-space. So, I watched to see how Helen would deal with that, then she did a cool thing by modifying the formula/code.
Thanks for the insight about the Pivot table attempt. I had no clue about any of that. You and Helen are taking me places I didn't know existed. 🤯
When you load the power query and add to data model, then you can use distinct count in pivot table
Thanks for the input! Now, we ALL learned something more. 🙏🏽
Would be cool if the data c/b downloaded for anyone to work on
The workbook is available through a card in the video.
Sir, Thanks for your entertaining and educative videos. I like the way you present it . One day, I expect you yourself being live challenged in this channel.
I recently proposed to someone that she could give me a challenge. We’ll see. 🤷🏽♂️
Great! and thanks for respectfully responding to my wish.
Totally loved it...🥰🥰🤩🤩
Thank you ☺
Thanks! Got my answers in 20 minutes, no power query, just basic Excel formulas! (textsplit, vstack, unique, countif). Almost forgot trim (sneaky of you Oz....) LOL
Dear Brother, can you give us the file in upcoming challenges so that we can try if for ourselves please? Learning Powerquery and Powerpivot was the number 1 most valuable tool for me. Many thanks for all the video's, I learned a ton.
I'm so glad you're learning. 👏🏽👏🏽👏🏽BTW: the file is available. the card in the video has the link.
Hi Oz, nice to see such videos. Maybe Hellen Wall should watch your LInkedin Course "Get and tranform" 😉there you explained the advanced option to split columns to rows! Very interesting to see that someone of the stature of Helen with all her programming skills does not know some basic Excel language. Well apparently you can't have it all, and that is Ok !! I saw in the remarks that @DimEarly did not know that you can copy paste data in PQ (enter data). Well I also recently just got this, but for me this is a very interesting issue. In this way my students who work with power BI can now all work on the PQ statements. In the past this was only possible for the one student who originally uploaded the data from his/her laptop.
Thanks Bart!
Yeah. It shows that we're all human. 🤗 So much to learn.
Great!
Thanks Oz, I love these challenges. To make the 2nd and 3rd question in the challenge more dynamic, I came up with this solution:
=IFNA(XLOOKUP(SUBSTITUTE(TEXTAFTER(C6," ",-1),"?",""),A2:A15,B2:B15),"Employee Not Found"). Is there a more efficient way to accomplish this?
I think I would have used UNIQUE wrapped in COUNTA to calculate the distinct number of people
That would do it!
Could you please share the file so we could follow along?
The video is available in a card in the video
Man it’s great the see that smile! Stay smokey!
ABSOLUTELY! 💨💨💨💨💨
☺
Thank you for the video, Oz and your guest.
Just to note that I didn't see here nothing ADVANCED related to Excel, like I used to see in your other videos. Sorry, but that's a fact. Just an ordinary user level, not an expert one.
What I want to get at in this series is to have someone take us through their thinking, watch how someone else works, and show that we're all human. I loved seeing Helen abandon the formula method and switch to Power Query. I liked seeing her method of dealing with the data types.
I'm not striving for advanced. One issue is that if I get too advanced, then the guest might need 3 or 4 hours.
I really just want to peel back the curtains to share how others think through solutions, change directions and make decisions.
I have got your idea. That makes sense then. Thank you.