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 […]
Thanks for all of the suggestions on what we could do with this CELEBRATION OF 1000 SUBSCRIBERS! In this video I responded to your suggestions and there’s no Excel here. Surprisingly, more people were interested in “a day in the life.” So, you get a bit of that–including a recipe for cooking alligator. I hope […]
In this final video of the 3 part training series on Excel Power Query & Data Cleansing you will learn the different ways to consolidate your various Excel workbooks and worksheets using Power Query. Download the Excel workbooks used in this tutorial to practice: http://myexcelonline.com/blog/wp-content/uploads/2016/06/Data-Cleaning-Power-Query-Training-Video-3.zip This training is for beginners & intermediate Excel users and […]
In this 2nd of a 3 part training series on Excel Power Query & Data Cleansing you will learn the different ways to clean and extract data using Excel formulas and Excel’s built in analytical tools. This training is for beginners & intermediate Excel users and can be applied to Microsoft Excel 2003, 2007, 2010, […]
In this 1st of a 3 part training series on Excel Power Query & Data Cleansing you will learn the different ways to format your messy data using Power Query. Download the Excel workbooks used in this tutorial to practice: http://myexcelonline.com/blog/wp-content/uploads/2016/06/Power-Query-Data-Cleansing-Excel-Workbooks.zip This training is for beginners & intermediate Excel users and can be applied to […]