How to add Row_number over partition by Customer - DAX or M query

[rax99]

Simple Table like below.

ID |    DATE        | Customer | RowNumber (desired)

1     10/09/18       AAAA             1

2     11/09/18       AAAA             2

3     12/09/18       AAAA             3

4     12/09/18       BBBB              1

5     12/09/18       BBBB              1

6     13/09/18       BBBB              2

7     17/09/18       DDDD            1

I need a DAX or Power M query calculated column that adds the rownumber and partitions by the customer and date (see desired column above as to what is required)

I can do this quite simple in SQL but not sure how to do so in DAX or M query.

Any ideas?

[echosilo]

A YouTube video so explain Add index to nested tables in Power Query - YouTube 

This solution would number duplicates in a sequence or by any category in your data. Very simple solution.

[Anonymous]

Need to either use EARLIER or define some variables before you start the calculation.  I went the variable route, as just makes more sense to me.  This is a calculated column, not measure

Row Number (Desired) = 
VAR CurrentCustomer= Table2[Customer]
VAR CurrentDate = Table2[Date]
RETURN

CALCULATE(
    DISTINCTCOUNT(Table2[Date]),
        FILTER(
            ALL( Table2),
            Table2[Customer]=CurrentCustomer
            && Table2[Date] <= CurrentDate
        )
    )

[rax99]

Thanks, the logic appears to be sound.

Only issue Im having is when scaling to the table size I have. I have over 300K of rows and its taking forever (over an hour and still spinning) - Is there a more efficient way of acheiving the same result? maybe via a Power M query?

[Anonymous]

To do it in M have a look a this https://www.excelguru.ca/blog/2018/06/27/number-rows-by-group-using-power-query/

[Anonymous · Accepted solution]

Ended up trying it my self 

Created this table named table3 in Query Editor

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Grouped Rows2" = Table.Group(Source , {"Customer", "DATE"}, {{"Count", each _, type table}}),
    #"Grouped Rows3" = Table.Group(#"Grouped Rows2", {"Customer"}, {{"Count.1", each _, type table}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows3", "Custom", each Table.AddIndexColumn([Count.1],"Id1",1,1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Count.1"}),
    #"Expanded Custom1" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Customer", "DATE", "Count", "Id1"}, {"Customer.1", "DATE", "Count", "Id1"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Custom1",{"Count", "Id1"}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Removed Other Columns", "Count", {"ID", "DATE", "Customer", "RowNumber (desired)"}, {"ID", "DATE", "Customer", "RowNumber (desired)"})
in
    #"Expanded Count"

The result is:

Last column matches the desired result.

[Anonymous]

FILTER is an iterator, so will take longer.  Being a calculated column it only calculates on refresh, which may may not be ok.

I'm sure there is a way to accomplish this in PQ, but still learning M, so a little out of expertise at this point.