Power Query :- Group By--->add multiple columns with multiple if conditions

[Anonymous]

It is just and extended of the below question and a curious thing that i am trying to learn.

https://community.powerbi.com/t5/Desktop/if-statement-in-group-by-Power-Query/m-p/489866#M228273

Here i got to know that we can use if conditions in power query group by function.

But what if i want to add two columns based on two if conditions by group by function.

I have added a comment also to the same that what i have tried.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZDPCoJAGMTfZc8K+0ddO0oaReJFs4N4EFtICo3NBO89Sm/ii/XtIag0NE/jN+zPGSZJUChkU+Sie0hRHkQpkYaWRd2CRCI/grhZLUDW1U1eldteBEq1BBnEJIyC09392HV8L4BvhhkBoZhwHVMdMziUsa/kaTplKM9UYTJrxPkFWlz9zom9MHIGKEsdcyBifjbk3FRvVo6/G2hHOBy9chOYrx1sTGxwvCDebIdWoHgm09vAZnTxuxulAznjRG8BmxH2b8oo8Z6SPgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type 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",{{"Serviceärendenr", Int64.Type}, {"City", type text}, {"Tech", Int64.Type}, {"Date", type date}, {"Hours", Int64.Type}, {"Type", type text}}),

    #"Grouped Rows" = Table.Group(#"Changed Type1", {"Date", "City", "Tech"}, 
                      {{"Max", 
                      each
                        List.Max(Table.AddColumn(_,"MaxHours", each if [Type]="Travel" then [Hours] else null)[MaxHours]),
                      type number}},
                      {{"Min", 
                      each
                        List.Min(Table.AddColumn(_,"MinHours", each if [Type]="Work" then [Hours] else null)[MinHours]),
                      type number}})
in
    #"Grouped Rows"

But getting error as 

Expression.Error: We cannot convert a value of type List to type Number.
Details:
    Value=List
    Type=Type

Can anyone please correct me what was the mistake i am doing here.

Thanks,

Mohan V

[v-cherch-msft · Accepted solution]

Hi @Anonymous

You may try to use below function:

= Table.Group(#"Changed Type1", {"Date", "City", "Tech"}, 
                      {{"Max", 
                      each
                        List.Max(Table.AddColumn(_,"MaxHours", each if [Type]="Travel" then [Hours] else null)[MaxHours]),
                      type number},
                      {"Min", 
                      each
                        List.Min(Table.AddColumn(_,"MinHours", each if [Type]="Work" then [Hours] else null)[MinHours]),
                      type number}})

Regards,

Cherie

[Anonymous]

@MarcelBeug Can you please help me with this..