Mastering Power BI: From Data to Dynamic Dashboards

Explore front end and back end in Power BI through a practical ETL workflow, from data extraction in Power Query to data modeling, DAX calculations, and publishing dashboards.

Learn to extract data from CSV and other sources in Power BI, use Power Query Editor for ETL transformation, and save a pbix with the extracted data ready for analysis.

Master the Power Query Editor in Power BI, handling extraction, transformation, and loading in the ETL process, while applying steps with M code and adjusting data types.

Explore basic Power BI table transformations by using the ribbon to shape data, remove unwanted columns and rows, and clean data as you load CSV files into the data model.

Connect to two CSV files in the Power Query editor, rename them as product category lookup and product subcategory lookup, and promote headers before loading to the data model.

Extract two csv files into Power BI, rename tables, promote headers, and create SKU type by extracting text before the second dash, then load into the data model.

Explore Power BI storage and connection modes, including import, direct query, composite model, and live connection, with CSV and database examples guiding analysts.

Learn to connect Power BI to a MySQL database using MySQL Workbench, create databases and tables, import CSV files, and load data into Power BI.

Learn to gather data from websites in Power BI using web scraping, the web option, and CSV imports, with dynamic data updates via Power Query and refresh.

Discover automatic data assessment with Power BI’s data profiling and QA tools, including column quality, distribution, and profile. Compare with pandas’ info and describe to gauge data quality.

Master Power BI text tools to transform textual data, including format, trim, split, extract, and merge columns to create a new full name with appropriate capitalization.

We tackle the new assignment focused on text tools within the Power BI course, exploring how to apply text tools to dashboards.

Duplicate the email address column in Power Query Editor, extract the domain name between the at and dot delimiters, capitalize domains, and apply changes to complete the assignment.

Use numeric tools in the transform tab to apply statistics tools and scalar functions on product price, then create a discount product price column and update the data model.

Complete the third assignment on numeric tools for Power BI, as introduced in Mastering Power BI: from data to dynamic dashboards.

Explore how to use Power Query for numeric tools in Power BI, calculating average product cost, counting colors, distinct customers, and maximum annual income, then restore the original table.

Explore date and time tools in Power BI to transform date columns, extract year, month, start of month or week, and create calendar lookups using Power Query editor.

Learn to align date formats in Power BI by using change type with locale to match your CSV file’s region, ensuring correct date conversion and error prevention.

Learn to build a rolling calendar in Power BI using a start date to generate dates up to the current date with M code in a blank query.

Begin the new assignment on calendar tables in Power BI, focusing on calendar tables as a key component of the task. Clarify how calendar tables support the course goals.

Master calendar tables by applying date and time tools in Power Query to extract month name, month number, start of year, and year from a date column.

Create an index column in Power BI to uniquely identify rows, and use conditional columns to label order quantity as single item or multiple items.

Learn how to group by categorical columns and apply aggregate functions—sum, count, min, max, and median—using basic and advanced group by on real sales data.

Learn to use pivoting and unpivoting to remedy messy data in Power BI, transforming columns into rows and vice versa, with practical CSV examples and a transpose alternative.

Learn how Power BI merges queries by joining two tables on a common key to create a wide table, and compare merge versus append while avoiding duplicates.

Master the append queries option to stack tables with similar structure and data types in Power BI, creating a long appended table and learning when to append versus merge.

Gather data from a folder in power bi to automatically append multiple csv files into a single, well-structured dataset, ensuring consistent columns and seamless refresh.

Explore how Power BI data source settings manage file paths and file names, handle data source errors, and update sources with change source and refresh preview when paths change.

Use the refresh feature to keep dynamic data up to date from web sources, and selectively include or exclude static data in the report refresh.

Learn how to import Excel models into Power BI, preserving prebuilt data models, relationships, and transformations from Excel using Power Query, Power Pivot, or Power View for seamless visualization.

Create a data model by linking multiple tables and establishing relationships. Learn data cleaning with the Power Query editor and explore normalization, primary and foreign keys, cardinality, and hierarchies.

Build a Power BI data model by linking tables via primary and foreign keys, establishing relationships, and understanding cardinality and filter flow for accurate order quantity visuals.

Learn how normalization reduces data redundancy and preserves integrity, clarifies OLTP vs OLAP, and shows when to normalize for easier insertion and deletion in databases and data warehouses.

Explore how to identify dimension and fact tables, classify columns as categorical or numerical, and build relationships to support grouping, filtering, and sales analytics in a Power BI data model.

Explore primary keys and foreign keys to relate fact and dimension tables, build normalized data models, and create clear relationships in Power BI data modeling.

Explore when to use merge tables versus relationships in Power BI, emphasizing normalization to separate fact and dimension tables and reduce redundancy and memory use through a relational data model.

Learn how to build relationships in Power BI by linking primary and foreign keys across dimensional and fact tables, using one-to-many cardinality, active relationships, and drag-and-drop in the report view.

Learn to manage and edit Power BI relationships by linking sales data with the calendar lookup via primary and foreign keys, use manage relationship to set active or inactive relationships.

Explore how star and snowflake schemas organize data models in power bi, linking a central fact table to dimension tables and illustrating dimension-to-dimension relationships.

Delete existing table relationships, build a star schema linking sales to calendar, customer, product, and territory, then extend to a snowflake schema and verify with a matrix.

Delete table relationships, build a star schema linking sales, calendar, customer, product, and territories to the fact table, extend to a snowflake schema with a matrix visual for order quantity.

Explore active and inactive relationships in Power BI, shown as solid and dotted lines. Relate calendar lookup dates to order dates, and keep stock dates inactive for easy future activation.

Learn about cardinality in data modeling for Power BI, covering one-to-one, one-to-many, many-to-one, and many-to-many relationships, and how to use keys and junction tables to simplify designs.

Relate multiple fact tables to dimension tables in Power BI to filter return quantities, showing when to connect tables and when skipping direct fact-to-fact joins.

Master how filter context and filter flow govern data filtering across tables. See filters travel from dimension to fact tables via filter direction and one-to-many relationships.

Explore bidirectional filtering and ambiguity in Power BI, mastering cross filter directions between dimension and fact tables and the impact of one-to-many cardinality on data filters.

Hide foreign keys in report view to ensure filters apply to dimension keys, guiding non-tech users to apply accurate filters while preserving fact-table relationships in Power BI.

Explore the Power BI filter flow by tracing product and sales table relationships to reveal a 338 sale value, and learn to hide unused foreign keys.

Demonstrate Power BI filtering by unhiding and hiding columns, explaining why product key 338 has no returns using the returns data and product lookup table.

Learn how model layouts let you focus on a portion of the model with multiple fact tables, preserve relationships, and reveal a star schema with sales data and returns data.

Learn how data format and data category control front-end presentation in Power BI, separating data type storage from display with short date formats and location data for maps.

Master hierarchy creation in Power BI by turning related columns into a tree-like territory hierarchy, enabling drill-down from continents to countries to regions in a matrix visual.

Create a date hierarchy with start of year, start of month, start of week, date, then add it to matrix visual to drill up and down across granularity levels.

Create a date hierarchy in Power BI using start of year, start of month, start of week, and date to enable drill-down in a matrix with a tree view.

Explore DAX, the data analysis expression formula language for Power BI, learn to create measures, columns, and tables using functions, aggregations, and group by concepts.

Compare Dax and M code in Power BI, exploring front-end Dax for calculations and back-end M code for ETL, header promotion, data types, and the new Dax query view.

Create a calculated column named quantity type from order quantity using a conditional DAX formula, illustrating row context and why aggregate functions don't apply to calculated columns.

Master DAX measures in Power BI to create aggregated values using filter contexts. Learn how measures differ from calculated columns and apply summations across groups.

Explore the difference between implicit and explicit measures in Power BI, showing how built-in aggregates compare to DAX-based new measures for data visualizations.

Discover how quick measures in Power BI create key calculations via DAX or predefined templates, using Copilot AI and natural language prompts to generate aggregates and insights.

Explore how to create a measure table in Power BI using DAX or the enter data option. Move measures into the new table and organize with display folders.

Define filter context in Power BI using DAX, showing how measures recalculate with filters on country, continent, and year, and how sums respond to conditions that filter rows.

Explore DAX syntax and operators, name measures, and reference tables and columns with brackets and quotes. Learn about aggregate functions, calculated measures vs calculated columns, and grouping by region.

Explore the seven DAX function categories in Power BI—math and stats, text, date/time, logical, table, filter, and relationship—and how each enables specific data operations.

Explore basic maths and stat functions in DAX, including sum, average, max, min, and divide, with syntax and practical demos on total returns and alternate results for divide-by-zero.

Explore counting functions in Power BI, including count, count a, distinct count, and count rows, and learn how they treat blanks, booleans, and table versus column context.

Create a total customers measure to count distinct customers who transacted in Power BI, and define return rate as total quantity divided by total quantity sold using the divide function.

Create a total customers measure using distinct count of customer keys from the sales data, then compute a return rate as returns divided by orders, formatted as percentage by category.

Explore conditional and logical functions in Power BI, including if, if error, switch, and, or, with a practical customer lookup example showing whether a customer is a parent.

Discover how the switch function in Power BI maps month names to IDs in a calculated column, replacing long if-else chains and handling else values.

Create calculated columns in the customer lookup table to classify priority and income level with and conditions and if statements; apply a switch function to map education categories.

Build calculated columns in the customer lookup table to set customer priority, income level, and education category. Use if, double ampersand, and switch expressions to implement the logic.

Master Power BI text functions by learning length, concatenate and ampersand, upper and lower case, left, right, and mid, substitute, and search to transform and extract data from strings.

Apply text functions in Power BI to extract and uppercase the month abbreviation and derive the SKU category from product SKUs using DAX, with two objectives and upcoming solutions.

Explore Power BI text functions by building a solution that extracts and capitalizes month abbreviations and derives SKU category from product SKUs using left and search.

Master date and time functions in Power BI using DAX, including today, now, day, month, year, hour, and weekday, with end of month and date diff examples.

Create a birth year column in the customer lookup table by extracting the year from the birth date using the year function in Power BI.

Extract birth year in Power BI by adding a new birth year column in the customer lookup table using the year function on the birth date, with practical examples.

Use the related function to pull a dimension table column into the fact table for revenue. Multiply order quantity by product price to compute revenue, noting normalization trade-offs.

Explore the calculate function as a container that extends and overrides filters by applying expressions with square brackets, enabling complex insights like bulk orders and weekend orders.

Learn how DAX measure totals and the calculate function reveal distinct orders, not the sum of filtered counts, and why overlapping orders affect total values.

Master calculate function in Power BI to create bike returns and bike sales measures, display them in a start-of-month matrix, and compute bike return rate as a percentage for insights.

Learn to apply the Power BI calculate function to build bike returns and bike sales measures, analyze return rates, and assess impact on monthly bike sales using a calendar matrix.

Learn how the all function in Power BI DAX acts as a filter remover, returning all rows and ignoring filters to reveal true totals and percentages.

Create measures using the calculate and all functions to compute total returns without filter context, then compute percentage of all returns, and build a matrix by product categories.

Create an all returns measure with calculate to count total returns without filters, then display a matrix showing percentage of all returns by category, with accessories highest and clothing lowest.

Learn how the filter function row-by-row filters a table to records that meet a condition, compare it with calculate, and apply it to measures in Power BI.

Learn how iterator functions in Power BI loop through each row, applying an expression and aggregating with sumx across related tables to compute total revenue and average revenue per customer.

Create measures for total cost and total profit using iterator functions, multiplying order quantity by product cost and summing, then revenue minus cost, and display yearly profit in a matrix.

Create a total cost measure with sumx of order quantity and product cost using related, then compute total revenue, total cost, and total profit, displayed by year in a matrix.

Explore time intelligence patterns in Power BI by using DAX functions like year to date, previous period, and running total to analyze revenue and performance over time.

Create time intelligence measures for previous month returns, orders, and profit using date add, and set 10% targets; compute a 90-day rolling profit.

Explore time intelligence in Power BI to compute previous month returns, orders, and profit with DAX, then apply 90 days rolling profit and 10% targets for dynamic dashboards.

Learn to transform data into dynamic dashboards in Power BI, from data gathering and ETL to modeling and communicating insights (revenue, profit, orders) to analysts, managers, and executives using visuals.

Master the Power BI dashboard sketch by outlining KPI cards for revenue, orders, return rate, and profit, and designing dashboards—regional, product categories, time-series, and customer base—for executives and managers.

Explore adding report pages and objects in Power BI by using the insert ribbon to build visuals, KPI cards, slicers, tables, and charts, and arrange a clear dashboard layout.

Group and rename KPI cards in Power BI, set the sidebar width, and create an executive dashboard with four pages—exec dash, geospatial map, product, and customer details.

Learn to build KPI card visuals in Power BI to display revenue, orders, profit, and return percentage, with formatting, color, and grouping for a cohesive executive dashboard.

Create a card on the customer detail report showing unique customers. Rename the field as revenue per customer and copy formatting from the executive dashboard for average revenue per customer.

Explore creating and formatting Power BI card visuals to display total customers and unique customers, and average revenue per customer, with copy-paste formatting, KPI grouping, and alignment in dashboards.

Explore line charts to visualize revenue trends by start of month, comparing year over year. Learn to customize the chart with on object formatting and tooltips for clear stakeholder insights.

Explore how Power BI uses trend lines and forecasting to project revenue over future months, adjust forecast units, and interpret confidence intervals for practical business insights.

Create a line chart in the customer detail report showing total customers by week, copy the executive dashboard's formatting, and add a trend line, x-axis zoom, and tooltips.

Add a line chart to the customer detail report showing total customers by week, adjust the x and y axes, and enable tooltips, a trend line, and a zoom slider.

Explore KPI visuals in a Power BI dashboard to compare monthly revenue, orders, and returns against the prior month using trend axes, targets, and color cues.

Learn to break down revenue and orders by product category using donut and bar charts, compare categories, and refine legends, tooltips, and formatting for clear category insights.

Learn to apply visual, page, and report level filters in Power BI, including interactive cross-filtering, numeric and text filters, top N, and how to clear and revert changes.

In power BI, build a donut chart in a customer detail report to show customers by income level, with income level as legend, data labels, and filters for top occupations.

Demonstrates creating a donut chart in Power BI to show total orders by income level and occupation, with data labels, a chart title, and top-n filters.

Explore how Power BI table and matrix visuals reveal top ten products and categories by revenue, orders, and profit, with month-by-month insights for executives.

Apply conditional formatting in a Power BI matrix with data bars and background color to highlight orders and profit, and use top ten product categories filtering to inform executives.

Apply top N filtering in Power BI to show the top ten product categories by total orders, using a visual filter on a matrix for concise executive dashboard results.

Add a table to the customer detail report showing name, total orders, and revenue; apply light gray data bars for orders and a white-blue color scale for revenue.

Join the solution walkthrough for assignment 4, building a top 100 customers table showing key, full name, orders, and revenue, with conditional formatting and a chart title.

Create top N text cards and KPI visuals on an executive dashboard, apply subcategory filters, and display the most ordered product type and the most returned product type.

Explore Power BI top end text cards in the customer detail report, filter top customers by total revenue, and replicate cards for total orders and revenue with formatted titles.

Demonstrates creating top N text cards in a customer detail report with a card visual showing the full name. Applies top by revenue, primary key and Power Query data cleaning.

Explore geospatial analysis in Power BI by building a map visual that shows total orders by country using bubble size, with styling and tooltip customization.

Learn how slicers in Power BI filter all visuals on a report page, adapting to textual, numeric, and date data with tile styling and continent examples.

Explore how to add a year slicer to filter the customer detail report visuals, exclude blanks with a visual level filter, and enable single or multiple year filtering.

Add a year slicer to the Power BI dashboard to filter the customer detail report; see updates across visuals with multi-select using ctrl+click or select all.

Learn how to use the has one value function in DAX to identify unique top customers by max orders or revenue, showing dash values for ties in Power BI dashboards.

Create gauge charts in Power BI to compare monthly orders, revenue, and profit against targets within a product report, using slicers and current-month filters.

Apply conditional formatting to show negative values via reddish color and positive values via dark gray for order target gap, revenue target gap, and profit target gap in dynamic dashboards.

Discover how area charts extend line visuals by shading beneath the line to show monthly profit and returns, with stacked options, axis labeling, and formatting in Power BI.

Master drill up and drill down tools to decompose revenue across year, month, week, and day hierarchies in Power BI. Build date and geographic hierarchies in charts for deeper insights.

Implement drill up and drill down in line chart, updating the x axis with date hierarchy or year-quarter-month-date, and use the chart header to explore trends and forecasting at granularities.

Master the drill up and drill down features in Power BI's customer detail report line chart by using date hierarchy to analyze customers by year, quarter, month, week, and day.

Explore drill through filters to navigate across pages by product name and see prefiltered visuals update on other report pages.

Explore report interactions in Power BI, learning how to control cross filter, cross highlight, and edit interactions between visuals like matrix, card, and KPI visuals.

Master the seventh Power BI assignment by configuring report interactions. Apply cross-filter to filter the line chart, not the donut, while the table remains unaffected; the slicer filters all visuals.

Learn to configure report interactions in Power BI by applying filters instead of highlighting, control cross-visual filtering with a slicer, and ensure visuals react as intended across a dashboard.

Create bookmarks and attach them to a reset button to clear all filters and restore the original dashboard state. Configure tooltips and button states for clear user guidance.

Explore how to create and use bookmarks in a Power BI executive dashboard to capture customer insights, manage filters, and reset visuals for stateful, dynamic insights.

Design custom navigation buttons in a Power BI dashboard. Build a sidebar with links to multiple pages and group icons with default, hover, and press states.

Learn to build a slicer panel in Power BI by tying a sidebar button to multiple slicers, using bookmarks to show and hide year and continent filters without altering data.

Explore numeric and field parameters in Power BI, using slicers to dynamically adjust metrics like orders, revenue, profit, and returns on interactive visuals.

Add a field parameter named customer metric with total customers and average revenue per customer in Power BI, and configure it as a horizontal tile slicer on the customer report.

Demonstrates creating a field parameter named customer metric solution with average revenue per customer and total customers, applying a tile slicer, and updating the line chart to reflect the metric.

Create a custom tooltip in Power BI using a report page and attach it to visuals like orders by category, showing revenue, profit, orders, returns, and a month-by-month area chart.

Sign in to the Power BI service using your organization or college credentials, then leverage Copilot AI insights and publish dashboards with extensions.

Learn how to import custom visuals into Power BI, including AppSource visuals. Explore licensing notes for Pro and Premium tiers and the role of paginated reports.

Master Power BI mobile layout to design a phone-friendly version of your executive dashboard, arranging visuals into a mobile view and publishing it for organization-wide access.

Publish your Power BI dashboard to the service, share it via workspaces with organization ID, and access web version. Export to PowerPoint, PDF, or Excel and deliver stakeholder insights.

Explore anomaly detection in Power BI, using AI-driven insights to flag and explain time-series outliers, adjust sensitivity, and ensure data quality with at least four data points.

Generate auto summaries and deep insights for Power BI dashboards using the smart narrative tool, with dynamic, explainable insights across visuals and drill-through interactions.

Learn how the Power BI Q&A visual turns user questions into answers and visuals by teaching synonyms, reviewing questions, and enabling dynamic charts.

Explore the decomposition tree in Power BI to break down numbers. Learn manual and AI-driven breakdowns across categories, subcategories, and product names.

Power BI's key influencer visual shows how factors like annual income, education level, marital status, parent status, and occupation drive the likelihood of becoming a home owner.

Pause visuals and refresh visuals optimize your Power BI dashboards via the optimize ribbon, controlling visual queries with optimization presets, performance analyzer, and apply all slicers.

Explore Power BI optimization presets that control interactivity, including query reduction, interactivity, and customize options, to manage cross filtering, cross highlighting, and real-time slicer selections.

Discover how to use an apply all slicers button in Power BI to apply or clear filters across multiple slicers on a report page and across dashboards.

Use the performance analyzer to record actions and track load times for each visual, analyzing dax query time, visual display, and other time to optimize Power BI dashboards.

Power BI: explore the latest features and updates from June 2023 to February 2024, including calculation groups, card visual, Dax query view, button slicer, and visual calculations.