Forum Discussion
Helper IIIShow only later value if duplicate
Hi,
I have a table that looks like this.
| Pricelist | Startdate | End date | Price | Item | Amount |
| test | 01/08/2023 | 31/12/2049 | 2,5 | 12002 | 1 |
| test | 22/08/2023 | 31/12/2049 | 2,5 | 12002 | 1 |
| test | 01/08/2023 | 2,5 | 12003 | 1 | |
| test | 22/08/2023 | 2 | 12003 | 1 |
I would like it to show only the ones with later start date, in case Columns Pricelist, Item and Amount are the same. Would this be possible to achieve in Power BI?
Thankful for help.
Hi sw123 ,
Please refer to my pbix file to see if it helps you.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKTE7NySwuUdJRCi5JLCpJSSxJBbJd81IUoEywEiDtWZKaC6Qcc/NL80qUYnWilUpSwfqMDIyM9S30DcFME0t9QyN9YzBHxxRIGhoZGBiBaCxajIxJ1wNSpYCi0hif6WClqApjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t, #"(blank).5" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}, {"(blank).4", type text}, {"(blank).5", type text}}), #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]), #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Pricelist", type text}, {"Startdate", type text}, {"End date", type text}, {"Price", Int64.Type}, {"Item", Int64.Type}, {"Amount", Int64.Type}}), #"Changed Type2" = Table.TransformColumnTypes(#"Changed Type1",{{"Startdate", type date}, {"End date", type date}}), #"Grouped Rows" = Table.Group(#"Changed Type2", {"Pricelist", "Item", "Amount"}, {{"Count", each List.Max([Startdate]), type nullable date}}) in #"Grouped Rows"How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Community Support Team _ RongtieIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hi sw123 ,
Please refer to my pbix file to see if it helps you.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKTE7NySwuUdJRCi5JLCpJSSxJBbJd81IUoEywEiDtWZKaC6Qcc/NL80qUYnWilUpSwfqMDIyM9S30DcFME0t9QyN9YzBHxxRIGhoZGBiBaCxajIxJ1wNSpYCi0hif6WClqApjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t, #"(blank).5" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}, {"(blank).4", type text}, {"(blank).5", type text}}), #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]), #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Pricelist", type text}, {"Startdate", type text}, {"End date", type text}, {"Price", Int64.Type}, {"Item", Int64.Type}, {"Amount", Int64.Type}}), #"Changed Type2" = Table.TransformColumnTypes(#"Changed Type1",{{"Startdate", type date}, {"End date", type date}}), #"Grouped Rows" = Table.Group(#"Changed Type2", {"Pricelist", "Item", "Amount"}, {{"Count", each List.Max([Startdate]), type nullable date}}) in #"Grouped Rows"How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Community Support Team _ RongtieIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Ashish_Mathur
Super User
Hi,
This M code works
let Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Pricelist", type text}, {"Startdate", type datetime}, {"Enddate", type datetime}, {"Price", type number}, {"Item", Int64.Type}, {"Amount", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Pricelist", "Item", "Amount"}, {{"All", each Table.Max(_,"Startdate")}}), #"Expanded All" = Table.ExpandRecordColumn(#"Grouped Rows", "All", {"Startdate", "Enddate", "Price"}, {"Startdate", "Enddate", "Price"}) in #"Expanded All"Hope this helps.
