• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Referencing the Next Row in Power Query

You are here: Home / Power Query / Referencing the Next Row in Power Query
Power Query reference next row
July 9, 2019 by Mynda Treacy

Unlike Excel, referencing the next row in Power Query, or even the previous row, is not as simple as we Excel users have become accustomed.

There are several approaches, but some are more efficient that others, which is something to consider if you’re working with a lot of data. Let’s take a look.

First, the data. Below I have a simple table that contains transaction data with the description on the row below each transaction. I need to bring the description up to the row above and then remove the rows that only contain the description.

simple table

Thankfully the table above has a consistent pattern that I can exploit. However, sometimes we have a table that has an inconsistent pattern, like the one below where ID003 on row 9 doesn’t have a description:

complex table

We’ll look at solutions for the Simple Table first, then move on to the Complex Table.

Download Workbook

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

Referencing the Next Row in Power Query – Simple Table

Option 1: Fill Up

One way we can bring data form one row up to the row above is with the Fill Up tool.

Step 1: Load data to Power Query

My data is formatted in an Excel Table called ‘SimpleTable’, so I’ll use the ‘From Table/Range’ connector of the Data tab of the ribbon:

From Table/Range

Note: Excel 2010 and 2013 users will go to the Power Query tab > From Table/Range.

Step 2: Add Conditional Column

We’ll use an ‘if’ statement to determine whether the row contains the transaction or the description. If it contains the description, we’ll bring it across to the new column, otherwise we’ll leave it blank (i.e. null).

The Date column contains a null value where there is a description, and we’ll use this for our logical test. Using the dialog box it’s easy to build a conditional if statement:

add additional column

Step 3: Fill Up

We can now use the Fill Up tool to bring the descriptions up to the row above. Select the new Description column > Transform tab > Fill > Up:

fill up tool

Tip: If you want to reference the row above, then you could use Fill > Down to copy the data down to the next row.

Step 4: Remove null rows

Let’s remove the rows we don’t need. Click on the drop down on the Date column > deselect ‘null’ from the list:

remove null rows

Step 5: Close and Load

Now you're ready to load the data to a Table in the workbook, the Data Model (Power Pivot), or for later versions of Excel you can jump straight to a PivotTable or PivotChart:

Power Query Close & Load

Option 2: Duplicate Query and Merge

Step 1: Load Data to Power Query

As shown in step 1 of Option 1.

Step 2: Duplicate the Query

Right-click the query name in the Queries pane > Duplicate:

duplicate the query

Step 3: Rename the Duplicate Query

In the Query Settings > Properties rename this query ‘SimpleTableDuplicate’:

rename the duplicate query

Step 4: Add an Index Column to both queries:

Back in the SimpleTable query go to the Add Column tab > click on the ‘Index Column’ drop down > From 1:

add an index column to both queries

Then go to the SimpleTableDuplicate query and add an index column ‘From 0’.

Step 5: Merge Queries

On the Home tab > Merge Queries > Merge Queries as New:

merge queries

In the Merge dialog make the following selections from the drop down lists, then hold the CTRL key while left clicking the Index columns in each query:

create a merged table

Tip: If you want to bring the data down to the next row you could switch the Index in the SimpleTable to start at zero and the index in the SimpleTableDuplicate to start at 1.

Step 6: Expand the Table

Click on the double-headed arrow on the SimpleTable column:

expand table

Choose the 'Expand' radio button and deselect ‘Use original column name as prefix’:

deselect use original column name as prefix

You should now have a table like this:

sample table

Step 7: Remove null rows

Click on the drop down on the Date column and deselect ‘null’ from the filter.

deselect null from the filter

Step 8: Rename Columns

Double click the column headers for ID and Description and type in new names like so:

type in new names

Step 9: Delete the Index Column

The index column has done its job. Click the column header and press the Delete key to remove it.

Step 10: Rename the query

SimpleTableMerge

Step 11: Close and Load

You should now have 3 queries; SimpleTable, SimpleTableDuplicate and SimpleTableMerge. You’re ready to Close & Load, but we only want to load one table; SimpleTableMerge. Go to the Home tab > Close & Load > Close & Load To… > in the ImportData dialog box choose ‘Only Create Connection’:

choose Only Create Connection

Step 12: Change Load to Settings

In the Queries & Connections pane on the right-hand side of the worksheet > right-click ‘SimpleTableMerge’ query > Load To…

change load to settings

Note: If you can’t see the Queries & Connections Pane you can turn it on via the Data tab. For Excel 2010 and 2013 users you can turn it on via the Power Query tab > Show Pane:

turn on Queries & Connections pane

In the dialog box you can choose where you want to load the data (note, earlier versions of Excel will not have all of the options shown below):

choose where to load the data

Ok, that seems like a lot of work, but I think you’ll agree that it’s all very straight forward.

Option 3: Custom Function

If you find you have performance issues with options 1 and 2 then you might like to try Imke Feldman’s custom function. It’s an advanced technique that creates a table from a list of columns and then duplicates the table shifting it down one row before joining them together.

I’ve included a modified version of it in the workbook you can download above. You’ll see the queries are called fnSimpleTableShiftedList and SimpleTableShiftedList. You can learn how it works here on Imke’s blog.

Referencing the Next Row in Power Query – Complex Table

The complex table requires a different approach because it doesn’t have a consistent pattern we can exploit. Here it is again for reference:

fill up won't work

Step 1: Load Data to Power Query

As shown in step 1 of Option 1.

Step 2: Add Index Column

In the ComplexTable query go to the Add Column tab > click on the ‘Index Column’ drop down > From 0:

click on the index column drop down

Step 3: Add Custom Column

In this step we’re going to use the Index column to reference the row below if the Amount column contains ‘null’.

Add Column tab > Custom Column. In the Custom Column dialog box enter the following formula (note: Power Query is case sensitive):

enter the following formula

= if #"Added Index"{[Index]+1} [Amount] = null then #"Added Index"{[Index]+1} [#"ID/Description"] else null

In English it reads:

Use the Index column to move down +1 row from the current row and reference the Amount column to check if the value is null. If it is, return the value from the ID/Description column 1 row down from the current row, otherwise return null

The formula returns a new column called ‘Description’:

new column description

Step 4: Filter null rows

Click on the drop down on the Date column and deselect ‘null’ from the filter. The result should look like this:

filter null rows

Step 5: Close & Load

And finally, Close & Load to a table or the Data Model.

close & load

Learn More Power Query

Power Query is an amazing tool that allows use to automate the gathering and cleaning of data resulting in huge time savings. If you'd like to learn how to use it, please consider my Power Query course to fast track you there.

Power Query reference next row

More Power Query Posts

Power Query if Statements incl. Nested ifs, if or, if and

How to write Power Query if statements, including nested if, ‘if or’ and ‘if and’, which are easier to write than their Excel counterparts.
power query variables

Power Query Variables 3 Ways

Power Query Variables enable you to create parameters that can be used repeatedly and they’re easily updated as they’re stored in one place.
delete empty rows and columns using power query

Remove Blank Rows and Columns from Tables in Power Query

Delete blank rows and columns from tables using Power Query. Even rows/columns with spaces, empty strings or non-printing whitespace
extracting data from lists and records in power query

Extracting Data from Nested Lists and Records in Power Query

Learn how to extract data from lists and records in Power Query, including examples where these data structures are nested inside each other.
combine files with different column names in power query

Combine Files With Different Column Names in Power Query

Learn how to load data into Power Query when the column names in your data don't match up. Sampe files to download.
power query keyboard shortcuts

Power Query Keyboard Shortcuts to Save Time

Time saving keyboard shortcuts for Power Query that work in both Excel and Power BI. Download the free Shortcuts eBook
remove text between delimiters power query

Remove Text Between Delimiters – Power Query

Remove all occurrences of text between delimiters. There's no in-built Power Query function to do this, but this code does.
power query advanced editor tips

Tips for Using The Power Query Advanced Editor

Tips for using the Power Query Advanced Editor in Excel and Power BI. Watch the video to see these tips in action
pivot unknown variable number of rows to columns

Pivot an Unknown Number of Rows into Columns

How do you pivot rows to columns when you don't know how many rows you're dealing with? It's not as easy as you may think.
try otherwise power query iferror

IFERROR in Power Query Using TRY OTHERWISE

Using TRY..OTHERWISE in Power Query Replicates Excel's IFERROR So You Can Trap and Manage Errors In Your Queries.


Category: Power Query
Previous Post:custom data types in vbaCustom Data Types in VBA
Next Post:Excel PivotTable Field List Tips

Reader Interactions

Comments

  1. Saj

    July 18, 2022 at 7:44 am

    For option 3, instead of index + “1” can we make this “1” dynamic?

    My bank statement is inconsistent. I’ve to group the description for 1 particular transaction based on the top row with main information + blank rows until the next row with values which could be 5 rows or 16 rows from the previous row with values.

    The description in between should be grouped. Grouping can be done but for that i need the index+“1” from your option 3 dynamic.

    Reply
    • Philip Treacy

      July 18, 2022 at 9:14 am

      Hi Saj,

      It’s hard to visualize what you mean. Please start a topic on our forum and attach a file with your data.

      Regards

      Phil

      Reply
  2. Geoff Faulkner

    August 8, 2020 at 2:12 am

    Linking tables with the index starting 0 /index starting 1 was perfect. Great idea!

    Reply
    • Mynda Treacy

      August 8, 2020 at 8:00 am

      Glad you liked it, Geoff!

      Reply
  3. Johnny

    January 22, 2020 at 10:18 am

    Is there a solution that for the complex table, the descriptions may be split into more than two rows? i.e. some may have two or three or four rows.

    Reply
    • Catalin Bombea

      January 22, 2020 at 2:28 pm

      Hi Johnny,
      In a structured table, a record should have only 1 row. If your source data has text split across rows, they can be grouped and merged into a single description.
      Can you upload a sample source file with this structure? Use our forum to upload, create a new topic after signup.

      Reply
  4. Yitzhak

    September 6, 2019 at 3:33 am

    Hi Mynda,

    I would like to know if it’s possible to solve this problem without power query?

    Reply
    • Mynda Treacy

      September 6, 2019 at 8:22 am

      Hi Yitzhak,

      Yes, you can simply use a formula to refer to the row above e.g. if your data starts in cell A1 then in cell B2 enter this formula =A1 then copy down.

      Mynda

      Reply
  5. MF

    July 20, 2019 at 3:07 pm

    HI Mydna,
    For Option 2, we could skip the step of duplicating the query. We could add two index columns (as you demonstrated) to the same query. In the “Merge” query step, we merge the same Table. 🙂
    I am lazy, I know. That’s why I love Power Query. 🙂
    Cheers,

    Reply
    • Mynda Treacy

      July 21, 2019 at 4:50 pm

      Cheers, MF. I often forget about the being able to merge the table to itself trick 🙂

      Reply
  6. Alex

    July 11, 2019 at 6:56 am

    Wow! This is super helpful to know this. Thanks for sharing, Mynda.

    Reply
    • Mynda Treacy

      July 11, 2019 at 3:16 pm

      Glad you’ll be able to make use of it, Alex 🙂

      Reply
  7. Tatiana Petkova

    July 10, 2019 at 3:52 pm

    Great solutions!

    Reply
    • Mynda Treacy

      July 11, 2019 at 3:16 pm

      Thanks, Tatiana 🙂

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.