Power BI Desktop - Query Editor Master data transformation
What you'll learn
- Cleanse, Shape, Filte, 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
- 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)
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 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.
Power View builds dynamic and interactive visualizations, dashboards and reports based on the results of DAX.
This course will focus completely on features for Power BI Query Editor.
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
I am Chartered Accountant, Certified Fraud Examiner and have completed Diploma in Information System Audit (Indian equivalent exam for CISA).
I have circa 16+ years of experience into Data
Analytics, Internal Audits, External (Statutory) Audits, SOX Compliance, Risk
Assessment and Regulatory Compliance. I have audited various entities under BFSI,
Automotive Manufacturing, ITES, Infrastructure and Pharmaceuticals sectors. I
have worked circa 9 years with Big4 consultancy firms – Deloitte, Price
Waterhouse and other with Bank of New York Mellon.
I have conducted various workshops at Institute of Chartered Accountants of India. Approximately 600+ people have attended these workshops and have given extremely positive feedback.