http://datascopic.net/FFF-Modulo

FFF: Powery Query (Get and Transform) – Shifting a column up by a single cell

/in /by

Excel’s PowerQuery (Get & Transform) forces the user to think in terms of full column, full rows, full datasets. This creates a problem:

What if you need to shift a column up by just 1 cell? That’s easy in native Excel. In PowerQuery this takes several steps.

In this example, artists aren’t matched with the right songs. To get them matched properly in PowerQuery, I show:
Index Column
Modulo
Conditional Columns
Fill Up

Check it out!

Download the sample workbook:

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

FFF: Splitting names from a cell that has other data in it

/in /by

The goal is to split a name out of a cell that included the person’s age, and the make, model and year of the car they drive. (download the workbook: http://datascopic.net/ParseGT)

I started this video with the idea of showing you a complex array formula solution. But I kept thinking about an easier way. Ah! I found TWO easier ways:
1. SUBSTITUTE and
2. Get & Transform (Power Query) Split and Merge columns.

The video starts with an explanation of where Funky Funktion Friday has been over the past few weeks. I’ve been on a mission with John Michaloudis of MyExcelOnline. We developed a course that focuses on data cleansing. You can check out the bonus videos on this link:

Now. I’m back. Back to spread the funk!

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

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

Power Query – consolidating 2 columns of data using Don’t Aggregate

/in /by

This video is in response to a question about a previous video where I showed how to unwind some data. (https://www.youtube.com/watch?v=zlEp9IXdaoI)
Delta Sport asked about going in the opposite direction: starting with the unwound data, wind it back up again.

This is challenging. It calls for multiple steps in both native Excel and Power Query. Excel Features you’ll see:

Pivot (in Power Query)
TEXTJOIN
Don’t Aggregate
Merge Columns
Add Custom Column
Index Column

My book: Guerrilla Data Analysis 2nd Edition

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

FFF: Part 3/3: Get & Transform – Custom Columns and Null Values (and Runningman Challenge)

/in /by

For this Funky Funktion Friday, we continue expanding on the party list from the previous videos. This time we have 2 issues:

1. A new column is added to the source data, how do we get it to show up in the final list?
2. We need a sum but Get & Transform doesn’t treat “null” the same as 0.

I show how to handle these in this episode of Funky Funktion Friday.

The previous videos are here:
Full Outer Join

If Statements in Get & Transform

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

Kevin Lehrbass & Oz Compare Solutions: Alphabetizing Titles in Excel

/in /by

Kevin Lehrbass and I went head-to-head and came up with 2 solutions for 1 problem: how can Excel alphabetize a list of titles and ignore: A, An and The?

Kevin uses a sweet array formula while I went the guerrilla route and implement a manual step. We both agree that any solution has to consider the context of what YOU need it for and how frequently it needs to be modified.

Download the sample workbook:
http://datascopic.net/Kevin-Oz-Titles

Check out Kevin’s discussion of the challenge:

Kevin Lehrbass at YouTube:
https://www.youtube.com/user/MySpread…

Kevin’s Blog: My SpreadsheetLab:

Home

My book: Guerrilla Data Analysis 2nd Edition
http://www.amazon.com/Guerrilla-Analy…

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

Music used:
Hitman by Kevin MacLeod is licensed under a Creative Commons Attribution licence (https://creativecommons.org/licenses/…)
Source: http://incompetech.com/music/royalty-…
Artist: http://incompetech.com/

FunkyFunktionFriday: Part 2/3: PowerQuery, IF Statements & Custom Columns

/in /by

Continuing from last week’s FunkyFunktionFriday, we expand on the Full Outer Join and I show you how to write IF statements in the PowerQuery (Get & Transform) editor.

You’ll see how to add a custom column, do more data shaping and boost your overall familiarity with the gifts that continue to flow from PowerQuery.

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

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/