Teach on Udemy

Turn what you know into an opportunity and reach millions around the world.

Learn More

Your cart is empty.

Keep shopping

Power BI Desktop Combo - Query Editor, Data Modelling, DAX

Name: Power BI Desktop Combo - Query Editor, Data Modelling, DAX
Rating: 4.4 (67 reviews)

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

Created byAbhay Gadiya

Last updated 6/2020

English

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

Course content

15 sections • 103 lectures • 11h 49m total length

Introduction to Power BI and Query Editor7:26
Download and Install Power BI Desktop5:05
Download and install Power BI Desktop, launch the app, and explore the home view. Import data using manual entry or Get Data, and examine the data model and relationships.
Getting familiar with Power BI desktop and Query Editor9:18
Get familiar with Power BI Desktop and the Query Editor, exploring data sources and transformations. Learn how queries record steps, and load transformed data into the data model.

Import Data from Excel File7:06
Import data from Excel files into Power BI Desktop using Query Editor, covering sheet, named range, and table sources; adjust headers and data types, then apply to load data.
Import data from Text - Tab delimited file3:08
Import a tab-delimited text file in Power BI Desktop using Get Data, adjust the delimiter, promote headers, remove unwanted columns and errors, then apply.
Import data from CSV delimited file4:35
import data from csv delimited files into Power BI Desktop using the query editor, remove grand totals, remove the top row, adjust data types, and fix import errors.
Import data from Access files3:39
Loading data to data model and file path changes8:46

In built Column Transformation15:54
Learn how to rename, add, remove, split, merge, and rearrange columns in Power BI Desktop's query editor using mouse clicks, with unpivot, transpose, and data-type adjustments.
In built Row transformations10:02
Explore built-in row transformations in Power BI's query editor, including removing or keeping rows, removing duplicates and blanks, filtering, and grouping by unique items with aggregates.
Filtering selected rows in Power Query9:08
Learn how to filter data in Power Query, including text, numeric, and date filters, with case sensitivity, multiple-column conditions, and dynamic date options.

Introduction to Append / Duplicate / Reference Queries2:40
Master append, duplicate, and reference queries in Power BI desktop to combine monthly sales data from multiple files, either individually or from a folder, and create dependent transformations.
Append multiple files individually with different queries5:25
Import multiple CSV files in Power BI Desktop and append them with the query editor to create a data source, ensuring column headers in files to build unified data model.
Importing multiple files from a folder using single query6:43
Append the Data from multiple excel sheets and multiples files in folder6:26
Import and append data from multiple Excel files and sheets in a folder using Power BI Desktop, configuring query steps to consolidate all sheets into a single dataset.
Duplicate / Reference queries - Differences and usage5:46
Power BI desktop duplicate and reference queries explained: duplicates are independent replicas with identical steps, while references depend on the original and inherit its changes.

Introduction to building blocks in M5:11
Explore the building blocks of M in Power Query language, including case sensitivity, single-column data types, and zero-based indexing with no IntelliSense; learn to access text, date, and conditional functions.
Text functions in M14:02
Explore text functions in m within Power BI Desktop, including insertion, removal, and extraction in custom columns, plus using Power Query help and function categories.
Date functions in M8:48
Learn date functions in M within Power BI's query editor, including converting text to date, adding days or weeks, and current month checks for data transformation.
Conditional functions in M - IF, AND, OR12:01
Column from example9:00
Learn how Power BI's column from example in the query editor automatically extracts text details, using selection vs all columns, and shows functions used, with lowercase and months, quarters, years.

Financial Statements7:33
Transform and load an Excel financial statements dataset in Power BI using Power Query, merging and transposing columns to create a clean, columnar dataset for analysis and visualization.
Payroll Data8:34
Stacked data to columnar data - Address Book7:34
Transform stacked data in an address book into a columnar format in Power Query Editor by using an index column and a conditional column to separate each person's details.
Multiple filters on single column6:42
Rows to columnar data set7:45
Looking up discount rates12:02
Identify discount rates in Power BI by building a lookup table across region, group, and product, then merge queries in Power Query to apply the correct discount rate.
Invoice data5:00
Transform scattered invoice data into a clean columnar format in Power BI using Query Editor, transpose and unpivot, split by delimiter, rename columns, and set proper data types for analytics.

Power Query Objects Introduction0:54
Explore Power Query objects such as lists, records, and tables, learn their syntax, and see how to use these objects within the Query Editor to solve tasks.
List as object and List functions in Power Query12:30
Explore power query lists as objects, from single-column lists to lists of lists with curly braces. Create, transform, and convert them to tables using list functions in Power BI Desktop.
Record as object and Record functions in Power Query9:32
Table & other objects and Table functions in Power Query3:10
Explore Power Query objects such as lists, records, and tables, and learn how table functions operate and how lists or records convert to tables for transformations in Power Query.
Power Query Objects - Recap1:15
Recap Power Query objects, including lists, tables, values, binaries, records, and functions. Show how lists are single-column data and records are single-row; convert to tables via contextual tabs.

Advanced Case Studies introduction2:01
Extracting only relevant data14:24
Creating dynamic Calendar table9:49
Individual sales to total sales percentage7:51
Calculate the percentage of each product category and subcategory against the grand total in Power BI using the query editor, group by, and DAX calculations.
Multiple filters on a single column - part 23:47
Import a country list embedded in a single cell, split it into rows by delimiter, and clean spaces. Then apply an interzone to filter GDP data for matching countries.
Leave Data Part - Split to individual rows from range of dates9:18

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 Query
Power Pivot
Power View

Power 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 mainly

Data 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.

Who this course is for:

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

Power BI Desktop Combo - Query Editor, Data Modelling, DAX

What you'll learn

Explore related topics

Course content

Introduction3 lectures • 22min

Importing Data into Power BI5 lectures • 27min

Transformation in built in Query ribbon3 lectures • 35min

Other in built transformation4 lectures • 44min

Multiple data source / queries5 lectures • 27min

Merge Queries / Multiple Joins in Power Query4 lectures • 31min

Building blocks for M language5 lectures • 49min

Case Studies - Part 17 lectures • 55min

Power Query Objects5 lectures • 27min

Advanced Case Studies - Part 26 lectures • 47min

Requirements

Description

Who this course is for: