This course is not the only course in our training center dedicated to business intelligence tools, but we suggest visiting it first of all.
The fact is that Power BI Desktop uses a component called Power Query to receive and transform data. For professional reporting, the ability to work with this component is necessary.
Power BI Desktop uses DAX, a language, to provide advanced business computing. For professional reporting, the ability to create expressions in this language is also required.
This course covers both Power Query and DAX, but not separately from Power BI Desktop, but in close connection with it and with each other, thus providing the most complete amount of knowledge, skills and abilities to work with BI tools from Microsoft.
After completing this course, students will be able to:
- Master the hottest BI tool - Power BI Desktop.
- Embrace the data culture. Master the principles of building data models, distributing data into tables (and tables into fields), plan relationships between tables and configure these relationships, and much more.
- Learn how to load data from different sources (text files, Excel workbooks, databases, web pages, Google spreadsheets, Share Point lists, various cloud storages, etc.) and link them together to form a data model.
- You will be able to carry out a step-by-step transformation of this data, with the help of which “bad” data can be brought to a form that will allow further use of this data.
- Learn how to extend the data model by creating so-called data models in DAX. calculated fields and measures.
- Learn how to visualize your data by creating interactive, visually appealing, informative and user-friendly reports.
- Learn how to use the Power BI cloud service to collaborate on reports.
The audience for this course is very broad. It is an absolute must visit for every data scientist who wants to learn how to use the most powerful tools of the Power BI platform for analysis.
Before attending this course, students must have:
- Experience in Windows, Office. Good skills in Excel (sorting and filtering data, subtotals, smart tables, pivot tables, charts, VLOOKUP - required). Experience in Access or any other relational DBMS preferred.
Important! Power BI Desktop uses Power Query to retrieve and transform data. In our opinion, it is better to study Power Query in the Excel environment, and then consider the differences between the Excel environment and the Power BI Desktop environment. Therefore, this unit is included in this training, and we will use Excel to study it.
- Basic concepts
Power BI Desktop
- BI (business intelligence) and business analysis
- Self Service BI (self-service business intelligence)
- Microsoft 365 - Power BI ecosystem
Extract and transform data with Power Query
- Overview of Power BI Desktop. Its architecture and basic principles of operation
- Get and install Power BI Desktop
- Using help
- Modes Report, Data, Model
- Creating and configuring a data model
- Basic principles for creating a data model
- Receiving data. Working with various data sources (text files, Excel workbooks, Access databases, web pages, web services, files in cloud storage, etc.)
- Data transformation. Query editor. The concept of the M language (These topics are discussed in detail in a separate part of the training on Power Query in Excel. Here we consider only those aspects of Power Query , which distinguish its use in Power BI Desktop from its use in Excel)
- Setting table and field properties
- Relationships between tables. Link types. Star and snowflake data model schemas. Reference tables (dimensions) and fact tables
- Installing and configuring relationships between tables. Active and inactive links Direction of cross-filtering
- Using DAX. Calculated columns, measures, and tables. (For more on the topic of using the DAX language, see the document below)
- Relationship Management with USERELATIONSHIP and CALCULATE Functions
- Hierarchies, grouping, clusters
- Working with dates Date tables. Using the CALENDAR and CALENDARAUTO Functions
- Import data model from Excel file
- Working with visualizations, principles of their use, formatting
- Basic visualizations (table, matrix, cards, histogram, graph, pie, etc.)
- More complex visualizations (bubble chart, waterfall chart, KPI, gauge, decomposition tree)
- Working with geographic maps
- Conditional formatting in visualizations. Using DAX in Conditional Formatting
- Using images from the internet
- Analysis and analytics in visualizations. Detailing
- Import custom visuals
- Using Q&A. Synonyms
- Customizing the appearance of the report. Using text fields, images, shapes. Visualization focus mode. Spotlight mode
- Using themes. Report page customization
- Filtering in reports. Slices, slice synchronization. Visualization, page and report filters Detail filters
- Interaction of visualizations. Cross highlight
- Using bookmarks
- Using the Control Buttons
- Custom Tip Pages
- Using the What If option
- DAX and "Quick Actions"
- Dynamic reports with parameters
- Using report templates
- Export report to pdf document
- Some options for Power BI Desktops
Basics of the DAX language, its use in Power BI Desktop
- Introduction: What is Power Query, its scope. The concept of a query, working in the query editor, the concept of the M language
- Using the result of the query. Update requests
- Getting data from various data sources (text files, xml , json , Excel tables , Access databases , data from the Internet, odata , etc.)
- The principle of step-by-step data transformation in the query editor. Actions with steps (change, delete, add a new step)
- Table transformation (filtering, sorting, grouping, splitting columns, transposing, creating calculated columns, etc.)
- Working with text and numeric data. Working with dates and times. Duration data type and its usage
- Working together multiple queries:
- Adding data from multiple queries
- Merging (combining, linking) queries. Join types
- Bulk data loading:
- getting data from all (or many) files in a folder and its subfolders;
- getting data from all (or many) sheets of a workbook
- getting data from all (or many) tables and named ranges of one workbook
- Request management. Grouping, duplication, link to request. View query dependencies Request protection
- M language. Its use to improve queries
- The concept of the language "M", the scope of its application
- Working in the advanced editor
- Fundamentals of the syntax of the M language
- Simple and Structured Data Types
- branch statement IF
- keyword Each
- Built-in language features. Using built-in help
- Custom Functions
- Absolute and relative references in queries
- Error processing
- Requests with parameters
Using the Power BI service
- The concept of the DAX language, its purpose and "habitat"
- Power add -in Pivot , its purpose and "habitat"
- The concept of a data model, its structure, the principles of its construction
- Extracting data from various external sources (Access database, csv file, Excel spreadsheet, etc.)
- Relationships between tables. Link types. Star and snowflake data model schemas. Reference tables (dimensions) and fact tables. Installing and configuring links.
- Visualizing the data model with pivot tables and charts
- Calculated columns, tables, measures
- The concept of context. Row context and filter context
- Creation simple measures . SUM, AVERAGE, MIN, MAX, DISTINCTCOUNT, COUNTA, COUNTROWS functions
- RELATED, IF, DIVIDE, SWITCH, BLANC functions
- Functions CALCULATE, ALL, ALLEXCEPT, ALLSELECTED, HASONEVALUE, FILTER
- VALUES, DISTINCT functions
- Using iterator functions SUMX, AVERAGEX, MAXX, MINX, RANKX, TOPN
- Working with dates Create and customize calendars. calculation funded results . Functions CALENDAR, CALENDARAUTO, NOW, DATE, FORMAT, YEAR, MONTH, DAY, DATESYTD, TOTALYTD, DATESQTD, DATESMTD, ENDOFMONTH, CLOSINGBALANCEMONTH, LASTNONBLANK, DATEADD, PARALLELPERIOD, PREVIOUSMONTH, DATESBETWEEN, FIRSTDATE, LASTDATE
- Using unrelated tables
- Relationship Management with USERELATIONSHIP and CALCULATE Functions
- Using Variables
- DAX and Quick Measures in Power BI Desktop
- Overview of Power BI
- Power BI site structure
- Power BI objects and the relationships between them: visuals, datasets, reports, dashboards (dashboards).
- Receiving data. Working with various data sources
- Publish a Power BI Desktop report to the cloud
- Creation and customization of reports. Making the report public
Export to Power point
- Creating and configuring dashboards (dashboards). Adding tiles. Share dashboards
- Data update. Understanding the Power BI Gateway
- The basics of using workspaces. Setting up access to data, reports, dashboards
- Some Power BI options