Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Is there a way to filter the data based on the date column. For example -
Product | Cost | Date |
1 | 3 | 1/4/2023 |
1 | 4 | 12/5/2022 |
1 | 5 | 12/20/2022 |
From the above table, I only need 1st row for product 1 which has the latest date and it would be the same for other products.
Product | Cost | Date |
1 | 3 | 1/4/2023 |
Solved! Go to Solution.
Hi @Anonymous ,
Select your [Product] column and go to the Home tab > Group By.
Call the aggregated column 'data' and use the All Rows operator.
Now add a new custom column with the following code:
Table.Max([data], "Date")
This will give you a nested RECORD column that you can expand to reinstate the columns you need from the row that has the max date per product.
Working example code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc1BCkAhCIThu7gOdMaC3lmi+1/jKUHh4t98jLiWQJp4ZF0NSqPLbod78lAwmZdHRCvMoJlraBwU/iJMBSrDcu7v5/4B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, Cost = _t, Date = _t]),
chgTypes = Table.TransformColumnTypes(Source,{{"Product", Int64.Type}, {"Cost", Int64.Type}, {"Date", type date}}),
groupProduct = Table.Group(chgTypes, {"Product"}, {{"data", each _, type table [Product=nullable number, Cost=nullable number, Date=nullable date]}}),
addMaxRecord = Table.AddColumn(groupProduct, "maxRecord", each Table.Max([data], "Date")),
expandMaxRecord = Table.ExpandRecordColumn(addMaxRecord, "maxRecord", {"Cost", "Date"}, {"Cost", "Date"})
in
expandMaxRecord
Example output:
Pete
Proud to be a Datanaut!
That's what I said! 🙂
There are lots of ways to do this. I've written up a few of them here:
https://community.powerbi.com/t5/Community-Blog/Select-Distinct-Rows-Ordered-by-Another-Column-Power...
Even easier to group by and add the max aggregation to the date column in the same GUI dialog.
--Nate
Except that it doesn't guarantee that you get the correct [cost] value associated with the latest [Date] row.
Pete
Proud to be a Datanaut!
Hi,
This post explains how to do it for Decimal value, you can follow the same approach for Dates :
https://gorilla.bi/power-query/using-table-max/
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
Select your [Product] column and go to the Home tab > Group By.
Call the aggregated column 'data' and use the All Rows operator.
Now add a new custom column with the following code:
Table.Max([data], "Date")
This will give you a nested RECORD column that you can expand to reinstate the columns you need from the row that has the max date per product.
Working example code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc1BCkAhCIThu7gOdMaC3lmi+1/jKUHh4t98jLiWQJp4ZF0NSqPLbod78lAwmZdHRCvMoJlraBwU/iJMBSrDcu7v5/4B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, Cost = _t, Date = _t]),
chgTypes = Table.TransformColumnTypes(Source,{{"Product", Int64.Type}, {"Cost", Int64.Type}, {"Date", type date}}),
groupProduct = Table.Group(chgTypes, {"Product"}, {{"data", each _, type table [Product=nullable number, Cost=nullable number, Date=nullable date]}}),
addMaxRecord = Table.AddColumn(groupProduct, "maxRecord", each Table.Max([data], "Date")),
expandMaxRecord = Table.ExpandRecordColumn(addMaxRecord, "maxRecord", {"Cost", "Date"}, {"Cost", "Date"})
in
expandMaxRecord
Example output:
Pete
Proud to be a Datanaut!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.