Forum Discussion

Ronning's avatar
Ronning
Frequent Visitor
2 years ago

Count changes in a column based on another column

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
Need Help
BA_Pete's avatar
BA_Pete
Super User
2 years ago

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