Custom Column - that counts distinct occurrences of an ID

[apoje]

Hi,

I have a dataset where I would benefit greatly with a helper column to see if the order has more items than one in an order. One row means one item - and I have an order ID, if there are more items in an order there are multiple rows with identical order IDs.

Desired result:

Order.ID	Item.id	Quantity	Helper.Column.Count
11826	123	1	1
11827	123	2	2
11827	125	1	2
11828	128	2	1
11829	124	1	3
11829	123	5	3
11829	141	3	3
11830	123	1	1

I would appreciate if anyone can help me how to compute this Helper column. 

Thanks!

Andraz

[camargos88 · Accepted solution]

Hi @apoje ,

You can use variable and list to count the ocurrence of each value, like:

let _orderId = [Order.ID] in
List.Count(List.Select(#"Changed Type"[Order.ID], each _ = _orderId))

[lbendlin]

@camargos88  Nice.  How does this perform for large tables?

[camargos88]

Hi @lbendlin ,

I haven't tested it with a large database.

However I believe once it's a list, shouldn't have a heavy impact on it.

But it worths a test.

[lbendlin]

do you want that in Power Query or in DAX?

[apoje]

@lbendlin  PowerQuery if possible 🙂

[lbendlin]

This is one example how to do it in Power Query.  If you have a large table then you might want to throw in a Table.Buffer

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjS0MDJT0lEyNDIGkWAcqwMRN4eLG4ExqrgpVD1C3AIsbgFVjzDHEixuAlVvjCYOMt8UU9wEohZJ3NgA3Z2xAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Order.ID = _t, Item.id = _t, Quantity = _t, Helper.Column.Count = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order.ID", Int64.Type}, {"Item.id", Int64.Type}, {"Quantity", Int64.Type}, {"Helper.Column.Count", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Order.ID"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    Merged = Table.AddColumn(#"Changed Type","Join",(k)=> Table.SelectRows(#"Grouped Rows",each([Order.ID]=k[Order.ID]))),
    #"Expanded Join" = Table.ExpandTableColumn(Merged, "Join", {"Count"}, {"Count"})
in
    #"Expanded Join"