if statement in group by Power Query

[kalleeljas]

Hi.

I would like to group a table on City, Tech and Date with different aggregations based on a value, "Type".

My table looks something like this:

Serviceärendenr	City	Tech	Date	Hours	Type
415132	ÄLVDALEN	3031	2017-02-03	1	Work
415132	ÄLVDALEN	3031	2017-02-04	35	Travel
415673	AVESTA	3031	2017-02-06	2	Travel
415673	AVESTA	3031	2017-02-06	15	Work
417751	FALUN	3031	2017-02-17	5	Travel
417751	FALUN	3031	2017-02-17	1	Work
418018	ENVIKEN	3031	2017-02-20	1	Work
418018	ENVIKEN	3031	2017-02-20	2	Travel
418329	FALUN	3031	2017-02-22	1	Work
418329	FALUN	3031	2017-02-22	5	Travel
418313	FALUN	3031	2017-02-22	1	Work
418313	FALUN	3031	2017-02-22	5	Travel

Now, what i would like to do is to Sum "Hours" where the "Type" is "Work", and get Max where the value is "Travel"

So i should get total 2,5 hours in "Falun" on the date 2017-02-22.

Im trying to do solve it like this:

Table.Group(Table, {"Date", "City", "Tech"}, {{"Hours", each if [Type] = "Travel" then List.Max([Hours]) else List.Sum([Hours])}})

But i always end up in the "else" statement and get this result:

As you can see the total is 3, and i want it to be 2,5

is there something wrong with my "if" statement or is there another way to achive this?

Thanks

// Kalle Eljas

[MarcelBeug]

My suggestion is to create new columns for work hours and travel hours, then do the grouping (summing the work hours and taking the max of travel hours) and finally add up the Work and Travel hours (at least, that's what I understand you try to achieve).

The code below is created using the menu-options, but I adjusted the code for step #"Inserted Addition" to use List.Sum (instead of +) so null values are counted as 0.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Serviceärendenr", Int64.Type}, {"City", type text}, {"Tech", Int64.Type}, {"Date", type date}, {"Hours", Int64.Type}, {"Type", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "TravelHours", each if [Type] = "Travel" then [Hours] else null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "WorkHours", each if [Type] = "Work" then [Hours] else null),
    #"Grouped Rows" = Table.Group(#"Added Custom1", {"Date", "City", "Tech"}, {{"SumHours", each List.Sum([WorkHours]), type number}, {"MaxHours", each List.Max([TravelHours]), type number}}),
    #"Inserted Addition" = Table.AddColumn(#"Grouped Rows", "Sum", each List.Sum({[SumHours],[MaxHours]}), type number),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Addition",{"SumHours", "MaxHours"})
in
    #"Removed Columns"

[MarcelBeug]

Not recommended, but just to illustrate how it can be done with an if...then...else statement in group by:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Serviceärendenr", Int64.Type}, {"City", type text}, {"Tech", Int64.Type}, {"Date", type date}, {"Hours", Int64.Type}, {"Type", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Date", "City", "Tech"}, 
                      {{"Hours", 
                      each List.Sum(
                        {List.Sum(Table.AddColumn(_,"SumHours", each if [Type]="Work"   then [Hours] else null)[SumHours]),
                         List.Max(Table.AddColumn(_,"MaxHours", each if [Type]="Travel" then [Hours] else null)[MaxHours])}),
                      type number}})
in
    #"Grouped Rows"

[Anonymous]

@MarcelBeug what if i want to add two columns, for max and min.

I have tried this but getting error.

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"

getting error as

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

@MarcelBeug Can you help me.

[Anonymous]

City, Tech, Time, Work can all be there own dimension table where hours worked is the measure being explored through different dimensions.