Wait Wait Don’t Excel Me! It’s trivia time. I’m going to give you some clues about a formula or an Excel or a Power Query term and you were going to guess what term or formula of which I am thinking. The first person will score a point.
Clue One:
Q: In Excel, this function gives you the power to round up, down, and even alter a number’s format but your beloved pets might be terrified at the sound of it especially during mating season. What formula am I thinking of that might terrify your pet?
Tick-tock. Tick-tock. Tick-tock. Drawing blanks from the panel.
This particular Excel formula will do the rounding but will also change the format of this number like the text function, but all in one.
Answer: FIXED Function.
Fixed allows you to format and round the number in one action.
Ken says I just use ‘Round’ function and then format it.
Rick says ‘Round’ won’t scare kittens. Ken needs to step up his game!
More than one way to skin a cat, Szilvia notes as we move to…
Clue two:
Q: Attach ‘play’ to this Excel function name and you’ve got a sexy trend that’s quite popular with your weird costume wearing performance artist friends. But among your equally weird Excel friends, this is probably just trigonometry?
Jordan. Cosign function. Cos.
Rick says over my head, but I will be Googling it later.
Yes the millennial on the board nails it, notes Szilvia also adding that tonight’s game is sponsored by Google Images.
Answer: Cos Returns the cosine of a number.
Clue three:
Q: This might be a bit of an embarrassment for our host Ken Puls -if he doesn’t get it! Robot in disguise or another name for Power Query? What ribbon term am I thinking of?
Ken: Are we talking Get and Transform? Drop the Get. Got it.
Answer: Transform.
Don’t over think this? What would Oz D du Soleil do? Look for transformers at all of Ken’s future sessions!
Clue four:
Q: This sounds more like a fraternity name but in the M language this one claims to return the smallest possible number. What Power Query function am I thinking of? “This is kind of obscure,” admits Szilvia.
Ken: I can go look it up, but I don’t have an answer.
Answer: Number.Epsilon
Clue five:
Q: Looking for a Power Query term. Santa is checkin’ it twice, but maybe Power Query could help him with the vast collection of functions in this category?
Jordan: List. Correct!
Ken: There are tons of list category functions in Power Query. There are over 600 Power Query functions, so we don’t cover all of them in my book. You will see this when you do something through the user interface. We teach people how to understand them so they can use them.
Answer: List.
Clue six:
Q: To make sure he doesn’t overcompensate the spoiled kid who already got one present, this handy M function helps Santa avoid dups! If I have a list, what is the list function in M that will help?
Ken guesses: Unique. So close, but the answer is List.Distinct.
Answer: List.Distinct
And there you have the results from the Excel TV expert panel with guest host and Power Query book “M is for (DATA) Monkey author Ken Puls.
Jordan squeaks by with the winning score, but loses by default for knowing CosPlay!
- Jon Acampora of ExcelCampus.com - October 5, 2017
- Nick Green of EchoXL.com - September 14, 2017
- Ken Puls of ExcelGuru.ca - July 26, 2017
