Here’s another video that offers warnings about Excel’s Get and Transform (Power Query).
Get and Transform does a lot of exciting things, but there are some things that are still better done in native Excel, and there unexpected ways you can be stung.
So, rather than how-to, this video warns about ways to get stung if you don’t know better. Read more →
Problem: a tall column of data that needs to be unstacked and converted into neat columns and rows.
Twist: the distinction where the list needs to be divided is by bold font.
(Download the workbook:
http://datascopic.net/unstackingboldfont )
Parents’ names are bold. Childrens’ names are regular font.
How does this list get unstacked with the children next to the right parent?
Kevin Lehrbass and I are here to offer several solutions. I open with Excel’s Get and Transform (Power Query). Kevin takes over and shows a formula-based solution and a VBA solution!
Three whole solutions for you! Cleansing this data and getting this stuff unstacked!
Check out Kevin’s channel:
My book: Guerrilla Data Analysis 2nd Edition
http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336
My blog: http://datascopic.net/blog-2-2
Excel’s Get & Transform is a fantastic tool, but there are some warnings that you should be aware of before you get stung. (Watch out for the scorpion)
This video shows 3 ways you can get stung:
3:20 – 6:24
Column headers are hard-coded. If you need a column header to be variable, it’s a bit tricky and requires going into the M Code in Get and Transform’s advanced editor.
6:32 – 6:56
Some things in Get & Transform start counting at 0 instead of 1. You’ll see where I point to column 4 but have to change it to column 3.
7:10 – 8:47
A number and text cannot be put together in the same cell. In this video I want to get a result like: 2000 sqft.
The number has to be formatted as text in order to be combined with ‘sqft.’
Have a look. Let me know if you have questions.
And please subscribe to the channel.
My book: Guerrilla Data Analysis 2nd Edition
My blog: http://datascopic.net/blog-2-2
Someone posted an urgent request. Some contacts from a small list had gotten combined with a larger list.
Question: how can I compare the 2 lists and extract the ones that shouldn’t be in the big list?
This video shows 3 ways:
Formulas:
– VLOOKUP
– MATCH and
Get and Transform (Power Query)
– Left Anti-Join
I hope you enjoy. Please subscribe to the channel, and let me know if you have any Excel questions!
My book: Guerrilla Data Analysis 2nd Edition
My blog: http://datascopic.net/blog-2-2
In the previous video I showed how to retrieve Word data and bring it into Excel. However, Nisha in Chicago noticed that the original data was in lists, but I didn’t retain the lists when the data was brought into Excel.
(The original video:
Nisha asked, what it would take to get the data into lists.
In this video I use the functions CHAR and SUBSTITUTE to insert line-breaks. Also, “wrap text” is required so that Excel can display the data in lists.
My book: Guerrilla Data Analysis 2nd Edition
http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336
My blog: http://datascopic.net/blog-2-2
Data is in tables in Microsoft Word and needs to be moved over to Excel–and retain the table structure.
PROBLEM: copy from Word and into Excel creates a mess. Items that were in the same cell in Word are in different cells in Excel.
The solution is to replace ^p in the document with something that can be used as a delimiter in Excel.
Notice in the video I used zzzz and then changed to a double-pipe as a delimiter. I could have used the double-pipe first, but it’s something I figured out during the editing of this video.
I had tried ** as a delimiter and that caused problems as I eventually realized that asterisks are seen as wildcards and aren’t good as delimiters if you’re going to apply Find/Replace.
My book: Guerrilla Data Analysis 2nd Edition
My blog: http://datascopic.net/blog-2-2
Once again, Kevin Lehrbass (MySpreadsheetLab) and I are taking on a common challenge and comparing each others’ solutions.
Today’s challenge: if you have 10 students in a class, and would like each student to work with a different student each week, how can you get a list of all of the unique pairs?
My favorite solution that Kevin created uses pivot tables and helper columns. Check out the details of Kevin’s solutions here:
Kevin’s blogpost
I present 2 solutions:
1. A Matrix solution that pairs each student.
2. Get & Transform. This uses a cross-join aka Cartesian-join and it’s beautiful! I also show how easy it is to get the unique pairs from 22 students.
This video takes you through a detailed approach to solving this problem in Excel.
ModelOff Global Training Camp – Toronto
My book: Guerrilla Data Analysis 2nd Edition
http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336
My blog: http://datascopic.net/blog-2-2
FREE EXCEL ONLINE TRAINING (THIS WEEK ONLY): Power Query & Data Cleansing Techniques!
SIGN UP HERE: http://myexcelonline.com/blog/datacleansingsignup
~ Receive 3 FREE training videos that will show you how to save HOURS EACH DAY using Excel’s built in features…including Formulas, Text to Columns, Pivot Tables plus much more!
~Learn the most powerful feature in Excel since VLOOKUP…it’s called POWER QUERY & it will rock your Excel world!!!!!
~ Transform messy data & automate your daily/weekly/monthly reports within minutes (NOT DAYS!)…without using VBA or Macros!
LEARN NOW: http://myexcelonline.com/blog/datacleansingsignup
My book: Guerrilla Data Analysis 2nd Edition
My blog: http://datascopic.net/blog-2-2
Taking you for a drive around Portland, OR while I answer 10 questions about travel and I’ve tagged:
Mr Excel, Bill Jelen and
St. Lucian vocalist, Sherwin Dupes Brice
https://www.youtube.com/channel/UCDJWTHHxVpOBmyTwuLYkd1w
I also mention my favorite sriracha:
Ghost Pepper Sriracha from California Blazin’ Chile Farms (I am not compensated for this shout-out, at all)
https://www.etsy.com/listing/165113213/blazing-dragon-ghost-pepper-sriracha
Amy Schmittauer’s Wanderlust Tag Video
And here are the questions!
WANDERLUST TRAVEL TAG QUESTIONS:
1. Your most treasured passport stamp?
2. Can you recite your passport # from memory if asked?
3. Preferred method of travel; planes, trains or automobiles?
4. Top 3 travel items?
5. Hostel or hotel?
6. Are you a repeat visitor or do you explore new places?
7. Do you read up on your destination (culture, history, safety) or do you wing it?
8. Favorite travel website?
9. Where would you recommend a friend to visit? Name city & why.
10, You’re leaving tomorrow, money is no object, where are you going?
My book: Guerrilla Data Analysis 2nd Edition
http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336
My blog: http://datascopic.net/blog-2-2