Forum Discussion

kalleeljas's avatar
kalleeljas
Frequent Visitor
9 years ago

if statement in group by Power Query

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ärendenrCityTechDateHoursType
415132ÄLVDALEN30312017-02-031Work
415132ÄLVDALEN30312017-02-0435Travel
415673AVESTA30312017-02-062Travel
415673AVESTA30312017-02-0615Work
417751FALUN30312017-02-175Travel
417751FALUN30312017-02-171Work
418018ENVIKEN30312017-02-201Work
418018ENVIKEN30312017-02-202Travel
418329FALUN30312017-02-221Work
418329FALUN30312017-02-225Travel
418313FALUN30312017-02-221Work
418313FALUN30312017-02-225Travel

 

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

 

 

 

Need Help

4 Replies

  • MarcelBeug's avatar
    MarcelBeug
    Community Champion
    9 years ago

    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's avatar
      MarcelBeug
      Community Champion
      9 years ago

      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's avatar
        Anonymous
        Not applicable
        8 years ago

        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.