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.
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
Solved! Go to 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))
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))
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.
do you want that in Power Query or in DAX?
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"
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.