Power BI desktop DAX + M Training Course

Software Overview for Government

• Creating a data model: Power BI Desktop - required
• Microsoft SQL Server Management Studio - optional
• DAX Studio - optional to work with DAX
• Visual Studio Code - optional to work with Power Query M

Working with Power BI for Government - Major Work Steps

• Preparation of data for Power BI Desktop using Power Query.
• Optimization and parameterization of data, including the use of SQL.
• Working with the DAX data model: relationships, tables, calculated columns, calculated tables, and measures.
• Building a report in the Power BI Desktop application.
• Publishing and sharing a report through the Power BI service.
• Managing access control to the data model for government use.
• Reusing a published data model in various government applications.
• Updating a report online as needed.

Power Query M Language for Government

• An overview of the Power Query M language, its applications, and capabilities in government settings.
• Applications of M (Power BI, Power Pivot, Dataverse) in public sector operations.
• The basics and concept of the M language as it pertains to government data management.
• Limitations of the M language and associated risks due to case sensitivity.
• Data updating processes for ensuring current information for government reports.

Data Sources for the Power BI Desktop Model for Government

• File-based data sources: CSV, Excel, JSON, XML, PDF files.
• Internet data sources: tables published on the World Wide Web.
• Streaming data services: OData feeds for real-time data.
• Mass import of files from a folder for comprehensive data collection.
• SharePoint 365 libraries as a file repository, providing an overview of their utility in government operations.
• Relational SQL databases (Microsoft SQL Server by default) for structured data management.

Power Query in Combining and Transforming Data for Government

• Filtering and sorting input data to ensure accuracy and relevance.
• Data quality control: identifying anomalies, errors, and inconsistencies to maintain data integrity for government use.
• Calculation and conditional columns to derive meaningful insights.
• Creation of custom computed columns tailored to specific government needs.
• Direct editing of M code using the formula bar and advanced editor for precise control.
• Duplicating a query versus referencing a query to optimize data processing.
• Transferring queries between applications to streamline workflows.
• Multiple references to objects for complex data relationships.
• Working with libraries of functions: text, numbers, time to enhance data manipulation capabilities.
• Data types and their conversion, considering user regional settings to ensure compatibility.
• Merging and splitting columns and rows to reformat data as needed.

Working with Tables in Power Query M Language for Government

• Joining and merging tables, tracking query dependencies to maintain data coherence.
• Table functions and managing transformation steps to streamline data preparation.
• Removing duplicate values from tables and column sets to ensure data accuracy.
• PIVOT transformations and their inverse: UNPIVOT for flexible data presentation.
• Aggregation and counting of data to derive summary statistics.

M Query Advanced Operations for Government

• Advanced functionalities of the M language to support complex government data requirements.
• Building objects: lists, datasets, tables using code for customized solutions.
• Creating custom functions in the M language to automate repetitive tasks.

Dynamic Queries - Parameters in M Language for Government

• Creating and managing parameters to enhance data flexibility.
• Parameterization of queries to support dynamic reporting needs.
• Handling parameters from the Power BI report level for seamless integration.

Power Query and SQL Server for Government

• SQL Server modes of operation: Data Import vs. Direct Query, including their capabilities and limitations in government applications.
• Importing SQL objects to build a robust data model for government use.
• Tables as the primary source of data for the model.
• Views as stored database queries to provide pre-defined data sets.
• Procedures that return data, offering an overview of their utility in government operations.
• Table functions for advanced data manipulation.
• Queries in SQL code for custom data retrieval and processing.

Selection Queries - DQL (Data Query Language) for Government

• Using the query designer to create a database query for government reports.
• Basic data types in SQL and their application in the Power BI data model for government use.
• Data retrieval using SELECT: command syntax and order of execution of SQL statements to ensure efficient data access.
• Operators and criteria in queries to filter and refine data for government analysis.

Optimizing SQL Queries in Power Query for Government

• Leveraging SQL language functions in queries to enhance performance.
• Operations on joined tables: SQL joins (SQL JOIN) to combine multiple data sources efficiently.
• Combining the results of UNION, UNION ALL, INTERSECT, and EXCEPT/MINUS queries for comprehensive data analysis.
• Aggregation of data on the SQL server side to reduce processing load.
• Window functions in SQL: OVER ordered results, PARTITION BY partitioning, and ORDER BY sorting of query results. Row references: previous, next, first, and last in a group for detailed data insights.
• Subqueries in SQL: using the result of one query as a WHERE condition in another query. Creating queries based on other queries to support complex government reporting needs.
• CTE (Common Table Expressions) table expressions for advanced data manipulation.

Power Query and SQL Advanced Parameterization for Government

• Modifying a query using the M parameter to support dynamic data retrieval.
• Incorporating parameters in remote SQL queries to enhance flexibility.
• Controlling parameter values from within Power BI to streamline report generation.
• Using fragmenters in Power BI Desktop visualization and parameters in Power Query for interactive reporting.

DAX Language in the Power BI Data Model for Government

• An introduction to the DAX language, including its basics and concept as applied to government data management.
• Applications of DAX (Power BI, Power Pivot, Analysis Services) in public sector operations.
• An overview of the environment and tools useful for working with DAX in a government context.

Introduction to the DAX Language for Government

• Data model - understanding its structure and functionality for effective data management.
• Best practices for organizing data to support government reporting requirements.
• Data types, type conversion, and handling of possible errors to ensure data integrity.
• Data type vs. data format: managing and customizing data formats to meet specific government needs.
• Creating relationships between tables to link related data sources.
• Data model relationships: active and inactive to manage complex data interactions.
• Parameter tables for dynamic data inputs.
• Filtering directions to control data flow in reports.
• Hiding columns in user view to maintain a clean and focused report interface.
• Operators in the DAX language to perform calculations and logical operations.

Calculation Columns and Built-in Functions in DAX for Government

• Understanding calculation columns and their role in data analysis.
• Creating and modifying calculation columns to derive new insights from existing data.
• Operators and their priorities in the DAX language to ensure accurate calculations.
• Basic functions of the DAX language: ROUND, IF, SWITCH for common data manipulations.
• Time functions: YEAR, MONTH, DAY, WEEKDAY, WEEKNUM, EOMONTH for temporal data analysis.
• Logical functions: NOT, OR, AND and operators || and && to handle conditional logic.
• Text functions: LEFT, RIGHT, MID, LOWER, UPPER for text manipulation.
• Numeric functions: ROUND, ROUNDUP, ROUNDDOWN for numerical data adjustments.
• Conversion functions: FORMAT to change the display format of data values.