Power Platform Tips & Tricks | The Difference Between Rows in Power Query

Visit Website View Our Posts

If your day-to-day involves working with accounting and finance data, you’re probably already a Microsoft Excel pro, or will be soon enough. As you aim to compile and unify a wide range of data sources into a usable format in Excel, Power Query can provide a low-code solution for data transformation and data preparation. Through a user-friendly graphical interface, finding the difference between rows in Power Query makes it even easier to prepare your data.

There are four phases to connecting and then shaping external data into a format that meets your needs using Power Query – connect, transform, combine and load.   

Many organizations have adopted Microsoft Power BI for interactive data visualizations to bring additional accuracy and insights to finance and accounting. Here’s one scenario: Accounts and Transactions Tables. Each account consists of many transactions. You may want to know how many days it takes between each transaction. This is easy to accomplish in Excel – just find the difference between the rows in a table. However, it is a little more complex in Power BI, but easy to learn in Power Query. 

Today we’ll provide a step-by-step guide of the different steps of two solutions that you can implement as a new approach to your process.  

Solution 1 – General Difference Between Rows 

  • Step 1: This solution provides a general difference between the rows. First things first, we need to complete a couple of simple steps to find the difference between each row. Sort the date column. We’ll sort Received Date in the Transactions table.  
  • Step 2: Add two index columns. One starts at 1 and another starts at 0 - Let’s call these Index and Index.0. 
  • Step 3: Now it is time to join the two new columns to each other and expand Received Date. After Step 3, you’ll have two Received Dates, the original one and the new Received Date, which is shifted by one row. At this time, you could create another column that gives the difference between the two date columns, and choose the date format for the difference.  

Code for Steps 1 – 3 Above

let 

Source = Transactions,
#"Sorted Rows" = Table.Sort(Source,{{"Received Date", Order.Ascending}}), 

    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1), 

    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.0", 0, 1), 

    #"Merged Queries" = Table.NestedJoin(#"Added Index1",{"Index"},#"Added Index1",{"Index.0"},"Next",JoinKind.LeftOuter), 

    #"Expanded Next" = Table.ExpandTableColumn(#"Merged Queries", "Next", {"Received Date"}, {"Received Date.1"}), 

    #"Added Custom" = Table.AddColumn(#"Expanded Next", "Difference", each [Received Date.1] - [Received Date]) 

in 

    #"Added Custom" 

Solution 2 – Grouping by an Account 

Step 1: This solution is more complex, and requires additional steps. In this approach, you will find the difference between the rows for each individual account. The first solution was for a general difference between the rows. To see it grouped by an account, the first step is to create two functions.  

Code for the first and second functions

First Function

(tabletorank as table) as table => 

 let 

 SortRows = Table.Sort(tabletorank,{{"Received Date", Order.Ascending}}), 

 AddIndex = Table.AddIndexColumn(SortRows, "Rank", 1, 1) 

 in 

 AddIndex 

Name the first function Rank Function Asc. 

Second Function

(tabletorank as table) as table => 

 let 

 SortRows = Table.Sort(tabletorank,{{"Received Date", Order.Ascending}}), 

 AddIndex = Table.AddIndexColumn(SortRows, "Rank0", 0, 1) 

 in 

 AddIndex 

Name the second function Rank Function  0 Asc. 

Step 2: After you create the functions above, group the transactions table by the Account SK or ID. You will plug in the functions to create two index or rank columns.  

Step 3: The next step is similar to Solution 1. You’ll merge Account SK and Index columns, then expand the Received Date to find the difference between the two new columns.  

Code For the Second Solution

let 

Source = Transactions, 
#"Grouped Rows1" = Table.Group(Source, {"Account SK"}, {{"AllRows", each _, type table }}), 

    AddedRank = Table.TransformColumns(#"Grouped Rows1",{"AllRows", each #"Rank Function Asc"(_)}), 

    AddedRank2 = Table.TransformColumns(AddedRank,{"AllRows", each #"Rank Function 0 Asc"(_)}), 

    #"Expanded AllRows" = Table.ExpandTableColumn(AddedRank2, "AllRows", {"Received Date", "Rank", "Rank0"}, {"Received Date", "Rank", "Rank0"}), 

    #"Merged Queries" = Table.NestedJoin(#"Expanded AllRows",{"Rank", "Account SK"},#"Expanded AllRows",{"Rank0", "Account SK"},"Next",JoinKind.LeftOuter), 

    #"Expanded Next" = Table.ExpandTableColumn(#"Merged Queries", "Next", {"Received Date"}, {"Received Date.1"}), 

    #"Removed Columns" = Table.RemoveColumns(#"Expanded Next",{"Rank", "Rank0"}), 

    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Received Date.1", type date}, {"Received Date", type date}}), 

    #"Added Custom" = Table.AddColumn(#"Changed Type", "Dif", each Duration.Days([Received Date.1] - [Received Date])) 

in 

    #"Added Custom" 

Solutions 1 and 2 present two possible ways to get the answers you need through Power Query, and the most efficient way to approach if you have a large table. However, if you don’t have a large table, you can also accomplish this with DAX.  

If you need further guidance, or are looking for a partner that can help you find the best solution for all your reporting needs, JourneyTEAM can help you on the road to perfecting your process. 

Get Started with JourneyTEAM 

As an award-winning Microsoft partner, JourneyTEAM has a proven track record with successful Microsoft technology implementations. Let us show you how to create a data driven culture at your organization and unleash your potential with data with Power BI! We can provide demos and full custom introductions. Contact JourneyTEAM today! 

Sources 

https://support.microsoft.com/en-us/office/about-power-query-in-excel-7104fbee-9e62-4cb9-a02e-5bfb1a6c536a 

https://docs.microsoft.com/en-us/power-query/power-query-what-is-power-query 

Leave a Comment

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.