Monday Morning Mulling: July 2022 Challenge

On the final Friday of each month, we set an Excel / Power Pivot / Power Query / Power BI problem for you to puzzle over for the weekend.  On the Monday, we publish a solution.  If you think there is an alternative answer, feel free to email us.  We’ll feel free to ignore you.

The challenge this month was to combine multiple rows by a key column.  How did you do?

The Challenge

This month, we had the following problem.  There was a table with multiple rows of transactions under the same invoices.  Our goal was to group rows so that those with the same Invoice ID would be on the same line, and column ‘Invoice ID’ would only show a unique list of invoices.

This issue may be common for anyone who wants to remove duplicates on a key column while still keeping specific data from others.  You can download the question file here.

This month’s challenge was to combine rows of the provided table by column ‘Invoice ID’.  The result should look like the table generated at the bottom of the picture below:

Below were some conditions

• ‘Invoice ID’ and ‘Make’ columns only show up once within that invoice group
• ‘Transaction ID’ and ‘Description’ columns combine all data on the same line with a delimiter (e.g. “, ”)
• ‘Transaction Date’ column lists the latest transaction date of each invoice
• ‘Plate’ column ignores the blanks and shows only the last item within that group
• ‘Amount’ column calculates sum of the amount of each invoice.

Suggested Solution

You can find our Excel file here which demonstrates our suggested solution.

There are several ways to accomplish this goal.  You may choose either Excel formulae, Power Query or DAX measures with a Pivot Table.

In our solution, we use Power Query which we think is the quickest way to solve it.  Additionally, it is more dynamic than traditional Excel formulae.  We start by extracting the table into Power Query / Get & Transform, using ‘From Table / Range’ in the ‘Get & Transform Data’ section of the Data tab.

Before continuing to the next step, we want to remove the unnecessary time component of ‘Transaction Date’ using ‘date’ datatype and convert ‘Transaction ID’ into ‘text’ under the same ‘Changed Type’ step.

Next, we are going to combine rows using the ‘Group By’ option on the Transform tab.  We want to group by ‘Invoice ID’.  To begin with, we use the ‘Advanced’ option:

We add more aggregations for all grouped columns, but all the operations are aimed at numerical values.  We are going to go ahead and apply a Max operation to ‘Transaction Date’ column and a Sumoperation to the rest, and then amend the M code produced.

This gives us an error, which is not surprising since we are adding up text values!

Having a look at the M code on the Formula bar, Power Query is currently using List.Sum() to add up text columns:

We need a text function Text.Combine() instead to compile a list of text values, separated by a separator of our choice.  We have chosen to separate ‘Transaction ID’ and Description with a comma and a space “, ”.

• ‘Transaction ID’:

• Description:

Then, we use List.Distinct(){0} to get the distinct value of the Make column,

and List.LastN(){0} as below to get the last item on the Plate column.

The number zero [0] in the code above is to get the first item value from the List functions.  The full step now looks like this:

Simple!

The Final Friday Fix will return on Friday 26 August 2022 with a new Excel Challenge.  In the meantime, please look out for the Daily Excel Tip on our home page and watch out for a new blog every business working day.