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

1 Reply

  • 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