Funky Funktion Friday – Part 1/3 Excel’s Full Outer Join in Power Query

/in /by

Today’s FunkyFunktionFriday continues from last week and explores the joins in Excel’s Power Query (Get & Transform).

We start with a list of people who were invited to a party, and a list of people who are coming and the food they’re bringing. It would be great to have a single list that not only brings the data together but also matches people who are on both lists.

Please, check out the video,

My book: Guerrilla Data Analysis 2nd Edition
http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336

My blog: http://datascopic.net/blog2-2

“Protofunk” Kevin MacLeod (incompetech.com)
Licensed under Creative Commons: By Attribution 3.0 License
http://creativecommons.org/licenses/by/3.0/

Excel & Commissions Calculations: a Discussion with George Mount, Oz du Soleil

/in /by

Both George Mount and Oz du Soleil have spent time as Commissions Analysts. Today, George and Oz discuss ways of handling negative numbers since Payroll can’t process a check for a negative amount.

How do George and Oz address this in their Excel models?
And what are other challenges that face real-world Commissions Analysts?

The stress of dealing with money. That’ll keep an analyst focused and sober. Other challenges are the facts that data constantly changes, and yesterday’s reports often fail to reflect today’s business complexities. Thus, a lot of work is done in Excel.

George Mount can be found at:
http://georgejmount.com/
https://www.linkedin.com/in/gjmount

http://hiredwithexcel.com/ebook/
https://www.instagram.com/gjmount/

Oz du Soleil:
http://DataScopic.net
https://www.youtube.com/c/OzduSoleilDATA
http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336
https://mvp.microsoft.com/en-us/PublicProfile/5001226?fullName=Oz%20du%20Soleil

Funky Function Friday: Excel Get & Transform – Anti Join

/in /by

This Funky Funktion Friday we look at the Anti Join in Excel’s Get & Transform (aka Power Query).

The mission: matching songs & artists in an old list to find out what’s still missing on the new list. Thus, we do an anti-join to eliminate matches.

The twist: a lot of songs have the same name, adn there are multiple songs by certain artists. So, this can only work if we match BOTH the song title AND the artist.

To do this easily, the Excel development team gives us the almighty Anti-Join.

My book: Guerrilla Data Analysis 2nd Edition
http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336

My blog: http://datascopic.net/blog2-2

Music used:
Style Funk Kevin MacLeod (http://incompetech.com)
Licensed under Creative Commons: By Attribution 3.0 License
http://creativecommons.org/licenses/by/3.0/

FunkyFunktionFriday: Find the Capital Letter to Separate Text

/in /by

Here’s the problem:
WyattD
KWilliam
UBrian

We need to separate the First Name and Last Initial but, for some names, the Last Initial is out front. So, we need to turn these into:

Wyatt D
William K
Brian U

Solution: CODE and Flash Fill

My book: Guerrilla Data Analysis 2nd Edition
http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336

My blog: http://datascopic.net/blog2-2

Accent Tag – Chicago (Maybe Texas, Louisiana or Tennessee)

/in /by

Here’s my raw accent. The one that I try to cover over in my Excel tutorials.

I recently discovered Accent Tag videos and decided to do one. I’m often asked about my accent and I don’t know where it came from. Most of my life has been spent in Chicago, but I’m asked if I’m from places I’ve never been before.

If there’s a linguist out there who can help solve this mystery, I’d be glad to buy you some coffee. :)

My blog: http://datascopic.net/blog2-2

Guerrilla Data Analysis 2nd Edition
http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336

http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336

SSSVEDA Day 30: Last Day, Thank Yous and Lessons Leaerned

/in /by

SSSVEDA 2016 has been great. A lot of good content was created. I’ve appreciated the comments, support and feedback. So, here’s a video thanking y’all, and sharing lessons I learned.

LESSONS
– A project like this reveals what a person gives a damn about. Being committed to something like this isn’t something you can phone in. So, whether it’s conscious or not, sticking with this is discovery of what matters in regards to content, style, and the medium.

– Commit to a creative idea and see if through.
The days that I missed were because I had so many creative ideas and kept thinking of more and more and more.

I think my best videos happened when I went all-in on an idea and had fun.

Please. It’d be great to have you as a subscriber. Let me hear from you. Let me know what you’d like to see. I don’t want to be the only one having fun. :)

Guerrilla Data Analysis 2nd Edition

My blog: http://datascopic.net/blog2-2

http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336

SSSVEDA Day 29: Crossfooting & Conditional Formatting

/in /by

TIMELINE
0:00 – Context. Explanation of the need for cross-footing. “How would you know if the numbers are wrong?”
2:59 – My ghost pepper coffee recipe
4:57 – Example of Cross-footing

This video begins with a story about an accountant who was annoying the hell out of me. She asked, “how would you know if your numbers are wrong?”

My response was, basically, “because I’m experienced and know what I’m doing.” That wasn’t good enough for her. This lead to her teaching me the valuable concept of ‘cross-footing.’

‘Cross-footing’ is a way of ensuring that you’ve accounted for all of your data and it adds up correctly. This is so important that I added a section on cross-footing in my book, Guerrilla Data Analysis, 2nd Ed.

In the second half of the video I share a simple example of crossfooting. It’s a real-world example of a non-profit that receives donated items, and a way of keeping track of them all.

Along the way, you get to see my coffee recipe. :)

Guerrilla Data Analysis 2nd Edition

My blog: http://datascopic.net/blog2-2

http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336

SSSVEDA Day 28: Relative and Absolute Cell References

/in /by

Today: a review of a fundamental element in Excel formulas: relative and absolute cell references.

Guerrilla Data Analysis 2nd Edition

My blog: http://datascopic.net/blog2-2

http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336

SSSVEDA Day 27: Calculating Wine Bottles, Glasses, Ounces & Milliliters

/in /by

Download the workbook here: http://datascopic.net/wine-calc

This video is the result of someone at a wine restaurant who said he had transaction data by the glass and bottle but wanted a final calculation of “number of bottles” at the end of the week.

Here’s high-speed footage of my actual development. I had no idea how I’d approach this until I turned the camera on.

Please subscribe to my channel and let me know your real-world needs with Excel. Maybe it’ll turn into a video.

Guerrilla Data Analysis 2nd Edition

My blog: http://datascopic.net/blog2-2