Count changes in a column based on another column

[Ronning]

Hello,

I have trouble to make the change column.
I want the [change] column to count the number of changes that hapens in [warehous] column per product.

 thanks

[BA_Pete]

Hi @Ronning ,

So basically a distinct count of [Warehouse] for each [Product]?

If so, then you can group by [Product], add Distinct Count and All Rows aggregated columns, then expand the nested tables back out again:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIyVIrVgTKNwEwjINPRDME0hzOhCowR2ozRRI2xiYIMiwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, Warehouse = _t]),
    
    groupRows = Table.Group(Source, {"Product"}, {{"data", each _, type table [Product=nullable number, Warehouse=nullable text]}, {"changes", each Table.RowCount(Table.Distinct(_)), Int64.Type}}),
    expandData = Table.ExpandTableColumn(groupRows, "data", {"Warehouse"}, {"Warehouse"})
in
    expandData

Pete