Segmenting a List into 8 Pieces using Excel’s Power Query (Get and Transform)

/in /by

WARNING: this is a fast-paced overview.

Don’t worry if you can’t follow every step.

The detailed workbook can be downloaded here:
http://datascopic.net/3Stores8Segments
0:29 Overview
2:35 Demonstration of the solution
3:17 How the solution/model works
3:45 Figuring out who HAS NOT made any purchases
4:30 Who’s made purchases at all 3 stores?
7:04 Figuring out who shopped at just 1 store
8:53 Who’s shopped at 2 stores

THE CHALLENGE
My friend Bart in Amsterdam sent a challenge:
In a loyalty program there are cardholders and 3 stores.

How can Excel or Power Query (Get & Transform) segment the list of transactions into 8 segments? People who’ve …

1. not made any purchases
2. purchased at all 3 stores
3. only shopped at Trader Joe’s
4. only shopped at Wegmans
5. only shopped at Publix
6. shopped only at Publix and Trader Joe’s
7. shopped only at Publix and Wegmans
8. shopped only at Trader Joe’s and Wegmans

This solution weaves together lots of Power Query features, including: left anti-join, left outer-join, grouping, fill-up, filtering, and conditional columns.

Power Query Solution over at ExcelIsFun:

Website: https://ozdusoleil.com

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

Creating a Variable Hyperlink in Excel

/in /by

This is cool!

The problem: 11 worksheets and hundreds of codes
Objective: the ability to type a code and be taken directly to that code wherever it is in the workbook.

It’s like the Find/Select feature in Excel, the user wanted to stay in the worksheet and minimize use of the ribbon.

One thing to know about creating hyperlinks.
Regular references to cells can look like: Sheet3!B5
But hyperlinks need to include a ‘#.” Therefore:

#Sheet3!B5

This video shows how to make the dynamic hyperlink. It’s crazy! We have to use COUNTIF, MATCH, OFFSET, INDIRECT, HYPERLINK and helper columns.

Download the workbook here:
http://datascopic.net/hyperlink
This video was recorded at Casa de Montecristo by Cigar Inn at 2nd & 54th in New York City.

Contact me:
oz@datascopic.net

Website: https://ozdusoleil.com

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

Power Query: Summing Data from Specific Ranges

/in /by

This is a CRAZY one!

Kevin Lehrbass presented this problem:
A tall stack of mini data ranges that need to be totaled. BUT!

Only total the ranges that say “TOTAL.” Ignore the ranges that are labeled anything else.

(Kevin’s solution:
https://www.youtube.com/watch?v=oomf8bfcFEw)
Kevin’s video shows various formula solutions. My video shows a Power Query (Get and Transform) solution using:
– Import data from another workbook
– Fill Down
– Add Custom Column
– Grouping with multiple aggregations

Download the solution:
http://datascopic.net/sumranges
Subscription link:

Channel Blurb:

Contact me:

Website: https://ozdusoleil.com

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

How to Stack Columns of Data in Excel: Formula Method, Power Query Method

/in /by

Lately, there have been a number of requests for stacking data that’s in multiple columns.

This video shows 2 methods for stacking 3 columns of data:
– The INDIRECT formula can be used once we know the pattern of how the data needs to be rearranged.
– In Get & Transform (Power Query) it’s simple: add an index column, then “unpivot other columns.” BOOM! DONE!

Website: https://ozdusoleil.com

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

Power Query: Stacking Columns of Data (Unpivot, Split Columns by delimiter, and everything!)

/in /by

This was interesting, and the solution is sweet.

We’ve got headers and a large number of paired columns that need to be condensed down to just 2 columns.

This was a real challenge that was recently presented to me. It was overwhelming, and I searched for existing solutions. The search turned up complex methods that got into M code or complex processes of peeling the data apart and then putting it back together.

But I thought of a simpler way. First …
In my last video I described how to use a small, nonsensical model to focus on a solution. So, I built a model.

(the previous video: https://youtu.be/WkOR6telzdA)

Second. The small model revealed that the last step in our solution should be an UnPivot. Thus, an intermediate step need to be something that looks like it needs to be unpivoted.

Therefore, the trick is in getting the data set up right in Excel BEFORE taking it into Power Query (Get and Transform)!!!

It was so sweet to see how simple the solution was … after seeing it as a very small model.

Check it out!

Contact me: oz1.depot@gmail.com

Website: https://ozdusoleil.com

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

The Wildest Excel Spreadsheet Ever: HLOOKUP and the Importance of Building a Model

/in /by

NOTE: This video isn’t about the details. It’s the thought process. So, please don’t worry if you don’t catch everything.

Download here: http://datascopic.net/messiest

Subscriber, eCabinets Tips and Tricks asked about the most bizarre spreadsheet I’ve ever faced, and what I did to untangle it.

‘Bizarre’ can be measured a lot of ways. I chose this example because it almost beat me. I almost threw up my hands and accepted defeat. It was too big to even try to handle manually.

I’ve faced spreadsheets that had far more complexity, but I could see the solution and it was just a matter of plowing through. But this one … it took a week to find a way to even get started.

It was a spreadsheet that had 2000 rows and over 600 columns. The data in the columns were all out of order, and needed to be moved in groups of 15. My job was to get them all in order.

Two things made a solution possible:
1. HLOOKUP (this is the only time I’ve ever used HLOOKUP. Ever.)
2. Making a small, simple model of the problem that could then be scaled out to the real data.

#2 is what I really want to stress: when a task is overwhelming, slow down. Make a small model. Clear away the noise and just focus on developing a solution.

eCabinets Tip and Tricks
https://www.youtube.com/user/ecabinetstips
Website: https://ozdusoleil.com

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

Excel Power Query: Clear Line-Breaks (A correction to the previous video)

/in /by

Here’s a quick video correcting 2 things from the previous video: Import Data from Web Page and Clear Line-Breaks

Thanks: Wyn Hopkins, Micheal Reynolds and Chris Pope for pointing these out:
1. There was a single carat delimiter that I missed
2. There’s a way in Power Query (Get and Transform) to separate columns by line-feeds and carriage returns.

Have a look! And thanks for being in the battle against Crap Data.

Also, I mention the Amsterdam MVP Excel Summit. It’s 2 days of Excel MVPS teaching the public the greatest about Excel.
It’s 18 and 19 April. Here’s a link for more details:

The Amsterdam Excel Summit

Contact me:
oz1.depot@gmail.com

Website: https://ozdusoleil.com

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

Excel Power Query: Import Data from Web Page and Clear Line-Breaks

/in /by

Power Query has Clean and Trim features, but sometimes those don’t work with line-breaks.

1:49 Importing the data from the Wikipedia page
2:29 Unpivot
3:13 Split columns
3:35 Explanation of the problem of the data being on multiple lines
4:30 Clean & Trim (they don’t work)
4:48 Setting up the data to
5:36 #(lf) to expose the line-feeds
6:22 Split columns
6:50 Unpivot

This video imports data from a Wikipedia page and cleanses it. The problem is each cell has a tall column of data that needs to be parsed. To do this, watch how we have to get crafty because it’s necessary to handle this with M code.

We have to use #(lf)

Along the way, you’ll see:
Unpivot,
Replace Values
Trim and Cleanse
And lots of fire.

Contact me:
oz1.depot@gmail.com

Website: https://ozdusoleil.com

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

Power Query Merging Columns and M Code (managing nulls and excess delimiters)

/in /by

In this video, I go deeper into the issue of clearing excessive delimiters after merging columns in Power Query (Get and Transform).

The previous, related video:

This video describes:
1. Why my hack worked.
I replaced nulls with: ^
Then systematically replaced the ^ symbols. This is much better that merging columns and having lots of delimiters stuck together

2. An M-code solution that Owen Auger helped me with, using
List.RemoveNulls
This is a single step and very clean. However, this solution is in the world of writing code and is great for those who are comfortable with coding.

Check out the previous video to see how this can be handled in native Excel by using the TEXTJOIN function.

Also mentioned in the video; other giants in the world of writing M-code:

Owen Auger
https://owenaugerblog.wordpress.com/
Miguel Escobar
https://www.poweredsolutions.co/blog/
Ken Puls
http://www.excelguru.ca/blog/
Bill Syzyz
https://www.youtube.com/channel/UCx_joCOEjU1KdTpPvu4baYA
Website: https://ozdusoleil.com

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