If you have time to learn just one skill in 2023, make it POWER QUERY. This
single technical skill can help you make massive gains in your data analysis
work.
Power Query is a data pre-processing engine built into Excel and Power BI. You
can use it to bring data from anywhere and do pre-processing or clean-up
activities with ease. It lets you automate common data tasks like removing
duplicates, combining files, merging (joining) datasets, adding derived columns,
splitting or extracting data and changing the shape / size of your data.
It is already there in Excel and Power BI.
In Excel - Go to DATA Ribbon and use the "Get & Transform Data" options.
In Power BI - use the "Get data" or "Transform" buttons.
Power Query and SQL have largely similar capabilities. But Power Query gives you
more freedom and options. Plus, it is easier to learn (in my opinion).
0:00 - One skill you should learn in 2023 - Power Query, what is it?
2:02 - How to activate & use Power Query?
4:43 - STEP 0: Pre-cleaning the data
6:31 - STEP 1: Dealing with Blank data points
7:48 - STEP 2: Removing the duplicates
8:30 - STEP 3: Generating ID values
11:05 - STEP 4: Calculating Course Date from Registration Date
13:25 - STEP 5: Figuring out the Fee Status
15:39 - How to load the cleaned data back to Excel?
16:19 - Analyzing the Power Query data and Refreshing
Sample raw data file - chandoo.org/wp/wp-content/uploads/2022/…
https://chandoo.org/wp/wp-content/uploads/2022/12/one-skill-you-should-focus-on.xlsx
Completed workbook (with Power Query steps) -
chandoo.org/wp/wp-content/uploads/2022/…
https://chandoo.org/wp/wp-content/uploads/2022/12/powerquery-demo-students.xlsx