Excel Expert Live Challenge - Helen Wall - Shifts & Overtime

  Рет қаралды 2,419

Excel On Fire (Oz du Soleil)

Excel On Fire (Oz du Soleil)

Күн бұрын

Пікірлер: 32
@tracywilliams61
@tracywilliams61 6 ай бұрын
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 ❤❤❤
@OzduSoleilDATA
@OzduSoleilDATA 5 ай бұрын
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. 🙏🏽
@DimEarly
@DimEarly 6 ай бұрын
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!
@OzduSoleilDATA
@OzduSoleilDATA 6 ай бұрын
That’s something I learned as well. I’d never seen someone paste data into Power Query until Helen did it. 🤯
@kebincui
@kebincui 6 ай бұрын
When you load the power query and add to data model, then you can use distinct count in pivot table
@OzduSoleilDATA
@OzduSoleilDATA 5 ай бұрын
Thanks for the input! Now, we ALL learned something more. 🙏🏽
@eshetiedametie8295
@eshetiedametie8295 6 ай бұрын
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.
@OzduSoleilDATA
@OzduSoleilDATA 6 ай бұрын
I recently proposed to someone that she could give me a challenge. We’ll see. 🤷🏽‍♂️
@eshetiedametie8295
@eshetiedametie8295 6 ай бұрын
Great! and thanks for respectfully responding to my wish.
@wcthrill
@wcthrill 2 ай бұрын
Would be cool if the data c/b downloaded for anyone to work on
@OzduSoleilDATA
@OzduSoleilDATA 2 ай бұрын
The workbook is available through a card in the video.
@ferdirosa7547
@ferdirosa7547 5 ай бұрын
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.
@OzduSoleilDATA
@OzduSoleilDATA 5 ай бұрын
I'm so glad you're learning. 👏🏽👏🏽👏🏽BTW: the file is available. the card in the video has the link.
@mathteacher1729
@mathteacher1729 6 ай бұрын
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?
@OzduSoleilDATA
@OzduSoleilDATA 6 ай бұрын
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. 🤯
@aman_mashetty5185
@aman_mashetty5185 6 ай бұрын
Totally loved it...🥰🥰🤩🤩
@OzduSoleilDATA
@OzduSoleilDATA 6 ай бұрын
Thank you ☺
@ExcelWithChris
@ExcelWithChris 6 ай бұрын
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
@mrpage221
@mrpage221 6 ай бұрын
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?
@mrbartuss1
@mrbartuss1 6 ай бұрын
Could you please share the file so we could follow along?
@OzduSoleilDATA
@OzduSoleilDATA 6 ай бұрын
The video is available in a card in the video
@xyclos
@xyclos 6 ай бұрын
Great!
@chrism9037
@chrism9037 6 ай бұрын
I think I would have used UNIQUE wrapped in COUNTA to calculate the distinct number of people
@OzduSoleilDATA
@OzduSoleilDATA 5 ай бұрын
That would do it!
@barttitulaerexcelbart9400
@barttitulaerexcelbart9400 5 ай бұрын
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.
@OzduSoleilDATA
@OzduSoleilDATA 5 ай бұрын
Thanks Bart! Yeah. It shows that we're all human. 🤗 So much to learn.
@jacobadams4915
@jacobadams4915 6 ай бұрын
Man it’s great the see that smile! Stay smokey!
@OzduSoleilDATA
@OzduSoleilDATA 6 ай бұрын
ABSOLUTELY! 💨💨💨💨💨 ☺
@ivanmamchych5802
@ivanmamchych5802 6 ай бұрын
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.
@OzduSoleilDATA
@OzduSoleilDATA 6 ай бұрын
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.
@ivanmamchych5802
@ivanmamchych5802 6 ай бұрын
I have got your idea. That makes sense then. Thank you.
Вопрос Ребром - Джиган
43:52
Gazgolder
Рет қаралды 3,8 МЛН
🎈🎈🎈😲 #tiktok #shorts
0:28
Byungari 병아리언니
Рет қаралды 4,5 МЛН
Excel Expert Live Challenge with Bill Jelen: Reception Party Guests
24:21
Excel On Fire (Oz du Soleil)
Рет қаралды 5 М.
Excel Experts Live Challenge: Diarmuid Early vs. The Jumbled Course Completions
32:02
Excel On Fire (Oz du Soleil)
Рет қаралды 4,3 М.
Excel Expert Challenge with Wyn Hopkins of @AccessAnalytic : Votes and Data Quality
53:59
Excel On Fire (Oz du Soleil)
Рет қаралды 2,8 М.
New: Semi-Joins in Power Query + a Brief Overview of Power Query's Outer and Inner Joins
9:09
24 - Group by and Text Combine in Power Query
16:56
WesleySon
Рет қаралды 440
Chandoo takes the Excel Expert Live Challenge | PALINDROMES!
33:37
Excel On Fire (Oz du Soleil)
Рет қаралды 4,4 М.
The New PIVOTBY Function + Warnings + a Weird Thing about COUNTA
11:46
Excel On Fire (Oz du Soleil)
Рет қаралды 2,3 М.
Вопрос Ребром - Джиган
43:52
Gazgolder
Рет қаралды 3,8 МЛН