Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
gogrizz
Advocate I
Advocate I

Quicker Way to do "MAXIFS" to Find the Last Purchase Date

Here's a sample of what I'm doing in EXCEL and need to translate into Power Query

sample2.jpg

The first  3 columns are connected to an external data source (no formula required).

For every row (that represents an order) I want to know the corresponding previous order for that specific company.

I.E. US-158 (blue) is showing 4 orders, their first order (ID 3843) doesn't have a previous order so it's value is 0.  For their 2nd order (3848), the 4th column indicates that this company's previous order was 3843.  etc. etc.

 

In Excel, I set the formula in the [previous_order] column to find the maximum order_ID. Excel has the useful "MAXIFS" function so I can set the following conditions

  1. Only look at the [order_ID] column when the value is less than the "order_ID" of that specific row.
  2. Only look at rows where the [account_number] is the same as the "account_number" of that specific row.

The real data set has 20k purchase records.  In POWER QUERY, I've found a way to replicate it by grouping the rows according to account_number, adding a rank, adding a row of lists, then expanding each list.  The problem is that it takes 5-10 minutes to refresh in Power BI.  (I found 4 other ways, with grouping, conditional merging, "List.Max", etc.  - but they take even longer.)

 

Excel calculates all 20k records in 4 seconds.

Does anyone know a quicker way?

1 ACCEPTED SOLUTION
AlienSx
Super User
Super User

Hi, @gogrizz try smth like this

let
    Source = your_source_table,
    g = Table.Group(Source, "account", {{"data", each f(Table.Sort(_, "order_id"))}}),
    f = (t as table) as table =>
        let 
            a = List.Buffer({0} & List.RemoveLastN(t[order_id], 1)),
            b = Table.FromColumns(Table.ToColumns(t) & {a}, Table.ColumnNames(t) & {"previous_order"})
        in b,
    expand = Table.ExpandTableColumn(g, "data", {"order_id", "order_date", "previous_order"})
in
    expand

View solution in original post

4 REPLIES 4
AlienSx
Super User
Super User

Hi, @gogrizz try smth like this

let
    Source = your_source_table,
    g = Table.Group(Source, "account", {{"data", each f(Table.Sort(_, "order_id"))}}),
    f = (t as table) as table =>
        let 
            a = List.Buffer({0} & List.RemoveLastN(t[order_id], 1)),
            b = Table.FromColumns(Table.ToColumns(t) & {a}, Table.ColumnNames(t) & {"previous_order"})
        in b,
    expand = Table.ExpandTableColumn(g, "data", {"order_id", "order_date", "previous_order"})
in
    expand

That's unreal.  Thank you so much!  I started down something similar, but I was no where near what you wrote.

I don't expect a full tutorial...but do you know of a video or site that goes through the general idea of what's going on there?  It works perfectly - but I can't figure out exactly why.

 

Thanks again!

Hi, the whole idea is to "slide down" order_id column by 1 position (skip last item and add zero item ahead). [order_id] column is a list so we use List.RemoveLastN function to cut the tail and lists concatenation ( & ) to add {0} element. To add the whole new column to our table we transform our table into a list of lists (of columns) using Table.ToColumns, add new list ( {a} ) and transform this list back into table using Table.FromColumns. This is how custom function f works.

In order to apply this function to a particular account data we group our data by account (Table.Group) and apply our function to each group of data (step g). Don't forget to sort every table by order_id (or order_date and order_id).

Finally we expand our (transformed) account data tables back into single table. One may use Table.Combine applied to the [data] column (this is list of tables) as well but I've chosen  Table.ExpandTableColumn this time.

It's all about the main idea (step a in custom function) and basic knowledge of how to work with tables and lists. It's a challenge every time. Have fun with Power Query M! 

Another huge help!  Thanks for going above and beyond!

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors