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: […]
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 […]
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 […]
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 […]
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 […]
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 […]
More about working with Excel’s Get & Transform (aka Power Query). this time, we explore merging columns and the “gotchas” that you need to know about. 0:49 Description of the 3 warnings 1:09 Showing the problem 2:00 Problem 1: The source columns are gone 2:17 Problem 2: The columns are out of order 2:50 Merge […]
0:00 – what we’re up to 1:21 – looking at the problem 5:12 – a word “going rogue” as an effective style 6:09 – starting the solution 6:29 – using RANDBETWEEN to make up some data 7:14 – making dropdown lists 8:07 – INDEX-MATCH-MATCH making the 2-way lookup 10:00 – summary and final comments Someone […]
“Reference a Query” is a feature in Excel’s Get and Transform, and it’s not obvious what it does. From the outside it looks the same as Duplicate Query. This video shows an example of when you’d reference a query and how the feature is used. Here, we received a dataset that looks like 2 datasets […]