Power Query tutorial - Data cleansing 101

In this video I use Discovery Medical Aid's claim report as a use case to cover more than 20 function's using Excel's Power Query to create a repeatable model for cleansing the report and to create a summary dashboard of claims. Now my report never needs cleansing again, just a download and refresh. Power Query will change your life! Download the example file here: https://drive.google.com/file/d/1JUWxNX982sr7vWqT_T-JmJjmTgbcHsij/view?usp=share_link Access the Google Form to submit ideas and questions for future videos here: https://forms.gle/dDtKupkSzhTjCjVU8 Thanks for watching. Please LIKE, SUBSCRIBE and leave a COMMENT! ▬ Contents of this video ▬▬▬▬▬▬▬▬▬▬ 0:00 - Intro 0:45 - The report 1:11 - How to extract your Discovery claims report 1:57 - The end product - dashboard 2:38 - The functions we're covering 4:15 - Start with a blank workbook 4:20 - Data from CSV 5:00 - Transform data 5:16 - Duplicate query 5:39 - Remove top rows 6:10 - Fixing column headings over multiple rows 7:23 - Blank vs. null 7:35 - Replace values - blanks with nulls 8:10 - Inserting a step before another 8:45 - Merge Columns 9:20 - Remove empty column 9:30 - Transpose 10:05 - Trim 10:50 - Promoting Headers 11:05 - Error handling 12:00 - Conditional Custom Column 13:00 - Text.Start 14:00 - Fill Up 14:30 - Reorder column 15:00 - Delete irrelevant rows 15:20 - Text Filter Does not contain 16:00 - Filter 16:44 - Fill down 17:40 - Change Types 18:40 - Keep Error rows 19:05 - Data type conversion to decimal numbers error 19:45 - Decimal comma instead of decimal point 20:43 - Split Column by delimiter 21:34 - Rename columns 22:00 - Reason code reference table 22:15 - Rename query 23:15 - Keep bottom rows 24:20 - Remove bottom rows 24:36 - Remove empty rows 24:42 - Remove other columns 25:00 - Merge / Join / VLOOKUP Queries 26:50 - Table Expand 28:05 - The Advanced Editor 28:30 - Close and Load - Back to Excel 28:55 - Connection only to Table 29:28 - Table design 29:40 - Formatting the data in the table 30:08 - Pivot Table creation and setup 32:00 - Checking totals to original report 32:50 - Inserting Slicers 33:25 - Disabling autofit column widths 34:10 - Aligning and ordering slicers 36:00 - What happens next month 36:58 - Outro