Learn Excel Power Query, Power Pivot, DAX & Power BI

Connect to data sources like Excel workbooks, text files, and websites with Power Query, import and shape data into an Excel table, and build a data model for a dashboard.

Learn how to connect Excel to external data using Power Query by importing a tab-delimited text file, creating a linked dataset, and refreshing pivots and tables.

Transform data with Power Query in Excel by importing from text, removing unwanted columns, filtering and sorting, adding a computed ratio column, splitting city, state, country, and loading to Excel.

Connect to data in another Excel workbook using Power Query, transform data, replace values to NZ, format suffix runs, add an index column, preserve order, and refresh for dynamic updates.

Connect to a website data source in Power Query, transform and remove columns, and create a connection without loading a table, using the same interface as other sources.

Combine two Spotify data tabs into a single table using append queries in Power Query, maintaining identical columns and linking to the original data source.

Merge data in Power Query teaches adding columns from a second table using a left join, expanding merged records to enrich datasets with a new type column.

Pivot and unpivot data with Power Query to transform months from rows to columns and organize sales, expenses, and profit by sales reps.

Explore Power Query math and statistics functions on numbers in connections via the editor. Apply absolute value, sign, rounding, and multiply for a 20 percent commission, then summarize with statistics.

Explore date and time functions in Power Query to derive year, month, days in month, earliest and latest dates, durations, and duplicate columns while handling large datasets.

Create new columns in Power Query using conditional, custom, and example-based columns on sales data, preserving index, and applying pattern-driven calculations.

Explore the power query interface overview, preview data connections, apply and review transformations, and learn when to close and load or load as a connection.

Connect to a folder with the from folder option to automatically combine yearly sales files into a dynamic data source, automatically incorporating new files and loading to the data model.

Create a fully functioning data model in Power Pivot by connecting data from multiple sources, establishing relationships between tables, and laying the groundwork for a dashboard.

Install the Power Pivot add-in via File > Options > Add-ins and enable it; a new Power Pivot tab appears on the Excel ribbon.

Learn database normalization by turning the North Wind orders data into products, suppliers, customers, and employees tables using primary and foreign keys and power query to build a data model.

Bring data into the excel data model to enable faster analysis, loading five tables into memory, then rename them for clarity and apply the available tools.

Load data into the data model using power query or from excel and text files, transform data types and formats, remove rows, and name tables for a unified model.

Learn to create one-to-many relationships in a data model by linking fact and lookup tables in Power Pivot, using diagram view or the design tab to establish and manage relationships.

Learn how to create and use hierarchies in Excel, grouping country, state, and city to tidy data and appear as a single field in pivot tables and charts.

Hide certain fields from client tools to control pivot table access. Harmonize keys by hiding the data table copy and keeping the lookup table key for accurate filtering.

Create a pivot table from the data model by dragging fields from related tables to rows, columns, and values, demonstrating how relationships enable cross-table analysis and downstream filtering.

Explore the Power Pivot interface, including the home tab and data options, and the Power Query editor. Refresh data, create measures in the calculation area, and manage relationships.

Explore DAX, the data analysis expressions language, and learn how to create calculated columns and measures for pivot tables, charts, and dashboards.

Explore DAX to create calculated columns and measures in Power Pivot, using related and index-like references to pull price and other data across tables, with paid status and profit examples.

Create measures using the tax formula language to summarize values across many rows and return a single result, such as a total profit measure for pivot tables.

Explore the difference between implicit and explicit measures in Power BI. Implicit measures are limited to standard aggregations and pivot tables, while explicit measures offer greater flexibility for complex formulas.

Distinguish columns from measures in DAX by using explicit measures in the values area and columns for rows, and learn to create, edit, and delete them via the measures icon.

Discover how related and relatedtable pull columns from related tables using data model relationships, such as price from products, and when to use each function.

Learn how to use text functions in Excel DAX such as LEN, LEFT, RIGHT, SEARCH, and SUBSTITUTE to parse addresses, extract street names, and shorten strings in Power Pivot calculations.

Learn the DAX FORMAT function, mirroring Excel's text function to change a date column's format with format codes, including date order differences between New Zealand and the United States.

Learn to use DAX measures for counting in a data model: count rows, count of shipped orders, and distinct counts of products to analyze filters and totals.

Learn how the DAX calculate function creates a new measure to override filter context, using total sales and a New York baseline for comparison.

Explore how the DAX filter function works with calculate to return repeating values, using a city filter on the customers table for New York and comparing to a baseline.

Explore the DAX all function, its behavior with calculate and filter contexts, returning the grand total sales across all orders and enabling city-level percentage calculations.

Explore creating a total profit measure in one step with DAX sumX, using related to pull product cost from the products table, and avoid a calculated column.

Rank products using the DAX rankx function by applying a total sales measure, remove row context with the all function, and identify top selling products for dashboards.

Learn DAX basic date and time functions in Power BI, including today, date difference, day, month, year, weekday, week number, and end of month, with Excel-like behavior.

Create and manage perspectives to tailor data views for the sales team, marketing, and logistics by selecting relevant tables and columns, then switch back to the default view.

Create a set of favorite products in a pivot table by selecting items and naming the set. Build a pivot table to analyze total sales using the total sales measure.

Evaluate a kpi for total profit against a 25 target with 10 and 20 thresholds, using traffic light indicators in pivot tables to compare against baselines or last year.

Pull data from the data model using cube member and cube value for live updates. Use CubeSat and cube ranking member to rank top products and customers.

Build a functional dashboard by integrating your data model with sources, relationships, and pivot charts, then add interactivity with slicers, formatting, and cube functions.

Turn your Northwind data model into a functional Power BI dashboard by applying Power Query, Power Pivot, and DAX techniques learned in this final section.

Import an existing data model into Power BI, load five tables and KPIs and measures, and switch between report, data, and model views to build your dashboard.

Create a Power BI dashboard by dragging month and total sales to build a line chart, then switch to a column chart and add profit and profit margin.

Learn to build a horizontal bar chart in Power BI dashboard part 2, showing total sales by product category, drilling down with top five category filters, and adding data labels.

Explore visual, page, and all pages filters in Power BI to show top five categories by total sales, using temporary click-through filters and permanent filters with paint for cross-chart updates.

Create a donut chart to display sales by state in Power BI, using total sales by state from the orders and customer tables and applying top five state filters.

Create city-level maps in Power BI by using city as location, sizing bubbles by total sales, and drilling into states; switch map styles and apply filters that update visuals.

Create Power BI dashboards using cards to display total sales and gauges for profit margin, with color and background formatting, and define max, min, and a target value.

Create a treemap and a cluster bar chart to analyze product sales by category, using total sales and product descriptions, add constant, minimum, maximum, and average lines with interactive filters.

Use the matrix to verify model data by breaking total sales into rows and columns, and use Q&A to explore questions like total sales by category or employee.

learn to add and format slicers in a power bi dashboard, sync them across two pages, and filter by salespeople to create interactive, multi-page reports.

Add a Power BI card to show the product count using a measure from the orders table. Adjust the data label and maximum to reflect unique products as slices change.

Discover the get more visuals option, import visuals from templates or a file, and focus on the core visuals you will use 80 percent of the time.

Learn to tidy a Power BI dashboard by adding a title and logo, then align and distribute charts and cards using top, bottom, center, and horizontal alignment tools.

Explore creating and configuring Power BI dashboard buttons for page navigation, including back buttons and a Q&A button, with focus mode, exports, and chart sorting.

Learn to create and use bookmarks in Power BI to save filtered dashboard views for a user, toggle object visibility with the selection pane, and switch between beverages and sources.

Use what-if parameters in the modeling tab to model discounts and uplift with sliders, and create sales after discount to visualize impact on total sales.

Create role-based security in the modeling tab by defining a role and filtering the suppliers table where ship name equals Fisher Products to restrict data.

Explore Power BI section essentials, leveraging help resources, using the view panes for filters, bookmarks, and selection, and adapting the desktop and mobile layouts before publishing the dashboard.