Power BI desktop from A to Z Training Course

1. Building Power BI Desktop Reports for Government
Purpose: Prepare a series of Power BI visualizations based on raw data to support government operations and decision-making.

• Setting up the Power BI environment for government use.
• Create a Power BI report using data from a single spreadsheet, ensuring it aligns with public sector standards.
• Visualization components: Chart, card, table, matrix, indicator, and map to enhance data presentation for government stakeholders.
• Working with visualizations: General rules for linking to data and formatting objects to ensure clarity and accuracy in government reports.
• Filtering a report: Filtering by selection, using the filter panel, and fragmenter to refine data views for specific government needs.
• Data model: Creating a report based on a data model consisting of relationally linked tables from a single spreadsheet. Import, transform, and clean data in Power Query. Automatic and manual creation of relationships between tables. Principles of creating and types of relationships suitable for government datasets.
• Data integration: Building reports based on distributed data sources relevant to government operations. Working with Power Query to integrate various data sources. Overview of popular data sources from which you can retrieve data for Power BI, including the capabilities of the Power Query M language.
• Data sources for Power BI: CSV files, Excel, JSON, XML, PDF. Tables published on the Internet. OData streaming data. Importing files from a folder. SharePoint file libraries. SQL databases, all tailored to government data management needs.
• Working with queries: Joining and merging tables. Tracking query dependencies. Handling duplicate values. PIVOT-type transformations. Grouping, counting, and aggregation of data for comprehensive government reports.
• Dynamic queries: Using variables (parameters) in query optimization. Creation, management, and handling of parameters from the Power BI report level to enhance flexibility in government reporting.

2. Power BI Reports with DAX Elements for Government
Purpose: Build a Power BI report incorporating elements of the DAX language to support advanced data analysis for government operations.

• DAX language and model in Power BI: Understanding its role and applications in government data analysis.
• Components of the DAX data model: Columns, calculated columns, tables, filtered tables, calendar tables, measures, and relationships. Data types and formats relevant to government datasets.
• Best practices for organizing data to ensure efficiency and accuracy in government reports.
• Computed columns: Creation and modification. Operators and their priorities in the DAX language. Hiding columns in the user view to maintain clarity in government presentations.
• DAX language functions: Text, number, time, logical, conditional, convert, array, and filter functions tailored for government data processing.
• Relationships in the data model: Active and inactive relationships and their use. Filtering directions. Joining tables in the absence of relationships to support complex government datasets.
• Computing tables: Filtering a table and its context in a query. Use of FILTER, ALL, and ALLEXCEPT functions for precise data manipulation.
• Measures in DAX: Understanding their purpose and creation. Differentiating measures from computed columns in the context of query execution. Utilizing the CALCULATE function and aggregate, count, and statistical functions in measures to support government analysis.
• Context in the DAX language: Context at the row, query, and filter levels used in government data models.
• Hierarchies in analysis: Automatically generated and manually defined hierarchies to enhance data organization for government reporting.
• Time Intelligence in practice: Calendar tables in DAX. Time intelligence functions in DAX for operations on time to support temporal analysis in government datasets.

3. Report Based on Data Stored on SQL Server for Government
Purpose: Introduce working with SQL database servers to streamline data generation, processing, and importing for government reports.

• SQL Server modes of operation: Data Import vs. Direct Query. Understanding capabilities and limitations relevant to government data management.
• Importing SQL Server objects that can be used in model building: Tables, views, procedures that return data, and table functions to support comprehensive government datasets.
• Working with queries in SQL: Classic data retrieval using SELECT commands. Syntax and execution order of SQL statements. Low-code query development using Query Designer for efficient government data processing. SQL standard in the Power BI data model for government use.
• Optimizing SQL usage: Retrieving only necessary data to improve efficiency. Functions in SQL queries. Operations on joined tables, including SQL joins. Combining query results and aggregating data on the SQL server side to support government reporting needs.
• Parameterization and SQL: Modifying queries with M language parameters. Controlling parameter values from Power BI. Integration of Power BI fragmenter with M language parameters for dynamic data retrieval in government reports.
• Dynamic SQL creation and submission to the server to enhance flexibility in government data processing.

4. Power BI Online for Government
Purpose: Create visualizations that can be shared online to support collaborative government operations.

• Online Report: Publish existing reports from Power BI Desktop. Create new reports from published datasets. Export a report to PDF, Excel, PowerPoint, and embed it in PowerPoint. Share reports with colleagues and publish them publicly. Refresh data by re-publishing to ensure up-to-date information for government stakeholders.
• Organization and user areas: Workspace and dashboard elements. Managing access to the workspace to control data sharing within government departments. Creating and managing a dashboard, including its components, capabilities, and limitations relevant to government operations.
• Data sets and data repositories: Using an existing data set in a new report. Downloading a data source as a PBIX file. Viewing query and object dependencies in Power BI Online to ensure data integrity for government reports.
• Dataverse and Power Query Online: Utilizing Dataverse, a component of the Power Platform, to manage master data for government operations. Creating and managing a data source, including synchronization scheduling and access control to enhance data governance.
• RLS (Row Level Security): Implementing table permission control and connecting it to the model. Defining access rules and adding users to ensure secure data sharing within government agencies.
• Data Gateway: Installation and configuration of Data Gateway. Adding new sources to the gateway. Managing connections and setting data update schedules. Ensuring security and access control for government data.
• Report subscriptions: Creating and managing report subscriptions. Scheduling notifications to keep government stakeholders informed.
• Power BI integration: Creating and publishing applications in Power BI Online. Downloading ready-made applications. Publishing applications to SharePoint, websites, and Microsoft Teams service. Providing dedicated report views for mobile devices. Managing permissions to ensure secure access for government users.

5. Summary: A-Z Project + R and Python Language Scripts for Government
Purpose: Building an analytics system with publishing and sharing capabilities to support comprehensive government reporting.

• Summary exercises to reinforce learning and application of Power BI skills in a government context.

Purpose: (Optional) Visualization and data processing using Python language for government.

• Python language applications: Running scripts directly in Power BI Desktop to import data into the model. Creating and sharing reports in the Power BI service to support government operations.
• Prerequisites: Setting up the Python environment in Power Query. Utilizing software libraries such as Pandas and NumPy for government data processing.
• Working with the Python language: Enabling scripting support. Importing and refreshing data using scripting to ensure timely updates for government reports.
• Creating visualizations: Generating dot plots to examine correlations, line charts with multiple data series, and bar charts for data presentation in a government context.
• Limitations and data security considerations when using Python language in government data processing.

Purpose: (Optional) R Language Visualization and Data Processing for Government

• Requirements and limitations of R language packages. Installing the R language and function libraries to support government data analysis.
• Applications of the R language: Preparing data models, creating reports, cleaning data, advanced data shaping, and analyzing datasets, including filling in missing data, forecasting, and clustering for government use.
• Running R language scripts: Preparing and executing scripts to import and refresh data models for comprehensive government reporting.
• Working with the R language: Utilizing R language in the Power Query editor. Using ready-made visualizations in Power BI. Creating visualizations based on R language script data to enhance government data presentations.