Episode 3 in a series about how to use Microsoft’s Dashboard In A Day (DIAD) dataset to understand the basics of working with Power BI.
This episode is an introduction to Microsoft’s Power Query ETL (Extract, Transform and Load) tool that is embedded in Excel and Power BI. I take you through a set of simple transformations that we need to apply to the DIAD dataset. At the end I explain how to use the Data Profiling tools, which are very helpful in understanding whether a field has unique values and can be used as the Primary Key of a table.
Previous episode https://www.youtube.com/watch?v=KeOfsiUbrK8
Microsoft’s page about the Dashboard In A Day (DIAD) course: https://powerbi.microsoft.com/en-us/instructor-led-training/#section_diad
Download the content for the course including the dataset here: https://aka.ms/diad_student
Further details about the course on the OP-Training website: https://op-training.co.uk/diad-landing-page/
Content links for this video
Introduction
1. Opening and closing the Power Query window https://youtu.be/_THnaSb3CLM?t=25
2. The query pane https://youtu.be/_THnaSb3CLM?t=80
3. The applied steps, with an example https://youtu.be/_THnaSb3CLM?t=125
4. Enabling the formula bar https://youtu.be/_THnaSb3CLM?t=242
5. Making user-friendly names for the tables that will appear in the data model https://youtu.be/_THnaSb3CLM?t=307
Applying the transformations to the data
6. Correcting the data types in the Sales query https://youtu.be/_THnaSb3CLM?t=363
7. Correcting the data types and applying other transformations to the International Sales query https://youtu.be/_THnaSb3CLM?t=487
8. Applying Split to the Product column in the Product table; applying Column From Examples to the Price column; applying Fill Down to the Category column https://youtu.be/_THnaSb3CLM?t=635
9. Transformations on the Geography table: removing top rows, use first row as headers, correcting data types https://youtu.be/_THnaSb3CLM?t=1034
10. Transformations on the Manufacturer table: transpose, using choose columns to remove several columns in one step https://youtu.be/_THnaSb3CLM?t=1144
Finishing off: data preview tools in Power Query
11. The data preview tools https://youtu.be/_THnaSb3CLM?t=1224
12. Opening and closing the query settings pane https://youtu.be/_THnaSb3CLM?t=1395