Excel Power Query, Power Pivot, DAX, Power BI & Power 3D Map

Learn to transform and automate data in Excel with Power Query, import csv files, pivot and unpivot data, clean and consolidate datasets, and refresh queries.

Watch a practical Power Query example that imports, cleans, splits IDs from last names, computes an 80% exam average, and merges data to show who passed.

Import csv files, use the power query editor to clean and delete unwanted data, load it into a worksheet, apply filters, and learn to unpivot and pivot data.

Define what a csv file is and how comma separated values work, with headers and delimiters. Show how Power Query transforms csv data into an analysis-friendly layout in Excel.

Normalize data by ensuring every attribute has its own column, enabling robust storage and analysis, reducing storage, easing maintenance, and speeding queries.

Import a csv file using power query, remove header rows, and transform data so dates appear in one column, creating a clean sales list with step-by-step data shaping.

Transform the csv by removing header rows, setting the first row as headers, removing unwanted columns, unpivoting date columns, and setting data types for store, category, sales, and date.

Rename the query to csv final and load the data into an Excel table from Power Query, placing it in the worksheet at cell A1, and refresh to update data.

Learn to shape a data table in Power Query Editor by renaming, reordering, deleting columns, and merging two columns to create a single Make and Model field.

Discover row-based data shaping in Power Query, using keep and remove techniques, sampling methods, and handling duplicates. Learn to sort, group, and reverse rows to tailor data for analysis.

Set the correct data types early to ensure accurate calculations and slicing, using Power Query's auto detect where possible and manually adjusting with the data type tool when needed.

Replace values across a column to standardize data and correct entries in Power Query, using the replace values dialog and advanced options, with text conversion for non-text columns as needed.

Explore how Power Query transforms column contents with text formatting, numeric rounding, and date-time extractions. Learn practical steps to uppercase text, trim spaces, round numbers, and extract year from dates.

Learn how to filter data in Power Query's Get and Transform editor, using text, number, and date/time filters to load only relevant records, including a less than €5,000 example.

Fill blank cells in Power Query using fill down from the first non-empty above, and use the first row as headers to promote proper column names.

Explore data mashup with Power Query to extend datasets via derived columns, joins, and pivoting, delivering a standardized, usable data model. Manage queries with the view and advanced editor ribbons.

Discover how to split columns in Power Query by delimiter or fixed characters, including custom delimiters, to separate dates, numbers, or text elements.

Create custom or calculated columns in Power Query, such as a vehicle column from make and model and a gross margin column from sale price and cost price.

Add an index column to number records, starting at zero or a custom start, and use it as a surrogate key for dimensional modeling and to reapply sort orders.

Merge data from multiple queries by joining on a shared field to create a single table. Look up data and aggregate invoice lines into invoices to enrich the dataset.

Power Query appends two structurally identical data sources into one, matching columns, order, data types, and names, and supports loading multiple text files from a folder.

Pivot and unpivot data inside Power Query to cleanse and transform datasets, transforming rows and columns for efficient data loading and reporting, including append and flatten operations.

Learn to handle multiple date formats and invalid dates with Power Query, set the English United States locale, and transform data to correct date time values.

Learn how to use column from examples in Power Query to create new columns from sample values, reveal transformations, and bucket numeric sizes into XL, L, M, S for analytics.

Unify data from multiple sources into tables, join queries, and build an ETL workflow in Excel using Power Query, Power Pivot, DAX, Power BI, and Power 3D Map.

Explore Power Query to pull, transform, and refresh data from Excel workbooks, external files, databases, and folders; learn appending, merging, unpivoting, pivoting, and basic M language to streamline your analysis.

Learn to use Power Query to split a column by a delimiter, transform data from a workbook, rename steps, and load results to a worksheet with refresh tips.

Use power query to get data from a database, transform and merge columns to create a staff name, and refresh to keep an up-to-date staff list loaded into excel.

Combine data from three or more sources using Power Query to append tables from PDFs or workbooks, fix mismatched headers with M code, and load the unified report.

Use a merge query in Power Query to join tables, selecting a left outer join by default to pull instructor names into the schedule and avoid lookup inefficiencies.

Apply a range of transformations in power query to clean and shape HR course evaluation data, including removing rows, promoting headers, filtering nulls, and deduplicating.

Discover how to unpivot data in Power Query, troubleshoot common query errors, and transform pivoted data back to a flattened form using unpivot other columns and M code edits.

Learn to use Power Query in Excel and Power BI to split text into columns and extract category, size, and color from dash-separated product numbers in the Adventureworks catalog.

Learn to import three tables from the Adventure cycles workbook, promote headers, split the product number by delimiter, and rename columns in Power Query.

Learn to merge category and color lookups into the product query using Power Query Editor, performing left outer joins and expanding results for a data model and pivot table.

Load categories, colors, and products as separate tables into the data model, establish one-to-many relationships, and hide key codes to streamline pivot tables and visuals.

Transform a messy invoice dataset with Power Query by splitting a single expenses cell into rows, then group by invoice to compute total expenses and refresh results.

Automate a monthly revenue report by consolidating multiple workbooks with Power Query, transforming data, converting dates to end-of-month, and pivoting to show revenue by product by month.

Fuse power query and power pivot to stage data from diverse sources into a dynamic data model. Create relationships and KPI calculations for code-free analysis in Excel.

Explore the Excel data model, build relationships between customers and orders, and analyze revenue by state with pivot tables using Power Pivot and Dax in Excel.

Load data into the Power Pivot data model using Power Query or direct import, connect to databases, create a calendar table, and build relationships for analysis.

Power Pivot's read-only data model keeps data trustworthy. Rename tables and columns before building formulas to avoid manual fixes, while loading data from sources such as csv, databases, and sharepoint.

Switch from data view to diagram view in Power Pivot to see the entire data set and create one-to-many relationships between lookup tables and data tables.

Create a car details hierarchy in Power Pivot to guide analysis from make to model, enabling drill up and down in pivot tables and preparing for DAX analysis.

Explore data modeling and normalization to structure data for reporting, highlighting fact and dimension tables, data integrity, and faster queries through table relationships.

Explore how database normalization separates facts from attributes using a sales fact table and product and data dimension tables to ensure integrity, and relate to Power Query and DAX workflows.

Learn how DAX powers Power BI and Power Pivot with calculated columns and measures. Discover row context versus filter context and DAX differs from Excel formulas in building dynamic visuals.

Learn dax syntax and data types, compare dax expressions to excel formulas, start expressions with an equal sign, and master arithmetic, comparison, text concatenation, and logical operators with implicit casting.

Learn to standardize and cleanse text in Power Query and DAX, including removing extra spaces and using the left function to create a three-letter town abbreviation.

Create a calculated column direct costs in the stock table by adding purchase price and related costs, including parts and labor, with Excel-like arithmetic applied to every row.

Create cross-table calculations in Power Pivot by building a calculated column that computes gross margin from invoice lines and direct costs across related tables.

Learn to use DAX in Power Pivot to compute aggregates and compare net profit to the average. Format currency and apply conversions for clear BI visuals.

Learn to use the if function in DAX and Excel to flag excessive parts cost above 2000, and to categorize mileage as low, medium, or high for dashboards.

Leverage DAX to build complex if statements using and/or across related tables, as shown by flagging red or blue coupes and creating a special sales column.

Create a basic pivot table from the Power Pivot data in Excel. Drag the car details hierarchy’s make to rows and sale price to values, linking to the data model.

Create and link a slicer to a pivot table in PowerPivot, filter by country, test the slicer, link it to pivot tables, and save the workbook.

Rename and delete tables and columns in Power Pivot, adjust formatting and data types, set default aggregations, and categorize data for maps and dashboards to prepare analytics.