Reshaping Data with Transpose, Fill Down, and Fill Up in Power Query

Introduction

Imagine you've just received a sales report from your regional manager. The data looks clean enough in the email — months across the top as column headers, products down the left side — but the moment you paste it into Power Query to start building your dashboard, everything falls apart. Your pivot table expects products as rows and months as a column, not spread across a dozen separate columns. Or maybe you've downloaded a survey export where every third row is blank because the respondent's name only appears once and the software just... stopped repeating it. Your data is technically there, but it's in a shape that makes transformation nearly impossible.

These are not rare edge cases. They are Tuesday. Data arrives in the shape that was convenient for whoever created it, not in the shape that's convenient for you. The ability to reshape data — to flip it, fill gaps, and rearrange its structure — is one of the most practically valuable skills in Power Query. And the good news is that Power Query gives you three powerful tools specifically designed for this: Transpose, Fill Down, and Fill Up.

By the end of this lesson, you'll be able to confidently take messy, awkwardly-shaped data and transform it into a clean, analysis-ready table. You'll understand not just how to use these tools, but why they work and when to reach for each one.

What you'll learn:

• What it means to transpose a table and when you actually need to do it
• How to use Fill Down to propagate values across blank cells in a column
• How to use Fill Up to handle cases where data is grouped in reverse
• How to chain these operations together to tackle complex real-world reshaping problems
• How to recognize the shape your data needs to be in for downstream analysis

---

Prerequisites

This lesson assumes you can open Power Query (via Excel's Data tab or Power BI's Transform Data button), load a basic dataset, and navigate the Query Editor interface. You don't need any M code knowledge — everything in this lesson is achievable through the ribbon buttons. If you've completed basic Power Query navigation lessons, you're ready.

---

Understanding Data Shape: Why It Matters

Before touching any buttons, let's build some intuition about what "reshaping" actually means.

When data professionals talk about tidy data, they mean a specific structure: each variable is a column, each observation is a row, and each type of observational unit forms its own table. This structure is what most analytical tools — pivot tables, DAX, SQL, Python's pandas — expect as input. When your data isn't in this shape, your tools fight you at every step.

Think of it like furniture. A flat-pack table is technically all the pieces you need, but until it's assembled in the right configuration, you can't put anything on it. Transpose, Fill Down, and Fill Up are your assembly tools.

---

Transpose: Flipping Rows and Columns

What Transpose Does

Transposing a table rotates it 90 degrees. Rows become columns, and columns become rows. If you have a table with 3 rows and 12 columns, after transposing it you'll have a table with 12 rows and 3 columns.

Here's a concrete example. Say you've received a quarterly revenue report that looks like this:

Category	Q1	Q2	Q3	Q4
Hardware	42000	38000	51000	67000
Software	95000	102000	88000	115000
Services	31000	29000	35000	41000

This is a perfectly readable human report. But if you want to analyze revenue over time — say, chart how all categories trend across quarters — you need the quarters as rows, not columns. You need this shape instead:

Quarter	Hardware	Software	Services
Q1	42000	95000	31000
Q2	38000	102000	29000
Q3	51000	88000	35000
Q4	67000	115000	41000

That transformation — rotating the table so rows and columns swap — is exactly what Transpose does.

How to Transpose in Power Query

Load your data into Power Query. Once you're in the Query Editor:

1. Click the Transform tab in the ribbon
2. Click the Transpose button (it's in the "Table" group on the far left)

That's it. Power Query rotates your entire table.

Important warning: After transposing, Power Query typically loses your column headers. The first row of your original data may end up as a data row rather than headers. You'll almost always need to follow a Transpose with "Use First Row as Headers" — found in the Home tab, under the "Transform" group. This promotes the top row into column header names.

The M Code Behind Transpose

If you're curious what Power Query writes for you behind the scenes (and you should be — it helps you understand what's happening), click the Advanced Editor under the View tab. You'll see something like this:

= Table.Transpose(#"Previous Step")

It's genuinely that simple. The function takes your current table and flips it. The step that follows to promote headers is:

= Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true])

You don't need to write this manually — the ribbon buttons generate it — but seeing it confirms that these are distinct operations happening in sequence.

When to Use Transpose

Reach for Transpose when:

• Your data has time periods or categories spread as columns that should be rows
• You've received a report formatted for human reading that needs to be reformatted for machine processing
• You're trying to unpivot data but the structure makes direct unpivoting difficult

Tip: Transpose is not the same as Unpivot. Transpose literally rotates the table. Unpivot takes multiple columns and collapses them into key-value pairs. For the quarterly revenue example above, either approach could work — but if you need to keep your column headers as data values in a column, Unpivot (covered in a separate lesson) is often cleaner. Transpose is best when you need to completely rotate the table's orientation.

---

Fill Down: Propagating Values Across Blank Cells

The Problem Fill Down Solves

Let's look at a different data shape problem. You've exported a customer order history from a CRM system, and it comes out like this:

Customer	Order ID	Product	Amount
Priya Sharma	1001	Laptop	1200
	1002	Mouse	25
	1003	USB Hub	45
David Chen	1004	Monitor	650
	1005	Keyboard	89
Maria Lopez	1006	Webcam	119
	1007	Headset	79
	1008	Docking Station	210

The customer name only appears in the first row for each customer. The blank cells in the Customer column aren't truly empty in meaning — they're implicitly "same as above." This format is common in Excel-exported reports and many CRM or ERP exports. It's human-readable, but for analysis it's broken. If you tried to filter for Priya Sharma's orders, you'd only get Order 1001.

Fill Down fixes this by taking a column and replacing each null (blank) cell with the last non-null value above it. It "fills down" the value from the last real entry.

How to Fill Down in Power Query

First, make sure your blank cells are actually null in Power Query, not empty strings. When you load the data, Power Query should automatically interpret true blank cells as null. You can verify this by looking at the cell — it will display the word "null" in italics.

To Fill Down a column:

1. Click the column header of the column you want to fill (in our example, the Customer column)
2. Go to the Transform tab
3. In the "Any Column" group, click the Fill dropdown button
4. Select Down

Power Query will instantly propagate each customer name down through all the blank cells beneath it, stopping when it hits the next non-null value.

After Fill Down, your table looks like this:

Customer	Order ID	Product	Amount
Priya Sharma	1001	Laptop	1200
Priya Sharma	1002	Mouse	25
Priya Sharma	1003	USB Hub	45
David Chen	1004	Monitor	650
David Chen	1005	Keyboard	89
Maria Lopez	1006	Webcam	119
Maria Lopez	1007	Headset	79
Maria Lopez	1008	Docking Station	210

Now every row is fully self-contained. You can filter, group, pivot, and aggregate without losing context.

The M Code Behind Fill Down

= Table.FillDown(#"Previous Step", {"Customer"})

The second argument is a list of column names to fill. You can fill multiple columns at once by adding them to the list:

= Table.FillDown(#"Previous Step", {"Customer", "Region", "Department"})

This is useful when you have a hierarchical structure where multiple grouping columns all need filling simultaneously.

A Common Intermediate State to Watch For

Here's something that trips up a lot of people: Fill Down only works on null values, not on empty strings. If your source data uses empty strings ("") instead of true nulls in blank cells, Fill Down will do nothing — it will see those cells as already containing a value (an empty string), not as blank.

Fix: Before filling, add a step to replace empty strings with null. Select the column, go to Transform → Replace Values, put an empty string in the "Value to Find" field and leave "Replace With" completely blank. Power Query will convert those to null, and then Fill Down will work correctly.

---

Fill Up: When the Grouping Information is Below

Why Fill Up Exists

Fill Up is the less commonly known sibling of Fill Down, but it solves a real problem. Consider this scenario: you're working with a financial system export where category labels appear at the bottom of their group, not the top. Or imagine a budget spreadsheet built by someone who put the department name as a footer under each section. The blank cells need to be filled, but in the opposite direction — upward.

Here's an example from a survey export where the question category appears after its questions:

Question	Response	Category
How satisfied were you with delivery speed?	4
How satisfied were you with packaging?	5
How satisfied were you with product quality?	3	Shipping Experience
Would you recommend us to a friend?	5
How likely are you to purchase again?	4	Loyalty

The Category is labeled at the last row of each group, not the first. Fill Down would push "Shipping Experience" down into the "Loyalty" group — wrong. Fill Up is what you need.

How to Fill Up in Power Query

The process is identical to Fill Down, just one menu option away:

1. Click the Category column header
2. Go to the Transform tab
3. Click the Fill dropdown
4. Select Up

After Fill Up, your table becomes:

Question	Response	Category
How satisfied were you with delivery speed?	4	Shipping Experience
How satisfied were you with packaging?	5	Shipping Experience
How satisfied were you with product quality?	3	Shipping Experience
Would you recommend us to a friend?	5	Loyalty
How likely are you to purchase again?	4	Loyalty

Every question now correctly carries its category. The M code equivalent is:

= Table.FillUp(#"Previous Step", {"Category"})

Tip: In the real world, you'll rarely need to choose between Fill Down and Fill Up without thinking. Ask yourself: "Where does the label appear relative to the data it describes?" If the label is at the top of its group (most common), use Fill Down. If it's at the bottom, use Fill Up. If it's in the middle... you have a more complex problem that may require splitting and merging steps.

---

Chaining Operations: A Real-World Reshaping Workflow

The real power of these tools shows up when you combine them. Let's walk through a realistic scenario from start to finish.

The Scenario

You've received an Excel file from your HR department containing a training completion matrix. The structure looks like this:

• The first row is not headers — it's a "Region" label that appears once and then goes blank
• The second row contains the actual column headers: Employee, Course 1, Course 2, Course 3
• The Region column only has values in the first employee row for each region
• You need the final table to have: Region, Employee, Course, Completed (a fully normalized, tidy structure)

This requires Transpose, Fill Down, and Unpivot (though we'll focus on the first two here and mention Unpivot as the finishing step).

Step 1: Load and Inspect

Load the file into Power Query. Immediately, you'll see the mess — the first row is partial data, not headers. This is where most beginners panic. Don't. Take a breath and work methodically.

Step 2: Promote the Right Headers

Use "Use First Row as Headers" if the second row contains your actual column names. If needed, first Remove Top Rows (under Home → Remove Rows → Remove Top Rows) to eliminate any garbage rows at the top before promoting headers.

Step 3: Fill Down the Region Column

Once your headers are correct, the Region column will have values only in the first row for each regional group. Select the Region column and use Fill Down to propagate region names to every employee row.

Step 4: Continue Transforming

With your data properly filled, you can now proceed with grouping, filtering, or unpivoting to normalize the Course columns into rows. Each step in Power Query builds on a clean foundation that the previous steps established.

Key principle: In Power Query, order matters. Think of each step as leaving the table in a particular state for the next step to work with. Always ask: "Is my table in the right shape for the next operation I want to perform?" If not, figure out which reshaping step gets you there first.

---

Hands-On Exercise

Let's put this into practice with a concrete exercise you can build yourself.

Setup: Create the Source Data

Open Excel and create a new sheet. Enter the following data starting from cell A1:

Region        | Employee        | Q1 Sales | Q2 Sales | Q3 Sales
              | James Okafor    | 45000    | 52000    | 48000
North         | Sarah Kim       | 61000    | 58000    | 70000
              | Tom Rivera      | 39000    | 44000    | 41000
              | Aisha Patel     | 55000    | 63000    | 59000
South         | Marcus Webb     | 72000    | 68000    | 80000
              | Chen Liu        | 48000    | 51000    | 46000

Note: "North" appears next to James Okafor's row, "South" appears next to Aisha Patel's row. The blanks above and below need filling.

Save this as a CSV or Excel file.

Exercise Steps

1. Load the file into Power Query via Excel's Data tab → Get Data → From File
2. Inspect the table — notice the Region column has gaps
3. Fill Down the Region column
4. Verify that every row now has a region value
5. Add a custom column (using Add Column → Custom Column) that concatenates Region and Employee: [Region] & " - " & [Employee]
6. Close and Load the data back to Excel
7. Build a pivot table from the result — you should now be able to slice by Region cleanly

Bonus challenge: After loading, try transposing the table. Notice how Q1, Q2, Q3 become rows. Then practice using "Use First Row as Headers" to recover your column names. Then transpose back. This builds muscle memory for the Transpose workflow.

---

Common Mistakes & Troubleshooting

Mistake 1: Forgetting to Promote Headers After Transpose

After transposing, your column names become Column1, Column2, etc. You must click "Use First Row as Headers" to restore meaningful names. Forgetting this step causes all downstream steps to reference the wrong column names — or to break when the data changes.

Mistake 2: Fill Down on Empty Strings Instead of Nulls

As mentioned earlier, Fill Down only works on true null values. If your source data uses empty strings, the fill will silently do nothing. Always verify that blank cells show the word "null" (in italics) in Power Query before attempting to fill. Use Replace Values to convert empty strings to null if needed.

Mistake 3: Filling the Wrong Direction

It's easy to reflexively click Fill Down when you actually need Fill Up, especially if you're working quickly. Always look at where your label values sit relative to their group. Take five seconds to look at the pattern before applying the fill.

Mistake 4: Applying Transpose to a Table with Duplicate Column Names

Power Query requires unique column names. If your original table has duplicate column headers (e.g., two columns both called "Total"), the transpose may produce errors or unexpected results. Rename duplicate columns before transposing.

Mistake 5: Applying Fill Down Before Removing Irrelevant Rows

If your table has subtotal rows, header rows buried in the data, or separator rows, Fill Down will propagate values across those rows too — and they'll pollute your final result. Always clean up unwanted rows before filling.

Debugging tip: Use the Applied Steps pane on the right side of the Query Editor to step backwards through your transformations. Click any step to see the table's state at that point. This makes it easy to identify exactly where something went wrong.

---

Summary & Next Steps

You've covered a lot of ground. Let's lock in the key ideas:

• Transpose flips your table so rows become columns and columns become rows. Use it when your data is oriented the wrong way for analysis. Always follow a transpose with "Use First Row as Headers" to recover meaningful column names.

• Fill Down propagates values downward through null cells in a column. It's essential for data where grouping labels only appear in the first row of their group — common in CRM exports, Excel reports, and survey tools.

• Fill Up does the same thing in reverse, propagating values upward. Use it when your grouping labels appear at the bottom of their section rather than the top.

• Order matters. These operations work best when applied in the right sequence. Think about the state your table needs to be in before each step, and build your transformation pipeline accordingly.

The reshaping skills you've learned here compose with everything else in Power Query. A transposed table can be unpivoted. A filled table can be grouped and aggregated. Clean shape is the prerequisite for clean analysis.

Where to go next:

• Unpivot Columns — the natural companion to Transpose, for normalizing wide data into long format
• Group By — now that your data is properly shaped and filled, learn to aggregate it by category
• Merging Queries — once your tables are clean, learn to join them together like SQL joins
• Custom Columns and M Basics — go deeper into the language that powers everything you've been doing through the ribbon

Every skill you build in Power Query compounds. Keep going.