CODEWITHSUNDEEP
excelpowerbipowerquery
Aaron
Aaron

Reputation: 331

Power Query - Pivot Table / Group by specific filters

I already get answered a similiar question here today, but this one is more complex. I tried it by myself by using the general Group By Function but it is not working like I want to. I have a table like this: enter image description here

Now I would like to get a table that shows me

Thhe result would be like:

enter image description here

How is this possible by using Power Query?

Upvotes: 0

Views: 367

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60474

If I understand you correctly:

Edited to correct logic flaw
Re-edited to correct to specifications
Re-edited to add some comments, and also improve data typing

let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Order Number", Int64.Type}, 
        {"Key 1", Int64.Type}, {"Key 2", Int64.Type}, 
        {"Status", type text}, {"Quantity 1", Int64.Type},
        {"Date", type date}}),

//blank the Date if Status = A1_    
    blankDate = Table.FromRecords(Table.TransformRows(#"Changed Type", 
        (r)=> Record.TransformFields(r, {"Date", each if r[Status]="A1" then null else _})),
        type table[Order Number=Int64.Type, Key 1=Int64.Type, Key 2=Int64.Type, Quantity 1=Int64.Type, Date= Date.Type]),

//Group rows by Order Number
//then Sort by Key 1 followed by Key 2; select the first row (highest rank) for the Date and Quantity
    #"Grouped Rows" = Table.Group(blankDate, {"Order Number"}, {
        {"filtered", each Table.Sort(_,{{"Key 1", Order.Descending}, {"Key 2", Order.Descending}}){0}, type record}
    }),
    #"Expanded filtered" = Table.ExpandRecordColumn(#"Grouped Rows", "filtered", {"Quantity 1", "Date"}, {"Quantity 1", "Date"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded filtered",{{"Quantity 1", Int64.Type}, {"Date", type date}})        
in
    #"Changed Type1"

enter image description here

Upvotes: 1

Related Questions