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 need your help because I have millions of rows to treat efficiently.
Here a sample of what I have at the beginning.
Wokspace | Object | Date |
W1 | O1 | 01/01/2024 |
W1 | O1 | 01/01/2023 |
W1 | O2 | 01/01/2023 |
W1 | O2 | 01/01/2024 |
W2 | O2 | 01/01/2025 |
W2 | O3 | 01/01/2025 |
W3 | O4 | 01/01/2025 |
W4 | O5 | 01/01/2025 |
W4 | O6 | 01/01/2025 |
And here is what I would like to have at the end.
Wokspace | Object | Date |
W1 | O1 | 01/01/2024 |
W1 | O2 | 01/01/2024 |
W2 | O2 | 01/01/2025 |
W2 | O3 | 01/01/2025 |
W3 | O4 | 01/01/2025 |
W4 | O5 | 01/01/2025 |
W4 | O6 | 01/01/2025 |
As you can see, the key is the concatenation of the Workspace and the Object, and I keep only the last date.
This needs to be done in M query. What is the best M code to achieve this result without waiting to much time ?
Regards,
Camille
Solved! Go to Solution.
Hi @CR,
Here is my solution using your sample data:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjdU0lHyBxEGhvpAZGRgZKIUq4NVwhhJwogoCahRRhgSpkgSxlgkQGL+JlgkQGL+prgkzNAkYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Wokspace = _t, Object = _t, Date = _t]),
ChangedType = Table.TransformColumnTypes(Source,{{"Wokspace", type text}, {"Object", type text}, {"Date", type date}}),
AddedCustom = Table.AddColumn(ChangedType, "Wks&Obj", each [Wokspace] & "-" & [Object]),
GroupedRows = Table.Group(AddedCustom, {"Wks&Obj"}, {{"LastDate", each List.Max([Date]), type nullable date}}),
MergedQueries = Table.NestedJoin(AddedCustom, {"Wks&Obj"}, GroupedRows, {"Wks&Obj"}, "GroupedRows", JoinKind.LeftOuter),
ExpandedGroupedRows = Table.ExpandTableColumn(MergedQueries, "GroupedRows", {"LastDate"}, {"LastDate"}),
AddedColumn = Table.AddColumn(ExpandedGroupedRows, "DeleteColumn", each if [Date] = [LastDate] then 1 else 0),
FilteredRows = Table.SelectRows(AddedColumn, each ([DeleteColumn] = 1)),
RemovedColumns = Table.SelectColumns(FilteredRows,{"Wokspace", "Object", "Date"})
in
RemovedColumns
The final output should be this:
Proud to be a Super User!
Hi @CR,
Here is my solution using your sample data:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjdU0lHyBxEGhvpAZGRgZKIUq4NVwhhJwogoCahRRhgSpkgSxlgkQGL+JlgkQGL+prgkzNAkYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Wokspace = _t, Object = _t, Date = _t]),
ChangedType = Table.TransformColumnTypes(Source,{{"Wokspace", type text}, {"Object", type text}, {"Date", type date}}),
AddedCustom = Table.AddColumn(ChangedType, "Wks&Obj", each [Wokspace] & "-" & [Object]),
GroupedRows = Table.Group(AddedCustom, {"Wks&Obj"}, {{"LastDate", each List.Max([Date]), type nullable date}}),
MergedQueries = Table.NestedJoin(AddedCustom, {"Wks&Obj"}, GroupedRows, {"Wks&Obj"}, "GroupedRows", JoinKind.LeftOuter),
ExpandedGroupedRows = Table.ExpandTableColumn(MergedQueries, "GroupedRows", {"LastDate"}, {"LastDate"}),
AddedColumn = Table.AddColumn(ExpandedGroupedRows, "DeleteColumn", each if [Date] = [LastDate] then 1 else 0),
FilteredRows = Table.SelectRows(AddedColumn, each ([DeleteColumn] = 1)),
RemovedColumns = Table.SelectColumns(FilteredRows,{"Wokspace", "Object", "Date"})
in
RemovedColumns
The final output should be this:
Proud to be a Super User!
User | Count |
---|---|
84 | |
79 | |
64 | |
62 | |
51 |
User | Count |
---|---|
120 | |
99 | |
91 | |
83 | |
65 |