Table.Buffer with extras - beginners level query

Power Query
1

Hello community
I hope my image provides the correct info of what I am attempting to do.
Data set contains duplicates in the Product ID column. I am required to keep the most recent $sales - hence the table.buffer which works fine, but when there are duplicates Product Id’s when sales are made on the same date, I am required to keep the highest $sales for that day so the Table.Buffer appears not to be the entire solution.

Thank you
Caroline

eDNA Help Request
eDNA Help Request673×609 22.1 KB

2

Hi ,

Can you provide me your work in progress file.

Thanks

3

Hello @Cazvon,

Thank You for posting your query onto the Forum.

Paste the below provided query onto the “Blank Query” and you’ll be able to see the results as expected. Below is the query alongwith the screenshot of the final results provided for the reference -

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY4xC8IwFIT/S+aDvJfE5CWbSCsutkg7SOkkHQRppdj/b+OiIB1uOO7j7rpOlW2TyuW1zIOCsgHeO3Cg1ZBoMtqQMapHp+qiSvUwPR8ZZFAUsJfMRU32y7XnU2rH+22axzWUgEACcrmRSZPbIr3AWobw7p887Ju0qjhWl2tODQwLIvv8mfXmOkc48jCfn7+d/Rs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Product ID" = _t, Sales = _t, Date = _t]),
    #"Transformed_Data" = 
        Table.Sort(
            Table.Distinct(
                Table.Sort(
                    Table.TransformColumnTypes(Source,{{"Product ID", type text}, {"Sales", Int64.Type}, {"Date", type date}}),
                {{"Product ID", Order.Ascending}, {"Date", Order.Ascending}, {"Sales", Order.Descending}}),
            {"Product ID"}),
        {{"Date", Order.Descending}})
in
    #"Transformed_Data"

Final Results
Final Results778×178 8.35 KB

I’m also attaching the working of the PBIX file for the reference purposes.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Thanks and Warm Regards,
Harsh

Power Query - Transformed Data - Harsh.pbix (16.0 KB)

1 Like
4

Thanks for your detailed response @Harsh!

Hi @Cazvon, did the response provided by Harsh help in solving your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.