Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
Ended up trying it my self
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.
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.
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 ) )
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?
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/
Ended up trying it my self
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
100 | |
80 | |
63 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |