Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
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
Proud to be a Datanaut!