惯性聚合 高效追踪和阅读你感兴趣的博客、新闻、科技资讯
阅读原文 在惯性聚合中打开

推荐订阅源

Simon Willison's Weblog
Simon Willison's Weblog
T
Troy Hunt's Blog
L
Lohrmann on Cybersecurity
S
Schneier on Security
Spread Privacy
Spread Privacy
WordPress大学
WordPress大学
阮一峰的网络日志
阮一峰的网络日志
freeCodeCamp Programming Tutorials: Python, JavaScript, Git & More
让小产品的独立变现更简单 - ezindie.com
让小产品的独立变现更简单 - ezindie.com
G
GRAHAM CLULEY
博客园 - 【当耐特】
有赞技术团队
有赞技术团队
SecWiki News
SecWiki News
博客园 - 叶小钗
博客园 - Franky
V
Vulnerabilities – Threatpost
奇客Solidot–传递最新科技情报
奇客Solidot–传递最新科技情报
O
OpenAI News
小众软件
小众软件
V
V2EX
N
News and Events Feed by Topic
T
The Exploit Database - CXSecurity.com
博客园 - 三生石上(FineUI控件)
The Hacker News
The Hacker News
Project Zero
Project Zero
The Last Watchdog
The Last Watchdog
雷峰网
雷峰网
Google Online Security Blog
Google Online Security Blog
T
Tailwind CSS Blog
C
Cyber Attacks, Cyber Crime and Cyber Security
Exploit-DB.com RSS Feed
Exploit-DB.com RSS Feed
量子位
D
Docker
Recent Announcements
Recent Announcements
T
Threat Research - Cisco Blogs
P
Privacy International News Feed
爱范儿
爱范儿
PCI Perspectives
PCI Perspectives
Jina AI
Jina AI
博客园 - 司徒正美
云风的 BLOG
云风的 BLOG
大猫的无限游戏
大猫的无限游戏
V2EX - 技术
V2EX - 技术
H
Hackread – Cybersecurity News, Data Breaches, AI and More
The Register - Security
The Register - Security
T
The Blog of Author Tim Ferriss
博客园 - 聂微东
Cloudbric
Cloudbric
S
Security Affairs
F
Fortinet All Blogs

DEV Community

Authentication Security Deep Dive: From Brute Force to Salted Hashing (With Java Examples) Why AI Systems Don’t Fail — They Drift Spilling beans for how i learn for exam😁"Reinforcement Learning Cheat Sheet" I Replaced Chrome with Safari for AI Browser Automation. Here's What Broke (and What Finally Worked) How Python Borrows Other People's Work The $40 Architecture: Processing 1 Billion API Requests with 99.99% Uptime Vibe Coding: A Workflow Guide (From Zero to SaaS) Most webhook security guides protect the wrong side. The scary part is delivery. Headless CMS for TanStack Start: Build a Blog with Cosmic EU Age Verification App "Hacked in 2 Minutes" — What Actually Happened Comfy Cloud’s delete function does not actually remove files Running AI Models on GPU Cloud Servers: A Beginner Guide Event-driven media intelligence with AWS Step Functions and Bedrock I scored 500 AI prompts across 8 quality dimensions — here's what broke How to Call Google Gemini API from Next.js (Free Tier, No Backend Needed) The Portal Protocol: Reclaiming Human Connection in the Age of AI How to Fix Your Team's Scattered Knowledge Problem With a Self-Hosted Forum Intro to tc Cloud Functors: A Graph-First Mental Model for the Modern Cloud Designing Multi-Tenant Backends With Both Ownership and Team Access I Built a Neumorphic CSS Library with 77+ Components — Here's What I Learned PostgreSQL Performance Optimization: Why Connection Pooling Is Critical at Scale Cómo construí un SaaS multi-rubro para gestionar expensas en Argentina con FastAPI + Vue 3 🚀 I Built an Ethical Hacking Scanner Tool – Open Source Project I Replaced /usage and /context in Claude Code With a Single Statusline A Pythonic Way to Handle Emails (IMAP/SMTP) with Auto-Discovery and AI-Ready Design I Collected 8.9 Million Polymarket Price Points — Here's What I Found About How Markets Really Move EcoTrack AI — Carbon Footprint Tracker & Dashboard Everyone's Using AI. No One Agrees How. 5 self-hosted ebook managers worth trying in 2026 Building Your First AI Agent with LangChain: From Chatbot to Autonomous Assistant Common SOC 2 Failures (Real World) Stop Vibe-Checking Your AI App: A Practical Guide to Evals How to Use SonarQube and SonarScanner Locally to Level Up Your Code Quality Your Next To-Do App Is Dead — I Replaced Mine with an OpenClaw AI Sign a Nostr event in 60 lines of Python using coincurve — no nostr-sdk, no nbxplorer, no rust toolchain ITGC Audit Explained Like You’re in Big 4 Patch Tuesday abril 2026: Microsoft parcha 163 vulnerabilidades y un zero-day en SharePoint Stop scraping everything: a better way to track competitor price changes Listing on MCPize + the Official MCP Registry while routing payments OUTSIDE the marketplace — how I kept 100% of my x402 revenue Building an AI-Powered Risk Intelligence System Using Serverless Architecture Why We Ripped Function Overloading Out of Our AI Toolchain Testing AI-Generated Code: How to Actually Know If It Works SaaS Churn Is Killing Your Business. Here Is What to Do About It (Without a Support Team) The Speed of AI Is No Longer Linear - And Self-Improving Models Are Why How to Implement RBAC for MCP Tools: A Practical Guide for Engineering Teams From Standard Quote to Persuasive Proposal: AI Automation for Arborists I built a CLI that scaffolds complete multi-tenant SaaS apps Axios CVE-2025–62718: The Silent SSRF Bug That Could Be Hiding in Your Node.js App Right Now The dashboard that ended our friendship Data Pipelines Explained Simply (and How to Build Them with Python) The Hidden Cost of AI Systems Nobody Talks About. undefined vs undeclared, and how typeof behaves Switching from file-based jobs to NATS/Kafka in Rust without changing code io_uring Adventures: Rust Servers That Love Syscalls Why Agentic AI is Killing the Traditional Database The POUR principles of web accessibility for developers and designers Quantum Neural Network 3D — A Deep Dive into Interactive WebGL Visualization How To Install Caveman In Codex On macOS And Windows Automation Pipeline Reliability: Why Your Workflow Breaks When Nobody Is Watching I Built an 'Open World' AI Coding Agent — It Works From ANY Folder From Freelancing to Product: A Tech Service Company's SaaS Transformation China's AI Giants: Adding Tencent Hunyuan & ByteDance Doubao to AI University (74 Providers) On the Vibe Coders and Their Lies clerk: Auto-Summarize Your Claude Code Sessions AI Weekly — 2026/04/10–04/17 | The Model Lockdown Is Here, but the Toolchain Is the Real Battleground AI 週報 — 2026/04/10–2026/04/17 模型封鎖潮來了,但工具鏈才是真戰場 Maybe this is how Open-Source apps are born... 🚀 Fine-Tune LLMs with LoRA and QLoRA: 2026 Guide tRPC v11 + Next.js App Router: End-to-End Type Safety Without the Boilerplate ShadCN UI in 2026: Why I Stopped Installing Component Libraries and Started Owning My Components SaaS Billing in React Server Components: Stripe + Supabase Without a Single `useEffect` Join our DEV Weekend Challenge — $1,000 in Prizes Across TEN winners! Submissions Due April 20 at 6:59 AM UTC. Implementing FSRS Spaced Repetition in Flutter + Supabase — Adding Memory Science to an AI Learning App "I Texted My Localhost From the Train — Claude Code Fixed the Bug Before I Got Home" I Built a Sales Prep AI and It Went Deeper Than Expected Design to Code #2: One JSON, Eleven Outputs Solving the 100M-Row Problem: A Summary Table Pattern for High-Volume Push Notification Logs Flutter Web With Wasm: What Actually Changes For Developers I Built 50 Royalty-Free Soundtracks for My Side Project in a Weekend Using AI Music Generation The Vibe Coding Security Checklist: 7 Things to Check Before You Ship Stop Letting Googlebot Guess Fix Your React App's SEO Right Desconstruindo o Streaming do LinkedIn: Como Criar um Engine de Extração de Vídeo de Alta Performance com HLS e FFmpeg (EDA Part-1) EDA (Exploratory Data Analysis) Explained With Real Life — Why Looking at Your Data Is the Most Important Step in Machine Learning Brand Relationship Management at Scale: Our 4-Touch Outreach System for 200+ Brands Why String.fromEnvironment() Might Return an Empty String in Dart JGuardrails 1.0.0 — Hardening Java LLM Apps Against Jailbreaks, Toxicity, and Prompt Injection Plan and Schedule a Full Week of Threads Content From One Claude Conversation Coding Cat Oran Ep3, Five Tables Changed Everything Updated: BFF Pattern I'm done watching freelancers get buried by 200 proposals. So I'm building the alternative. This is my first post BFS Algorithm in Java Step by Step Tutorial with Examples Tracking LLM Pricing Monthly: An Open Dataset for 22 AI Models How We Measure Content ROI on a Comparison Site: Revenue Attribution Without Perfect Data Introducing Nova AI Ops: The AI-Native Operating System for SRE Teams I built a free desktop video downloader for Windows — Grabbit How Talkie OCR Helps Vision-Impaired & Dyslexic Users Read the World Around Them VRCFaceTracking安装和iPhone面捕配置教程,有bug Even CrowdStrike Can't See Your Agents The Automation Gold Rush: What n8n Workflows and Claude Are Opening Up for Developers Right Now
Mastering Power Query in Power BI: A Complete Data Transformation Guide
Bamgboye Sim · 2026-05-10 · via DEV Community

Power Query is the backbone of data preparation in Power BI. Before you can build stunning dashboards or write complex DAX, your data needs to be clean, consistently shaped, and properly related. In this guide, we'll use the real CodeSphere Hub dataset, comprising sales transactions, booking records, product info, and calendar data, to demonstrate every essential Power Query technique.

The dataset includes 163,000+ sales rows spanning 2015–2020, a messy hotel bookings CSV full of nulls and inconsistencies, a product catalogue, and several lookup tables. Exactly the kind of real-world mess Power Query was built to handle.

Working with Power Query Editor

The Power Query Editor is where all data transformation happens before data is loaded into Power BI's model. You access it by selecting Transform Data after importing a source, or from the Home ribbon in Power BI Desktop.

Image 1

Key Interface Areas

Query Panel (left): Lists all loaded tables. Our project has 12 queries including Sales, Data Cleaning, Calendar, and all lookup tables.

Data Preview (center): Shows a sample of your data. Columns are typed (date, text, number) and clickable for column-level operations.

Applied Steps (right): Every action you take is recorded here as a step you can edit, reorder, or delete. This is Power Query's version of version control.

Formula Bar: Shows the M code behind each step, toggle it on from the View tab.

Data Profiling Techniques

Before cleaning, you need to understand your data. Power Query has three built-in profiling views that surface quality issues instantly. Open them from View → Column Quality, Column Distribution, Column Profile.

Image 10

Column Quality
Shows three metrics as a percentage bar at the top of each column: Valid (green), Error (red), and Empty/Null (gray). In the Data Cleaning table, you can immediately see that guest_name and payment_status have significant null rates.

Image 11

Column Distribution
Shows value frequency as a histogram under each column. Instantly reveals skewed distributions, outlier spikes, or unexpected cardinalities. For example, the listing_city column's distribution would show the "LA"/"los angeles"/"Los Angeles" fragmentation.

Image 13

Column Profile
The most detailed view — select a single column and see stats panel: min, max, average, count, distinct count, null count, and a value frequency chart. Available for the entire dataset (not just top 1,000 rows) when you change the profiling scope at the bottom status bar.

Image 12

Handling Missing and Null Values

The Data Cleaning table is a goldmine of real-world data quality issues — nulls in guest_name and payment_status, negative nights values, inconsistent boolean representations (True/False/Yes/No/n), and extra whitespace in guest names. Let's tackle each systematically.

1. Removing Rows with Nulls
For columns where a null makes a row useless (e.g., missing booking_id), remove the row entirely.

Image 16

2. Replacing Nulls with Default Values
For optional fields, replace nulls with a meaningful default rather than deleting the row.
Default values
Replace with 0, mean or median for numerical fields
Replace with “Unknown” for categorical fields
Replace with business-defined defaults

  1. Select the column

  2. Right-click

  3. Choose Replace Values

Image 14

Image 15

3. Removing Duplicates
Power Query can deduplicate on one or multiple key columns. For booking data, booking_id should be unique.

  1. Select the key column(s)
    — e.g., booking_id.

  2. Home → Remove Rows → Remove Duplicates
    (or right-click the column → Remove Duplicates).

Image 17

OR right-click the column → Remove Duplicates

Image 18

Creating Index Columns

Index columns provide unique row identifiers, essential for building relationships, tracking row positions, and creating surrogate keys. Our CodeSphere Hub Sales table doesn't have a guaranteed unique row key, making an index column critical.

  1. Open the target query Select Fact Table in the Queries panel on the left.
  2. Navigate to Add Column → Index Column In the ribbon, click Add Column tab, then click the dropdown arrow on Index Column.
  3. Choose your start value Select From 1 (1-based) for human-readable IDs, or From 0 for zero-based indexing used in joins.
  4. Rename the column Double-click the new Index column header and rename it.

Image 2

Conditional Columns and Logic

Conditional columns let you create new derived columns based on if/else logic, without writing complex DAX measures. In the Data Cleaning table, we can classify bookings by payment status and validity.

  1. Go to Add Column → Conditional Column

  2. Set up the first condition

  3. Add an else-if clause

  4. Set the else (default) value

Image 3

Column from Examples

This is one of Power Query's most powerful AI-assisted features. You provide 1–3 example output values, and Power Query infers the transformation rule automatically. It's ideal when you know what output you want but don't know the exact M formula.

  1. Select column
    Click on the column header to highlight it.

  2. Add Column → Column from Examples → From Selection
    A new editable column panel appears on the right.

  3. Type your example output in the first cell
    For example, "los angeles" type Los Angeles, for "LA" type Los Angeles, for "San Fran" type San Francisco.

  4. Press Enter after each example
    Power Query suggests a formula. Keep typing examples until the entire column looks correct. Click OK.

Image 4

Grouping and Aggregation

Group By is Power Query's equivalent of SQL's GROUP BY; it lets you summarise data by collapsing rows into aggregated results. This is useful before loading data, reducing model size and query load time.
Common aggregation functions include:
Sum
Count
Average
Minimum
Maximum

  1. Select the Sales query
    Make sure you're working on the right table.

  2. Home → Group By
    The Group By dialogue opens.

  3. Configure grouping
    Group By column: Region_Key. New column name: TotalQty. Operation: Sum. Column: Quantity_Sold.

  4. Add a second aggregation (optional)
    Click Add aggregation. New column: TotalTransactions. Operation: Count Rows.

Image 5

Image 6

Pivoting and Unpivoting Columns

The Pivot Demo.csv in our dataset is a perfect real-world example; it contains years as column headers (2015, 2016, 2017…) with metrics as rows. This "wide" format is common in exports but difficult to work with in Power BI's data model, which needs a "long" format.

Unpivoting: Wide → Long (Most Common)

  1. Load the Pivot Demo table
    It has columns: Metric, 2015, 2016, 2017, 2018, 2019, 2020.

  2. Select the year columns (2015–2020)
    Hold Ctrl and click each year column header.

  3. Transform → Unpivot Columns
    Power Query creates an Attribute column (the year) and a Value column (the number).

  4. Rename columns
    Rename Attribute to Year and Value to Amount.

Merging and Appending Queries

When a dataset has two separate annual sales files, we have to look up tables (Regions, Gender, Categories) that need to be joined.

Appending Queries (Union / Stack Rows)

Use Append when you want to combine tables with the same structure — like stacking 2019 and 2020 sales into one table.

  1. Home → Append Queries → Append Queries as New
    (creates a new query instead of modifying an existing one)

  2. Select tables to combine.
    Choose three or more tables and add Sales 2019 and Sales 2020 to the list.

  3. Click OK.
    Power Query stacks the rows. Columns are matched by name; mismatches produce nulls.

Image 7

Merging Queries (SQL-style JOIN)

Use Merge to bring lookup columns into your fact table. Let's enrich our Sales table with Region names from the Regions lookup.

  1. Select Sales query → Home → Merge Queries

  2. Select the right table:
    Choose Regions. Click Region_Key in both tables to set the join key.

  3. Join Kind: Select Left Outer (all Sales rows + matching Region info).

  4. Expand the nested table column.
    Click the expand icon on the new Regions column. Uncheck all but Region and Country. Uncheck "Use original column name as prefix."

Image 8

Date and Time Transformations

The Calendar table contains date-time strings in ISO format, like 2015-01-01T09:53:24. Proper date handling is critical for time intelligence in DAX.

Key Date Operations

Change Type to Date: Select Date_and_Time column → Transform → Data Type → Date/Time → Date.
Extract Year/Month/Day: Add Column → Date → Year / Month / Day. Creates integer columns useful for slicers.
Extract Month Name: Add Column → Date → Month → Name of Month. Returns "January", "February", etc.
Extract Day of Week: Add Column → Date → Day → Day of Week. Returns 0 (Sunday) through 6 (Saturday) — or Name of Day for text.
Calculate Date Difference: Use a Custom Column with an M formula to find the duration between dates.

Image 9

Adding Prefix and Suffix Using Power Query

Adding prefixes or suffixes to values standardises identifiers, helps distinguish keys across tables, and makes data more readable. Our Invoice_ID column already uses "INV-" prefixes — let's see how that's done and how to apply it to other columns.

Format → Add Prefix/Suffix (GUI)

  1. Select the column
    (e.g., Region_Key)

  2. Transform tab → Format → Add Prefix

  3. Type the prefix, e.g., REG-. Result: 1 → REG-1, 10 → REG-10.

You Now Have a Clean, Model-Ready Dataset

Working through all these techniques on any dataset, you've transformed a collection of raw, messy CSVs into a structured, consistent, analysis-ready data model. Here's what was accomplished:

  • Loaded different tables and datasets
  • Added index columns as surrogate keys for relationship management
  • Created conditional columns to flag data quality issues and categorise data
  • Unpivoted the wide Pivot Demo table into a proper long format
  • Merged sales data with Region and many more datasets
  • Appended 2019 and 2020 sales files into a unified table
  • Extracted date parts from the Calendar table for time intelligence
  • Identified and resolved 7 data quality issues in the booking dataset

With this foundation in Power Query, your DAX measures will be simpler, your reports faster, and your insights more reliable. Happy modelling!