Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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!
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
84 | |
76 | |
73 | |
42 | |
36 |
User | Count |
---|---|
109 | |
56 | |
52 | |
48 | |
42 |