Are you tired or repetitive manual work in Excel? Of doing the same tasks again and again?
Imagine if you knew how to do these tasks instantly. Imagine how much time you could save .. How much more you could get done. How much progress you could make in your career?
In this video, I will introduce to the best easy way to automate manual work in Excel: Power Query
🔥DOWNLOAD FILES LINK
tinyurl.com/swm8e5b5
00:00 Power Query In Excel
00:33 Technique 1: Cleanse Data
Jobs such as removing blank or duplicate rows, removing columns and sorting data in Excel are onerous and repetitive. Power Query provides simple options for instantly cleansing data – saving you huge amounts of time and effort. After loading two Excel tables into Power Query, let’s explore the options in the Home tab in Power Query, and see how to instantly load your cleansed data back to the spreadsheet. Cool!
04:54 Technique 2: Transform Data
‘Parsing’ data is another task that takes a lot of time in Excel. Parsing refers to separating out text data by a ‘delimiter’ – a specific character. You might want to use a comma (,) as a delimiter to parse an address into individual lines, for example. We’ve all had to do it at some point!
Power Query provides a range of useful options to ‘transform’ your data, including parsing a column into multiple columns. Let’s use the ‘Split Column’ option in the Transform tab to parse out goal times for each game.
08:31 Technique 3: Add Columns
The Add Columns tab in Power Query allows us to add useful calculated information to a table. In our case, we would like to create a ‘match code’ to reference the games across different datasets. More on that later! Power Query allows you to add columns to your data in Excel.
We’ll do two things in Power Query’s ‘Add Column’ tab. First, we’ll insert a column to display the first three characters of each team. Second, we’ll ‘concatenate’ or join together the three-character codes to create a ‘match code’ for each game. These custom columns use Microsoft’s ‘M’ language and can take a little time to set up. But, once they are set, Power Query does all the work for you in the background.
12:26 Technique 4: Collate Tables
Power Query not only consolidates tables within Excel files, it can bring in data from other files, from online spreadsheets – and other sources too. In this video, we’ll collate data from within the file, but make sure you explore the other possibilities of Power Query …
In Power Query terminology, to ‘merge’ tables means to add columns. To ‘append’ tables, by contrast, means to add rows. So, if you’re looking to attach additional data to rows, data that happens to live in another table (it happens!), you need to merge. If you’re looking to stack two or more tables together which, broadly speaking, comprise the same columns – then you need to append.
In this video, I use the ‘match code’ we created previously to match rows between two tables and merge them together, bringing the goal times data into the merged table. This completes the data cleansing, transformation, calculation and collation jobs we want to do. All with the magic of Power Query!
But there’s more. We complete the whole task by appending the table to the previous week’s data, creating a single consolidated output.
If you enjoyed this session, you’ll love this 30-minute video where we did deeper into the magic of Power Query. You’ll be automating more manual work in no time. See you there!
- Learn Excel's BEST Automation Tool In 15 Minutes! ( Download)
- Learn Excel VBA to Automate Anything ( Download)
- Use Excel Like a PRO | Learn Power Query, Power Pivot & DAX in 15 MINUTES (project files included!) ( Download)
- Learn Power Query & Automate Boring Data Tasks in 15 Minutes! ( Download)
- Learn Excel MACROS in 10 Minutes Using Real World Examples ( Download)
- 20 Excel Shortcuts to Save You HOURS of Work ( Download)
- Learn Excel Power Query to Automate Boring Tasks ( Download)
- Master Data Analysis on Excel in Just 10 Minutes ( Download)
- From Classroom To Career - Global Scholarships and Career Paths in Physiology ( Download)
- Excel Budget Template | Automate your budget in 15 minutes ( Download)
- Make an Awesome Excel Dashboard in Just 15 Minutes ( Download)
- AUTOMATE EXCEL USING CHATGPT ( Download)
- Top 10 Essential Excel Formulas for Analysts in 2024 ( Download)
- MS Excel - Macros - Automate your Spreadsheets in ONE CLICK!! ( Download)
- Excel Magic Trick 543: Build Time Schedule With 15 Minute Intervals ( Download)
