Entries by Oz du Soleil

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

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 […]

Power Query Merge Columns

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 […]

Complex IF Formula Converted to 2-Way Lookup with INDEX-MATCH-MATCH

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 […]

Celebrating 1000 Subscribers to Excel on Fire

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 […]

Excel Power Query Data Cleansing Part 3 Consolidate Workbooks Worksheets Using Power Query

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 […]