Power Bi Desktop Combo - Query Editor, Data Modelling, Dax

Learn all about Data Cleansing (ETL), Data Modelling, Relationship, DAX for Power BI Desktop

What you'll learn

Cleanse, Shape, Filter, Stage, Merge and integrate data from multiple sources within Power BI

You will master the data cleansing tasks from Power BI Query editor

Proven development techniques and guidance for implementing custom solutions with M language

Build efficient data retrieval and transformation processes with the M Query Language

Requirements

Person who is really struggling with Data Cleansing tasks and looking for robust solution.

Have used or started using Power BI and want to master data cleansing techniques.

Need to install Power BI Desktop (only available for Windows)

Description

Microsoft Power BI is a suite of Business Intelligence tools, designed to help the BI professionals get easier, quick and crucial business insights from their data. It contains three main tool-set combined in one single software:Power QueryPower PivotPower ViewPower Query lets you to connect to hundreds of data sources, simplify data preparation and drive ad-hoc analysis. It is also know as Power BI Query Editor.Power Pivot is the analytical tool in Power BI. Using various DAX formulas you can drive your analytical journey. This is the second step after you import the data into Power BI using Power BI Query Editor.This course will focus completely on features for Power BI Query Editor, Date Modelling and DAX inside Power BI Desktop.Before you can present any analysis or insight, you need source data. Your source data could be in many places and in many formats. Nonetheless, you need to access it, look at it, and quite possibly clean it up to some extent. You may also need to join separate data sources before you can shape the data into a coherent data set using PowerPivot, deliver the results using Power View or Power Map, and then share it using Power BI.Discovering, loading, cleaning, and modifying source data is where Power BI Query editor comes in. It lets you load, shape and streamline data from multiple sources.Power BI Query Editor allows you to do many things with source data, but the four main steps are likely to be Import data from a wide variety of sources. This covers corporate databases to files, and social media to big data.Merge data from multiple sources into a coherent structure.Shape data into the columns and records that suit your uses.Cleanse your data to make it reliable and easy to use.There was a time when these processes required dedicated teams of IT specialists. Well, not any more. With Power Query, you can mash up your own data so that it is the way you want it and is ready to use as part of your self-service BI solution.This course will start with the basic installation and configuration of Power BI Desktop, and go on to connect your data sources with it. You’ll transform and get your data ready for analysis, and create effective data views using it. You would be performing following tasks mainlyData Discovery —Find and connect to a myriad of data sources containing potentially useful data. This can be from both public and private data sources.Data Loading —Select the data you have examined and load it into Power Query for shaping.Data Modification —Modify the structure of each data table that you have imported, filter and clean the data itself, and then join any separate data sources.Although I have outlined these three steps as if they are completely separate and sequential, the reality is that they often blend into a single process. Indeed there could be many occasions when you will examine the data after it has been loaded into Power Query—or join data tables before you clean them. The core objective will, however, always remain the same: find some data and then load it into Power Query where you can tweak, clean, and shape it.This process could be described simplistically as “First, catch your data.” In the world of data warehousing, the specialists call it ETL, which is short for Extract Transform Load. Despite the reassuring confidence that the acronym brings, this process is rarely a smooth logical progression through a clear-cut series of processes. The reality is often far messier than that. You may often find yourself importing some data, cleaning it, importing some more data from another source, combining the second data set with the first one, cleaning some more, and then repeating many of these operations several times.This course will excite and empower you to get more out of Power BI Query Editor via detailed recipes, development tips and guidance on enhancing existing Power BI Projects.

Overview

Section 1: Introduction

Lecture 1 Introduction to Power BI and Query Editor

Lecture 2 Download and Install Power BI Desktop

Lecture 3 Getting familiar with Power BI desktop and Query Editor

Section 2: Importing Data into Power BI

Lecture 4 Import Data from Excel File

Lecture 5 Import data from Text - Tab delimited file

Lecture 6 Import data from CSV delimited file

Lecture 7 Import data from Access files

Lecture 8 Loading data to data model and file path changes

Section 3: Transformation in built in Query ribbon

Lecture 9 In built Column Transformation

Lecture 10 In built Row transformations

Lecture 11 Filtering selected rows in Power Query

Section 4: Other in built transformation

Lecture 12 Text Transformation

Lecture 13 Numbers transformation

Lecture 14 Date transformation

Lecture 15 Conditional Columns

Section 5: Multiple data source / queries

Lecture 16 Introduction to Append / Duplicate / Reference Queries

Lecture 17 Append multiple files individually with different queries

Lecture 18 Importing multiple files from a folder using single query

Lecture 19 Append the Data from multiple excel sheets and multiples files in folder

Lecture 20 Duplicate / Reference queries - Differences and usage

Section 6: Merge Queries / Multiple Joins in Power Query

Lecture 21 Left, Right and Outer joins

Lecture 22 Left, Right, Outer joins - Part 1

Lecture 23 Left, Right Anti joins - Part 2

Lecture 24 Cross Joins / Cartesian Product using other functions

Section 7: Building blocks for M language

Lecture 25 Introduction to building blocks in M

Lecture 26 Text functions in M

Lecture 27 Date functions in M

Lecture 28 Conditional functions in M - IF, AND, OR

Lecture 29 Column from example

Section 8: Case Studies - Part 1

Lecture 30 Financial Statements

Lecture 31 Payroll Data

Lecture 32 Stacked data to columnar data - Address Book

Lecture 33 Multiple filters on single column

Lecture 34 Rows to columnar data set

Lecture 35 Looking up discount rates

Lecture 36 Invoice data

Section 9: Power Query Objects

Lecture 37 Power Query Objects Introduction

Lecture 38 List as object and List functions in Power Query

Lecture 39 Record as object and Record functions in Power Query

Lecture 40 Table & other objects and Table functions in Power Query

Lecture 41 Power Query Objects - Recap

Section 10: Advanced Case Studies - Part 2

Lecture 42 Advanced Case Studies introduction

Lecture 43 Extracting only relevant data

Lecture 44 Creating dynamic Calendar table

Lecture 45 Individual sales to total sales percentage

Lecture 46 Multiple filters on a single column - part 2

Lecture 47 Leave Data Part - Split to individual rows from range of dates

Section 11: Data Modelling

Lecture 48 Introduction to Power BI and Its components

Lecture 49 Power BI desktop - Download & Install. Learn about desktop layout

Lecture 50 Loading of data in Power BI desktop using Query Editor and separate course

Lecture 51 Data Modelling mindset - Think in multiple tables

Lecture 52 Keys in Tables - Primary Key and Foreign Key

Lecture 53 Introduction to Normalisation of Datasets

Lecture 54 Rule # 1 : First Normal Form - single value and not repeating groups

Lecture 55 Rule # 2 : Second Normal Form - composite primary key

Lecture 56 Rule # 3 : Third Normal Form - dependency between non-key fields

Lecture 57 How to normalize the a dummy dataset by applying normalization rules

Lecture 58 Recap - Three Normalisation rules

Lecture 59 Table types - Data / Fact table and Lookup / Dimension Table

Lecture 60 Relationship Cardinality an introduction

Lecture 61 One to Many relationship

Lecture 62 Many to Many relationship

Lecture 63 One to One relationship

Lecture 64 Relationship cardinality recap

Lecture 65 Relationship in Power BI - Active / In-active, one / both way filter propagation

Lecture 66 Diagram view : Top Down, Star Schema, Snow Flakes - managing table presentation

Section 12: Preparing your data for Dashboards

Lecture 67 Tables related options - Table view, Rename, Hide / Delete

Lecture 68 Columns related - Width, Rename, Sort / Sort by, Hide / Delete, Filters, Groups

Lecture 69 Data Types valid inside Power BI Desktop

Lecture 70 Formatting data inside a column

Lecture 71 How to categorise data inside Power BI Desktop

Lecture 72 How to apply default summarization to a column inside Power BI Desktop

Lecture 73 How to mark a table as Date Table inside Power BI Desktop and why is it required

Lecture 74 How to import Power Pivot data model from Excel into Power BI

Section 13: DAX fundamentals - Power of Power BI desktop

Lecture 75 An introduction to DAX language fundamentals

Lecture 76 Data Types, DAX Operators and DAX Syntax

Lecture 77 DAX Functions list by categories - Table and Scalar values

Lecture 78 Calculated Columns

Lecture 79 Calculated Measures

Lecture 80 Calculated Columns vs Measure

Lecture 81 Filter Context and Row Context

Lecture 82 Golden rules of Measures and other important things

Section 14: Basic DAX function examples

Lecture 83 Introduction to Common DAX functions

Lecture 84 Related and Related Table function

Lecture 85 Date and Time functions

Lecture 86 Fundamental Math Operation using DAX functions and Operators

Lecture 87 Rounding off numbers inside Power BI

Lecture 88 Statistical DAX Functions

Lecture 89 DISTINCTCOUNT function in DAX to count unique customers, etc.

Lecture 90 Text functions

Lecture 91 SWITCH function best alternative to nested IF functions

Section 15: CALCULATE - boss of DAX functions

Lecture 92 CALCULATE an introduction

Lecture 93 CALCULATE syntax explanation

Lecture 94 Examples using CALCULATE function

Lecture 95 FILTER function introduction to syntax, how it works and when to use it.

Lecture 96 FILTER function inside CALCULATE function - Case study

Lecture 97 Context Transition using CALCULATE and FILTER function

Lecture 98 ALL, ALLEXCEPT and ALLSELECTED function inside DAX to remove filter context

Lecture 99 ALL and ALLEXCEPT function case study - single table

Lecture 100 ALL and ALLEXCEPT function case study - multiple tables

Lecture 101 ALLSELECTED function to remove filter context and keep selected filter context

Lecture 102 CALCULATE, FILTER, ALL functions - Similarities and Differences

Lecture 103 Bonus

Users who have used or have started using Power BI.,Person looking for easy alternative to Excel or any other ETL software,This course is for Beginners to pro users as well.,People who want to automate their data preparation task for within Power BI